SQLite3中如何为多对多关系编写INSERT语句？

嘿，我来帮你搞定这个多对多关联的插入问题！首先得提个小问题：你原来的task表定义里，name和description字段类型设成了INTEGER，这明显不对，应该改成TEXT，不然插入字符串内容会出问题，我下面的示例会先修正这个点。

第一步：优化表结构（推荐，保证数据完整性） #

你的task_category关联表目前没有外键约束和主键，这很容易导致无效的关联数据（比如关联不存在的任务或分类）。建议给它加上复合主键和外键约束：

-- 先修正task表的字段类型
ALTER TABLE task MODIFY COLUMN name TEXT NOT NULL;
ALTER TABLE task MODIFY COLUMN description TEXT NOT NULL;

-- 给关联表添加复合主键（确保同一个任务-分类组合不会重复）和外键约束
ALTER TABLE task_category 
ADD PRIMARY KEY (task_id, category_id),
ADD FOREIGN KEY (task_id) REFERENCES task(id),
ADD FOREIGN KEY (category_id) REFERENCES category(id);

第二步：高效插入并关联分类的标准写法 #

你之前手动查询任务ID的方法可行，但效率确实不高。SQLite提供了last_insert_rowid()函数，可以直接获取最后插入的自增ID，再结合SELECT语句批量插入关联记录，同时用事务保证操作的原子性（要么全成功，要么全回滚）。具体代码如下：

BEGIN TRANSACTION;

-- 1. 插入新任务到task表
INSERT INTO task (name, description) 
VALUES ('wrote script', 'I wrote a python script.');

-- 2. 一次性关联已存在的"python"和"scripting"分类
-- 用last_insert_rowid()自动获取刚插入的任务ID，通过SELECT匹配分类ID
INSERT INTO task_category (task_id, category_id)
SELECT last_insert_rowid(), id 
FROM category 
WHERE name IN ('python', 'scripting');

COMMIT;

为什么这是标准做法？ #

• 无需手动查询ID：last_insert_rowid()直接拿到刚插入的任务ID，省去了额外的SELECT查询步骤，提升效率。
• 批量关联：通过SELECT语句一次性插入所有需要关联的分类记录，比多次执行INSERT更简洁高效。
• 数据一致性：事务保证了如果任务插入成功但关联失败，整个操作会回滚，不会出现孤立的任务数据；外键约束则防止插入不存在的任务或分类ID，避免脏数据。

内容的提问来源于stack exchange，提问作者Scoob