SQLite Insert语句怎么写？批量插入、主键冲突与性能优化一次讲清

SQLite 的 Insert 语句虽然看起来只是把数据写入表的基本操作，但很多项目里出现的主键冲突、批量导入缓慢、自增 ID 不连续等问题，根源都在 Insert 的使用细节上。本文围绕 SQLite Insert 的基础写法、冲突处理和批量写入展开，用 SQL 和少量 Python 代码把关键点演示清楚。

一、先把基础语法和单行插入写对

最常用的插入语句是 INSERT INTO table_name (col1, col2) VALUES (val1, val2)。列名可以省略，但省略时 VALUES 必须按建表时的列顺序提供全部字段。建议生产代码保留列名，这样后续表结构新增字段时，旧插入语句不会因为列顺序变化而写错位置。如果某一列有默认值，可以在列清单中省略该列，SQLite 会自动填 DEFAULT 值；如果显式写 NULL，则优先写入 NULL，除非列上有 NOT NULL 约束。

-- 建表示例
CREATE TABLE user_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL,
  action TEXT NOT NULL,
  created_at TEXT DEFAULT (datetime('now'))
);

-- 推荐：显式列出列名
INSERT INTO user_log (username, action) VALUES ('alice', 'login');

-- 省略列名：必须按建表列顺序提供值
INSERT INTO user_log VALUES (NULL, 'bob', 'logout', NULL);

上例中 id 是自增主键，插入时可以不提供；created_at 有默认值也可以省略。第二种省略列名写法虽然可用，但没有显式列名直观，不建议在项目里大量使用。

SQLite 支持一次插入多行，语法是 INSERT INTO t (a,b) VALUES (...), (...), (...)。这种方式比在循环里执行多次单行插入更省了解析和事务往返。不过要注意多行 VALUES 的写法需要 SQLite 3.7.11 及以上版本，目前主流环境中基本都已支持。多行插入时如果中间某一行违反约束，默认整个语句会失败并回滚，除非使用 INSERT OR IGNORE 等冲突处理子句。

INSERT INTO user_log (username, action) VALUES
  ('alice', 'login'),
  ('bob', 'logout'),
  ('carol', 'upload');

多行插入适合几十到几百条一组的场景。如果是几万条甚至更多数据，则应当结合事务批量提交，后面会具体说明。

二、主键冲突不是只有报错一种结果

当表上存在 UNIQUE 或 PRIMARY KEY 约束时，插入重复值会默认触发错误：UNIQUE constraint failed。SQLite 允许在 INSERT 后接 OR 处理策略，常用的是 INSERT OR IGNORE 和 INSERT OR REPLACE。IGNORE 在冲突时跳过该行，不报错也不写入；REPLACE 会先删除已有行，再插入新行。REPLACE 听起来方便，但它是删除加插入，会改变原行 rowid，可能触发外键级联删除，也会重建索引，开销比单纯更新大。

-- 遇到 username 冲突时跳过
INSERT OR IGNORE INTO user (username, score) VALUES ('alice', 80);

-- 遇到 username 冲突时删除旧行再插入新行
INSERT OR REPLACE INTO user (username, score) VALUES ('alice', 95);

如果需求是冲突时更新部分字段而不是整行替换，应该使用 SQLite 的 UPSERT 语法：ON CONFLICT(column) DO UPDATE SET ...。它只在指定列冲突时执行更新，保留原有 rowid，不影响其他列。这个语法还可以配合 WHERE 子句，例如只有新值大于旧值时才更新，避免并发场景中数据回流。习惯 MySQL 的开发者可以把 ON CONFLICT DO UPDATE 理解成 SQLite 版本的 ON DUPLICATE KEY UPDATE，但语法和目标列都要显式写清。

-- 如果 username 已存在，只更新 score 和 updated_at
INSERT INTO user (username, score, updated_at)
VALUES ('alice', 95, datetime('now'))
ON CONFLICT(username) DO UPDATE SET
  score = excluded.score,
  updated_at = excluded.updated_at;

-- 只有新分数更高才更新
INSERT INTO user (username, score)
VALUES ('alice', 95)
ON CONFLICT(username) DO UPDATE SET
  score = excluded.score
WHERE excluded.score > user.score;

这里 excluded 是 SQLite 的专用别名，表示本次插入试图写入的那行数据。user.score 是表中已有值。这种写法比先 SELECT 再 INSERT 或 UPDATE 更简洁，也避免查询和写入之间的竞态。

除了 IGNORE 和 REPLACE，SQLite 还提供 ABORT、FAIL、ROLLBACK 等冲突策略。默认行为是 ABORT，即回滚当前语句但不结束事务；FAIL 不会回滚当前语句已经插入的前面部分；ROLLBACK 会回滚整个事务。一般业务中掌握 IGNORE、REPLACE 和 ON CONFLICT DO UPDATE 已经足够应对大多数写入场景。

三、批量写入慢怎么办：事务和参数化是关键

逐条执行 INSERT 语句时，如果不显式开启事务，SQLite 会为每条语句自动开启并提交一个事务，涉及磁盘同步，速度极低。把多个插入放进一个事务里，通常能提升几个数量级。批量插入几万条数据时，可以使用 BEGIN ... COMMIT，或者在编程语言里设置连接为非自动提交模式，然后统一提交。要避免每插入一条就 commit 一次。

import sqlite3

conn = sqlite3.connect("app.db")
cur = conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS user_log (id INTEGER PRIMARY KEY, username TEXT, action TEXT)")

data = [("u%d" % i, "login") for i in range(10000)]

# 开启事务
conn.execute("BEGIN")
for username, action in data:
    cur.execute("INSERT INTO user_log (username, action) VALUES (?, ?)", (username, action))
conn.commit()
conn.close()

代码中使用参数化查询，用 ? 占位符传递值，不让用户数据直接拼接 SQL。这有两层好处：一是防止 SQL 注入，二是 SQLite 可以复用已编译的语句，减少解析成本。循环内部的 execute 不会立即提交，直到 commit 才落盘，事务减少了磁盘同步次数。

如果想把导入性能再往上推，可以在事务中配合 PRAGMA synchronous = OFF 或 PRAGMA journal_mode = MEMORY 来降低持久性保证。不过 synchronous = OFF 在系统崩溃或断电时可能损坏数据库，适合一次性批量导入后立即备份的场景。正式业务写入不建议长期关闭。另一个常见做法是分批提交，例如每 5000 条 commit 一次，既有性能提升，也避免一个超大事务锁库过久或内存占用过高。

conn.execute("PRAGMA synchronous = OFF")
conn.execute("PRAGMA journal_mode = MEMORY")

try:
    for batch in range(20):
        for i in range(500):
            cur.execute("INSERT INTO log (name) VALUES (?)", (i + batch * 500,))
        conn.commit()
finally:
    conn.execute("PRAGMA synchronous = FULL")
    conn.close()

四、插入后如何拿自增ID，以及 rowid 为什么不连续

SQLite 中 INTEGER PRIMARY KEY 列直接作为 rowid 的别名。插入成功后，可以执行 SELECT last_insert_rowid() 获取当前连接最近一次插入的 rowid。在 Python 中通常用 cursor.lastrowid。AUTOINCREMENT 关键字会额外使用 sqlite_sequence 表保证生成过的 rowid 不会被重用，但会增加少量开销；普通 INTEGER PRIMARY KEY 在最大行被删除后可能复用较小 rowid。如果业务上要求 ID 永远递增不复用，才需要 AUTOINCREMENT。

cur.execute("INSERT INTO user_log (username, action) VALUES (?, ?)", ("dave", "login"))
new_id = cur.lastrowid
print(new_id)

rowid 不连续不代表程序出错。删除行、插入冲突回滚、REPLACE 删除再插入、事务回滚等操作都可能消耗或跳过一些编号。SQLite 只保证 rowid 在同一张表内唯一，不保证严格连续且无空洞。业务中的订单号、用户编号如果要求对外连续，不应直接使用 rowid，而应该用业务序列或单独的序列表生成。不要依赖 rowid 的连续性做数据完整性判断。

新版本的 SQLite 还支持 INSERT ... RETURNING 子句，可以在插入后直接返回刚写入的列值，省去一次 SELECT。对于带有触发器修改列值、默认值、UPSERT 更新后的最终值，RETURNING 返回的是实际写入或更新后的结果，这比插入后再按 rowid 查一次更可靠。

INSERT INTO user (username, score)
VALUES ('alice', 80)
ON CONFLICT(username) DO UPDATE SET score = excluded.score
RETURNING id, username, score;

需要留意的是，RETURNING 在部分旧版本 SQLite 中不可用，使用前要确认运行环境的 SQLite 版本。掌握这些 Insert 相关的写法后，大部分 SQLite 写入问题都能在代码层面提前规避，尤其是主键冲突策略和事务批量提交这两块，对稳定性和性能影响最明显。

SQLite Insert语句批量插入主键冲突修改时间：2026-09-18 23:32:43