SQL向关联多表插入数据时如何匹配主表自动生成的id

关联表同步插入SQL修改方案 #

支持WITH DML和RETURNING语法的数据库（如PostgreSQL）的正确写法如下：

WITH inserted_substance AS (
    -- 插入主表并返回生成的自增id
    INSERT INTO substancedetails (user_id, substance, duration, planneddose)
    VALUES ('xxxx', 'tea', 8, 'tealeaf')
    RETURNING id
)
-- 一次性插入3条关联时间记录
INSERT INTO "Substance Timing" ("substance id", time)
SELECT inserted_substance.id, t.time
FROM inserted_substance
CROSS JOIN (
    -- 临时拼接要插入的三个时间值
    VALUES 
        ('08:00 AM'),
        ('12:00 PM'),
        ('16:00 PM')
) AS t(time);

如果你实际使用的时间存储表名为scheduledtime、关联字段名为substancetiming，直接替换上述SQL中对应的表名和字段名即可。

原写法错误点说明 #

• SELECT语句字段末尾多了多余逗号：SELECT id, '08:00 AM', 末尾的逗号属于语法错误，会直接触发执行失败
• 拆分三次INSERT语句不仅冗余，在部分数据库中还会因为CTE作用域问题无法读取到主表返回的id
• 包含空格的表名、字段名必须用标识符包裹（PostgreSQL用双引号，MySQL用反引号），否则会被识别为多个语法单元报错

MySQL适配写法（8.0以下版本不支持WITH DML） #

-- 第一步插入主表
INSERT INTO substancedetails (user_id, substance, duration, planneddose)
VALUES ('xxxx', 'tea', 8, 'tealeaf');

-- 第二步用内置函数获取刚生成的自增ID，批量插入关联表
INSERT INTO `Substance Timing` (`substance id`, time)
VALUES
    (2538511, '08:00 AM'),
    (2538511, '12:00 PM'),
    (2538511, '16:00 PM');

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