SQLite中INSERT INTO...SELECT结合last_insert_rowid()的问题

这确实是SQLite的预期行为——last_insert_rowid()函数会返回最近一次INSERT操作生成的rowid。当你执行第二条INSERT INTO item_tag SELECT ...语句时，每插入一条关联记录，last_insert_rowid()就会被更新为这条item_tag记录的rowid，所以只有第一条item_tag记录能正确关联到刚插入的item，后续的都会指向错误的id。

下面给你两种可靠的解决方案：

方法1：用临时变量保存item的ID #

先插入item并把生成的ID存到变量里，再用这个变量插入关联的标签记录，避免last_insert_rowid()被后续操作覆盖。

如果是在SQLite命令行的批处理脚本中，可以这样写：

-- 插入条目并获取ID
INSERT INTO item VALUES(NULL, ...);
-- 将ID存入临时参数
.param set @item_id = last_insert_rowid();
-- 用保存的ID插入关联记录
INSERT INTO item_tag SELECT @item_id, tag.name FROM tag WHERE tag.name IN('tag1','tag3');

如果是在编程语言的SQL执行逻辑里，也可以先执行插入item的语句，然后单独调用获取last_insert_rowid的API（比如Python的cursor.lastrowid），再用这个ID构造插入item_tag的语句。

方法2：用CTE+RETURNING子句一次性完成（推荐） #

从SQLite 3.35.0版本开始，支持RETURNING子句，可以结合公共表表达式（CTE）在同一个语句中完成插入item和关联标签的操作，全程不会出现rowid被覆盖的问题：

WITH inserted_item AS (
  -- 插入条目并返回生成的ID
  INSERT INTO item VALUES(NULL, ...)
  RETURNING id
)
-- 关联标签表插入关联记录
INSERT INTO item_tag (item_id, tag_id)
SELECT inserted_item.id, tag.name
FROM inserted_item, tag
WHERE tag.name IN('tag1','tag3');

这种方法更简洁，而且原子性更好，不需要额外的变量操作，推荐在支持的SQLite版本中使用。

为什么你的原方法会失效？ #

再补充解释一下：last_insert_rowid()是全局的，每次执行INSERT（不管是哪个表）都会更新它的值。你的第二条INSERT语句本质是批量插入多条item_tag记录，每插入一条，这个值就会被更新为当前item_tag的rowid，所以除了第一条，后面的记录都会用错误的ID关联。

内容的提问来源于stack exchange，提问作者Germain D.