SQLite Insert 全面解析：语法、UPSERT、批量优化与避坑指南

刚接触 SQLite 的时候，很多人的第一反应是“这不就是个嵌入式数据库嘛，Insert 还能玩出花来？”说实话，我一开始也是这么想的。直到后来我在一个数据迁移项目里连续踩了几天坑，才发现自己连最基础的 INSERT INTO 都没吃透。SQLite 的 Insert 语句看着简单，真到用的时候——批量导数据、存在就更新、拿回自增 ID、跨库复制——每一样都有讲究。这篇文章我就把 SQLite Insert 相关的语法、场景、陷阱和优化手段一次性讲透，适合刚入门 SQLite 的开发者，也适合写了好几年 SQL 但没系统整理过 SQLite 细节的人。

1. 先把 INSERT 的基础语法吃透

1.1 三种基础写法，不是只有 VALUES

先看最基础的单行插入，这个大家都会：

INSERT INTO user (name, age, email) VALUES ('张三', 28, 'zhangsan@example.com');

但如果只是这样写，其实你还没用上 SQLite 的全部能力。SQLite 的 INSERT 支持三种基础形态：

第一种是标准 VALUES 写法，刚才已经演示了。这里要注意的是，如果你要插入所有列，可以省略列名，直接写成：

INSERT INTO user VALUES (1, '张三', 28);

但省略列名有个隐患——一旦表结构调整，比如新增了一个字段、调整了字段顺序，这条 SQL 立刻失效，而且报错信息在数据量大的时候特别难定位。我的习惯是永远显式写清楚列名，哪怕麻烦一点，至少表结构变动时 SQL 还能自己兜底。

第二种是多行插入。SQLite 从 3.7.11 版本开始支持一条语句插入多行：

INSERT INTO user (name, age) VALUES ('李四', 25), ('王五', 31), ('赵六', 22);

多行插入不仅写法简单，性能上也比逐条插入高出不少。因为一条语句只触发一次 SQL 解析和一次事务提交，省掉了大量重复开销。实测下来，同样是插入一万条数据，多行 VALUES 比单行逐条插入快了一个数量级。当然，也有上限问题，后面我会专门讲这个坑。

第三种是 DEFAULT VALUES 写法，这是很多人不知道的：

INSERT INTO user DEFAULT VALUES;

它的作用是插入一行“全是默认值”的记录，适合那些完全依赖表默认值或自增主键的场景。比如你要建一个操作日志表，只有 id 和 created_at 字段都有默认值，那就直接这样插入一条空记录，后面再 UPDATE 补信息。用得不多，但偶尔能省不少事。

另外还要留意 INSERT OR 系列变体，比如 INSERT OR IGNORE、INSERT OR ABORT、INSERT OR FAIL、INSERT OR ROLLBACK。它们的区别在于遇到约束冲突时怎么处理，最常用的是 IGNORE。这个我会在第 3 节详细展开，因为它是“存在就跳过”这类需求的基础。

1.2 利用 RETURNING 拿回关键数据

早期用 SQLite 的开发者都有一个痛点：插入一条数据之后，想拿自增 ID，只能再查一次 last_insert_rowid()，或者先单独执行 SELECT 去捞。麻烦不说，在高并发环境下还容易出现拿到别人的 ID 的情况。

SQLite 3.35.0 版本引入了 RETURNING 子句，一下子把这个问题解决了：

INSERT INTO user (name, age) VALUES ('孙七', 27) RETURNING id, created_at;

这条语句执行后，会直接返回刚才插入的那行数据的 id 和 created_at，不用再额外查询了。你还可以配合 WHERE 再过滤：

INSERT INTO user (name, age) VALUES ('孙七', 27) RETURNING id, name WHERE age > 18;

虽然这种写法用得不多，但在某些业务场景里确实能少写几行代码。我个人最常用的场景是：插入之后要把 id 同步到内存对象、缓存或者其他关联表，一条 RETURNING 直接搞定，不用发第二条 SELECT，既省时间又少一次数据库往返。需要提醒的是，RETURNING 在 3.35 以下的版本里不支持，老项目升级 SQLite 之前要先确认版本。

1.3 关于 SQLite 动态类型的几个认识误区

SQLite 和 MySQL、PostgreSQL 有个本质区别：它是动态类型。也就是说，你往一个 INTEGER 列里插入字符串，它不一定报错，甚至可能存得进去（除非开启了 STRICT 严格表模式）。很多从 MySQL 转过来的同事第一次遇到这种情况都会懵：“怎么还能这样？”

其实 SQLite 的类型体系是“亲和类型”（Type Affinity）。比如 TEXT 亲和类型的列，如果插入的数据是数字，SQLite 会尝试转成文本；而 INTEGER 亲和类型的列，如果插入的是字符串但内容看起来像数字，也会优先转成数字。只有在 STRICT 表模式下，类型才会被严格校验，不匹配直接报错。这个特性带来的结果就是：INSERT 语句在 SQLite 里更“宽容”，但宽容是有代价的——数据质量要靠应用层自己把关。

实操建议是：建表时显式声明字段类型，插入前在代码层做类型校验，不要依赖 SQLite 的隐式转换。另外，SQLite 并没有真正的布尔类型和日期时间类型，布尔值一般存 0/1，日期时间一般存 TEXT（ISO 格式）、INTEGER（Unix 时间戳）或 REAL（儒略日）。这些“潜规则”如果在写 INSERT 时没注意，后面查询和统计的时候就会莫名奇妙地出问题。

2. 批量导入：从 SELECT 结果写进表

2.1 INSERT INTO ... SELECT 的基本形态

如果你要导入的数据不是手工敲的，而是已经从别的表里查出来的，那最直接的方式就是 INSERT INTO ... SELECT：

INSERT INTO user_backup (id, name, age, email) SELECT id, name, age, email FROM user WHERE deleted = 0;

这条 SQL 的核心价值在于：它把“查询”和“插入”合并成一次操作，不需要先把数据拉到应用层，再逐条 insert。数据量大的时候，这种方式既节省内存，又减少了网络传输和类型转换的开销。在数据迁移、备份、归档、报表预处理这些场景里非常实用。

注意一点：目标表和源表的字段顺序、数量可以不一致，只要你写清楚了目标表的列名，再把 SELECT 的结果按同样的顺序返回就行。如果目标表字段数量和 SELECT 返回的列数不一致，SQLite 会报错。

另外，SELECT 里可以带各种计算、函数和条件。比如导入时想顺便把字符串清洗一下，或者根据已有字段计算新字段，都可以直接写在 SELECT 里：

INSERT INTO user_report (user_id, user_name, age_group) SELECT id, upper(name), CASE WHEN age < 30 THEN 'young' ELSE 'old' END FROM user;

这样数据在数据库内部就完成了转换，应用层拿到手直接是最终结果，省掉一大段遍历清洗的逻辑。

2.2 跨库导入实战：ATTACH DATABASE + INSERT SELECT

更复杂的场景是，你要从另一个 SQLite 数据库文件里导数据。比如接手一个老系统，数据在一份旧的 .db 文件里，现在要把其中几张表合并到新库里。这时候 ATTACH DATABASE 就派上用场了：

ATTACH DATABASE 'old.db' AS olddb; INSERT INTO user (id, name, age, email) SELECT id, name, age, email FROM olddb.user; DETACH DATABASE olddb;

ATTACH 的作用是把另一个 SQLite 数据库文件“挂载”到当前连接里，然后你就能同时操作两个库的表了。这在日常开发里特别实用，比先把旧库导出成 CSV、再导入新库省事得多。

实操时要注意几个点：第一，ATTACH 只对当前连接有效，换一个连接就要重新 ATTACH；第二，附加的数据库里表名如果和主库冲突，最好带上库名前缀访问（比如 olddb.user），避免混淆；第三，ATTACH 在嵌入式设备上偶尔会踩到路径问题，尽量使用绝对路径，相对路径的基准是当前进程的工作目录，搞错了一直报“no such table”还不知道为什么。

如果你需要导入的是 CSV 文件，那一般不走 INSERT 语句，而是用 SQLite 自带的 .import 命令，或者图形工具 db browser for sqlite 的导入功能。但要注意，.import 默认把内容当 TEXT 处理，遇到全数字字符串的列可能存进去类型不对，导入之前最好先建好表结构，并设置好列类型。

2.3 批量导入前的数据清洗与去重

批量导入最怕什么？最怕源数据里有重复和脏数据，导进去之后才在统计报表里发现问题。所以在 INSERT INTO ... SELECT 之前，一定要做好清洗和去重。

最简单的方法是加 DISTINCT：

INSERT INTO user_clean (id, name, email) SELECT DISTINCT id, name, email FROM raw_user;

但 DISTINCT 是整行去重，如果两张记录只有 id 相同、name 不同，它去不了。这时候要用 GROUP BY 或者窗口函数来指定去重规则：

INSERT INTO user_clean (id, name, email) SELECT id, name, email FROM raw_user GROUP BY id;

上面的写法是保留每组里第一条记录，但哪条是第一条取决于表的物理存储顺序，不确定性很大。更严谨的做法是用窗口函数指定排序规则，比如每个 id 保留创建时间最新的一行：

INSERT INTO user_clean (id, name, email) SELECT id, name, email FROM ( SELECT id, name, email, ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at DESC) AS rn FROM raw_user ) WHERE rn = 1;

这里 SELECT 子查询生成一个带行号的临时结果，外部再筛出每组里的第一行。这样去重规则完全由你掌控，而不是交给数据库的默认行为。很多人在“sql语句去重查询”里翻来覆去找 SQL，其实就是没意识到去重不仅是查询的事，导入阶段更要把好关。

清洗方面，常见的做法是在 SELECT 里做类型转换和字段修正。比如缺省的填充默认值，空字符串转 NULL，邮箱转小写，日期统一成 ISO 格式。这些动作放在 SQL 里做，比导出来在代码里清洗后再插回去，效率高得多。

3. 存在就更新、不存在就新增：UPSERT 的正确姿势

3.1 ON CONFLICT 语法拆解

“sqlite 存在就更新不存在就新增”，这大概是 SQLite 的 Insert 相关话题里搜索量最高的一类需求。SQLite 从 3.24.0 版本开始支持 UPSERT 语法，官方名字就叫“UPSERT”，核心就是 ON CONFLICT 子句。

来看一个典型示例。假设有一张库存表，主键是商品编号：

CREATE TABLE stock ( sku TEXT PRIMARY KEY, quantity INTEGER NOT NULL );

现在要执行“有就加库存，没有就新建一条”的业务：

INSERT INTO stock (sku, quantity) VALUES ('A-1001', 5) ON CONFLICT(sku) DO UPDATE SET quantity = quantity + excluded.quantity;

这段 SQL 的意思是：尝试插入一条 sku 为 A-1001、数量为 5 的记录；如果 sku 冲突，就把原有记录的 quantity 加上 5。这里的 excluded 是个特殊引用，代表“本来准备插入的那行数据”，通过 excluded.quantity 就能拿到刚才想插入的 5。

ON CONFLICT 后面括号里指定冲突检测的列，可以是主键、唯一索引或者一组唯一列。如果没指定冲突目标，直接写 ON CONFLICT DO UPDATE，那它就会对任何唯一性约束冲突生效。不过我不建议这么写，因为无目标的 DO UPDATE 容易误伤，还是明确写清楚冲突列比较稳。

3.2 DO NOTHING 和 DO UPDATE 怎么选

ON CONFLICT 子句有两种处理动作：DO NOTHING 和 DO UPDATE。

DO NOTHING 的字面意思是“冲突就算了，什么都不做”。比如插入一批数据，已有的保留原样，没有的新增：

INSERT INTO user (id, name) VALUES (1, '张三') ON CONFLICT(id) DO NOTHING;

这个适合“只插不更新”的场景，比如初始化数据、导入历史记录。它和你用 WHERE NOT EXISTS 手动判断的效果类似，但写起来更简洁，而且不存在并发下的竞态问题。

DO UPDATE 则是冲突后执行指定的更新逻辑。它比“先 SELECT 判断，存在就 UPDATE，不存在就 INSERT”的代码稳得多，原因是它把“判断+操作”合成了数据库内部的一个原子动作，并发环境下不会出现两个请求同时判断“不存在”然后重复插入的情况。

这个点的价值很容易被低估。很多人习惯先 SELECT 再决定 insert 还是 update，单机低并发的时候确实没问题，但一旦并发上来，或者数据量大了之后有两个线程同时执行 SELECT，都发现“不存在”，然后都走 INSERT，于是其中一条就会撞上主键冲突。用 ON CONFLICT，这个逻辑直接交给数据库处理，根本不需要应用层加锁。

3.3 为什么我不推荐 INSERT OR REPLACE

很多人一看到“存在就更新”的需求，第一反应是用 INSERT OR REPLACE。这个确实能实现效果，但隐藏了很多坑。

INSERT OR REPLACE 的执行逻辑是：如果冲突，先 DELETE 掉旧行，再 INSERT 新行。听上去没问题，实际上至少带来三个副作用：

第一个副作用是自增主键变了。如果表里有一个 AUTOINCREMENT 自增列，删除旧行再插入新行后，id 可能和原来不一样，如果其他表外键引用了这个 id，关联关系就断了。

第二个副作用是未指定的列会被重置成默认值。你只想更新 name 字段，但 INSERT OR REPLACE 会先把整行删掉，再按 INSERT 语句指定的列插入，其他没写的列就全部变成默认值。这往往不是你想要的效果。

第三个副作用是 DELETE 会触发外键级联动作。如果其他表定义了 ON DELETE CASCADE，旧行一删，关联数据也会被连带删除，这是最危险的情况，生产事故级别的坑。

对比一下，ON CONFLICT DO UPDATE 只更新你写了 SET 的字段，其他字段原样保留，id 不变，外键不触发。所以碰到“存在就更新，不存在就新增”的需求，我强烈建议用 ON CONFLICT 而不是 INSERT OR REPLACE。除非你有意识地就是想要“删旧插新”的效果，才考虑 REPLACE。

4. 把写入性能榨干的关键手段

4.1 事务是批量写入的底线

很多人在 SQLite 里做批量插入，一万条数据插了半天，其实主要原因就是没有用事务。SQLite 默认是自动提交模式，每执行一条 INSERT，都会有一次磁盘写入和事务提交动作。如果一万条记录全走自动提交，那就是一万次磁盘 fsync，速度能快才怪。

正确的做法是把批量插入包进一个显式事务里：

BEGIN; INSERT INTO log (content) VALUES ('row 1'); INSERT INTO log (content) VALUES ('row 2'); -- 更多插入 COMMIT;

如果你用的是多行 VALUES，也可以一次性打包：

BEGIN; INSERT INTO log (content) VALUES ('row 1'), ('row 2'), ('row 3'); COMMIT;

实测数据很直观：同样是插入十万条记录，逐条自动提交可能要几十秒，包进一个事务后通常只需要一两秒，性能差距可以到一两个数量级。原因很简单，事务提交时只需要做一次磁盘同步，而自动提交模式下每条都同步。

要注意的是，事务别开得太大。一个事务插入几百万条数据，长时间占着写锁，其他人读都不方便，而且如果中途出错回滚，整个事务全部作废。我一般的习惯是每 5000 到 10000 条提交一次，也方便出错时定位到具体某个批次。

4.2 预编译语句与参数绑定

如果你是在代码里循环拼接 SQL 字符串来插入数据，那性能一定不是最优的，安全性也有隐患。SQLite 提供了预编译语句接口，C 语言里是 sqlite3_prepare_v2 + sqlite3_bind_xxx，Python 里是 execute 的 ? 占位符，Java 里是 PreparedStatement。

核心思路是：SQL 语句只解析一次，后面每次执行只是往占位符里填参数，省掉了反复解析 SQL 的开销。比如在 Python 里：

import sqlite3 conn = sqlite3.connect('test.db') cursor = conn.cursor() cursor.execute('CREATE TABLE IF NOT EXISTS t (id INTEGER, name TEXT)') rows = [(i, f'name_{i}') for i in range(10000)] cursor.executemany('INSERT INTO t (id, name) VALUES (?, ?)', rows) conn.commit()

executemany 内部就是典型的预编译 + 批量绑定参数模式，既安全又快。参数绑定还顺带解决了 SQL 注入问题。你不需要绞尽脑汁去处理字符串里的单引号转义，数据库库自己知道怎么正确解析参数。

这里补充一句：网上经常有人问“insert 语句里字符串带引号怎么办”，也有“将带引号的字符串保存到数据库的 sql 语句”这种搜索词。如果你在拼 SQL 字符串，确实要小心转义；但只要你换成参数绑定，这个问题根本不存在。这也是我强烈推荐预编译的原因——它不仅仅是性能优化，更是安全和代码可维护性的提升。

4.3 PRAGMA 调优与 WAL 模式

SQLite 的写入性能，除了事务和预编译之外，还受一堆 PRAGMA 参数影响。最常用的几个：

journal_mode 设为 WAL：

PRAGMA journal_mode = WAL;

WAL 模式（Write-Ahead Logging）把写入操作先追加到 WAL 文件里，而不是直接改主数据库文件。带来的好处是读写可以并发，读操作不会被写操作阻塞，同时写入性能也有提升。在嵌入式系统或者桌面客户端里特别实用。

synchronous 设为 NORMAL：

PRAGMA synchronous = NORMAL;

这个参数控制什么时候做磁盘同步。默认是 FULL 或 EXTRA，每条提交都保证数据刷到磁盘，安全但慢。调成 NORMAL 后，在 WAL 模式下绝大多数故障场景数据都不会丢，但性能明显提升。如果只是缓存类数据、丢了也无所谓，甚至可以设 OFF，不过生产环境我不建议 OFF。

cache_size 调大：

PRAGMA cache_size = -64000;

cache_size 单位是页，正值是页数，负值是 KiB。设成 -64000 表示缓存上限约 64MB。批量插入时，更大的缓存能减少磁盘 I/O，在数据量大的场景里提升很明显。

还有 temp_store 和 mmap_size 之类，视场景调整。我的建议是：先用默认参数跑通功能，再根据实际数据量和并发情况逐个调优，不要一次性全改，否则出了问题你根本不知道是哪一项引起的。

4.4 大数据量写入时的索引策略

很多人不知道，索引对 INSERT 的影响比对 SELECT 大得多。因为每次插入新行，数据库除了写入表数据，还要维护所有的索引。数据量大时，索引数量越多、索引树越高，写入越慢。

如果你的需求是“先大批量灌数据，之后才做查询”，那最优策略是：先删掉索引，插入完所有数据，再重新创建索引。建索引的耗时，远远小于插入过程中边插边维护索引的耗时。实测中，一张表上有四五个索引时，大批量插入的性能差距可以到数倍。

另一个策略是延迟约束检查。SQLite 不像 PostgreSQL 那样有完整的 DEFERRABLE 约束支持，但如果你把插入放在事务里，约束检查仍然是在语句级别执行。想要在提交时才检查唯一性，目前的 SQLite 版本支持做得有限，所以能做的就是尽量减少不必要的索引和约束。

还有一点要注意：SQLite 的默认页面大小是 4096 字节，如果单行数据特别长（比如存了 JSON 或大文本），会让每个页面的利用率下降，影响写入速度。遇到这种场景，可以考虑把大字段拆到独立的表里，或者改用压缩存储，不要一股脑塞进宽表。

5. 高频异常与跨数据库迁移避坑

5.1 单引号转义与注入防护

SQL 里插入字符串时遇到单引号，最常见的错误是直接拼接导致语法错误。比如要插入 It's fine：

INSERT INTO note (content) VALUES ('It's fine');

这条 SQL 在 SQLite 里会报错，因为字符串里的单引号没有转义。SQLite 的转义规则是双写单引号：

INSERT INTO note (content) VALUES ('It''s fine');

这是标准 SQL 规则，不是 SQLite 独有的。但很多新手在这里被坑过，尤其是数据来自用户输入时，不做转义就直接拼 SQL，轻则 SQL 报错，重则被 SQL 注入。

最佳实践仍然是参数绑定。Python 里用问号占位符，C 接口里用 sqlite3_bind_text，这样单引号、反斜杠、Unicode 字符统统交给底层处理，完全不用手动转义。如果你非要拼 SQL，那至少要把单引号替换成双单引号，同时对其他特殊字符做好处理。

5.2 datatype mismatch 和 NOT NULL 冲突

SQLite 的报错信息里，datatype mismatch 是最常见的一类。前面提过 SQLite 是动态类型，默认模式下一般不会报类型错误，但如果你用的是带 STRICT 关键词建的表，或者插入的值和列亲和类型冲突时，数据库就会直接报 datatype mismatch。

实操中，我用 STRICT 表模式时吃过一次亏：一个列声明为 INTEGER，代码里传进来的是一个字符串形式的数字“12345”，普通表能存进去，STRICT 表直接拒绝。这种现象和 MySQL 的严格模式有点像，但 SQLite 的 STRICT 更严格，连隐式转换都不愿意多做。所以在写 INSERT 之前，一定确保代码层的类型和表结构声明一致。

NOT NULL 约束冲突是另一个高频问题。大多数人建表时对必填字段加了 NOT NULL，但 INSERT 语句一偷懒就没填，数据库就报错。这里有个容易忽略的点：如果插入时用的是 INSERT OR IGNORE，NOT NULL 约束兜底失败时也会被忽略，那不仅是这次插入没成功，而且你的代码还不知道失败了多少条。所以用 INSERT OR IGNORE 的人群要特别留意约束冲突的情况，不要想当然地以为“没报错就成功了”。

5.3 与 MySQL、Oracle、PostgreSQL 的 INSERT 差异对照

很多人平时用 MySQL 或 Oracle，转 SQLite 时习惯把旧语法带过来，结果踩了不少坑。这里我列一个简单的对照表：

这里最容易踩的坑是 MySQL 转 SQLite：把 ON DUPLICATE KEY UPDATE 抄过来，结果 SQLite 不认。用法上虽然语义类似，但语法完全不同，SQLite 必须写 ON CONFLICT 配合 DO UPDATE。另一个坑是 PostgreSQL 的 INSERT ... ON CONFLICT 在 SQLite 里支持有限，ON CONFLICT 后面的动作没有 WHERE 子句做过滤，属于简单版的 UPSERT。

Oracle 用户转 SQLite 也要注意，MERGE INTO 在 SQLite 里是不存在的，要写 UPSERT 得用 ON CONFLICT 语法替代。另外 Oracle 的 INTO 子句和 RETURNING INTO 用法都不一样，迁移时不能拿来就用。

5.4 移动端与桌面端几个真实场景补充

SQLite 在移动端的应用比大家想象中更普遍。UniApp、Android、iOS 本地存储都用 SQLite 做持久化，而移动端的 INSERT 有几个特殊问题值得说。

第一个是主键自增。移动端离线写入场景经常要用本地事务缓存一批操作，然后等网络恢复后再同步。此时如果本地表用了自增主键，不同设备上同一批数据生成的 id 可能会有冲突，同步时后面的记录会覆盖前面的。常见解法是使用业务 ID 作为主键，而不是依赖本地自增，或者用 UUID/雪花 ID 做全局唯一标识，再配合 ON CONFLICT DO UPDATE 做幂等写入。

第二个是并发写入。SQLite 在移动端容易被误用成“多线程共用同一个连接写”，其实 SQLite 默认一个连接同一时间只允许一个写操作。如果多个线程同时写，就会出现 database is locked 错误。正确做法是使用单写者模式，或者通过连接池串行化写操作。一些框架（比如 sqlite-net）也提供了队列机制，可以避免这类问题。

第三个是性能。移动端设备存储性能远不如 PC，如果每插入一条都提交一次，用户能明显感觉到卡顿。用事务批量提交、把 WAL 打开、适当调节 synchronous，是移动端 SQLite 写入性能的三大抓手。

我在用 C++ 写工具时，也经常遇到“把 JSON 保存进 SQLite”的需求。这时候我不会把整个 JSON 作为一个文本串塞进去，而是把 JSON 解析后的关键字段拆成表结构的列，再逐列插入。这样查询和索引的效率都高很多。如果必须保留原始 JSON，也要考虑压缩后再存，减少存储空间和写入 I/O。

最后再分享一个小技巧。SQLite 的 INSERT 语句是我个人认为所有数据库里最值得逐字研读的，因为它的语法相对精炼，没有 MySQL 那么多扩展，也不像 Oracle 那么重，但该有的能力都有。花点时间把 ON CONFLICT、RETURNING、INSERT INTO ... SELECT 这几个组合练熟，日常开发里几乎一半的“写数据”问题都能用一条 SQL 解决。尤其是 UPSERT 这个能力，很多人以为必须靠应用层逻辑去实现，实际上 SQLite 原生早就支持了，只是知道的人不多。