如何在插入数据时利用刚生成的自增PK填充字段值？

现有文件表结构及示例数据如下：

Id,   RealName, FilePathName
1000, foo.doc,  C:\data\1000

希望插入数据时，用刚生成的自增主键（Id）来拼接FilePathName，期望的SQL逻辑类似：

INSERT INTO [File] ([RealName], [FilePathName])
VALUES ('foo.doc', 'C:\data\'+(CAST Inserted.Id AS VARCHAR))

但无法直接在INSERT语句中使用Inserted.Id，同时部分场景下文件名不含PK，需要显式存储该字段值。咨询是否有可行方法实现插入时用刚生成的PK填充对应字段。

针对这个需求，不同SQL数据库有几种可行的实现方式，以下分场景说明：

方法1：用OUTPUT/RETURNING子句捕获ID后更新 #

以SQL Server为例，可先插入核心数据，捕获生成的主键后再更新目标字段：

-- 声明表变量存储插入的ID
DECLARE @InsertedIds TABLE (Id INT)

-- 插入数据，捕获自增主键
INSERT INTO [File] ([RealName])
OUTPUT Inserted.Id INTO @InsertedIds
VALUES ('foo.doc')

-- 用捕获的ID更新FilePathName
UPDATE [File]
SET FilePathName = 'C:\data\' + CAST(f.Id AS VARCHAR)
FROM [File] f
JOIN @InsertedIds i ON f.Id = i.Id

这种方式逻辑清晰，支持单条或批量插入，能精准关联刚插入的数据。

方法2：通过AFTER INSERT触发器自动处理 #

如果需要对所有插入操作自动适配，可以创建触发器，插入完成后自动填充字段：

CREATE TRIGGER trg_File_SetFilePathName
ON [File]
AFTER INSERT
AS
BEGIN
    UPDATE [File]
    SET FilePathName = 'C:\data\' + CAST(f.Id AS VARCHAR)
    FROM [File] f
    JOIN Inserted i ON f.Id = i.Id
    -- 仅更新未指定FilePathName的记录，兼容自定义文件名场景
    WHERE f.FilePathName IS NULL
END

后续插入数据时，若不传入FilePathName，触发器会自动用自增ID填充；若需要自定义文件名（不含PK），直接传入对应值即可，触发器会跳过这些记录。

其他数据库的适配实现 #

• MySQL：利用791433获取刚生成的ID，再执行更新：

  INSERT INTO `File` (`RealName`) VALUES ('foo.doc');
  UPDATE `File` SET `FilePathName` = CONCAT('C:/data/', 791433) WHERE `Id` = 791433;
  

• PostgreSQL：通过RETURNING子句结合CTE完成更新：

  WITH inserted AS (
      INSERT INTO "File" ("RealName") VALUES ('foo.doc') RETURNING "Id"
  )
  UPDATE "File"
  SET "FilePathName" = 'C:/data/' || "Id"
  FROM inserted
  WHERE "File"."Id" = inserted."Id";
  

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