插入数据时如何自动将自增ID值填充至非约束字段copyOfId

现有数据表包含两个核心字段：

• 唯一自增字段 Id：支持手动指定取值，也可由数据库自动分配
• int类型字段 copyOfId：无自动赋值机制，要求取值始终和Id一致

原有实现逻辑为先查询表内最大ID+1作为待插入ID，再同步赋值给两个字段：

set @nextId = (select id from table order by id desc limit 1) + 1;
insert into table (Id,...,...,...,copyOfId,...,...,...
) VALUES (
@nextId,...,...,...,@nextId,...,...,...);

该写法存在严重并发安全问题：多个事务同时执行时可能拿到相同的@nextId，引发主键冲突、copyOfId赋值错误，不建议在生产环境使用。

以下方案均不需要在插入前手动预查ID，可自动同步Id值到copyOfId，按优先级从高到低排列：

方案1：使用存储生成列（推荐） #

主流数据库（MySQL 5.7+、PostgreSQL 12+、SQL Server等）均支持存储生成列特性，直接将copyOfId配置为引用Id的存储生成列即可，不需要额外维护逻辑。

以MySQL为例，修改表结构语句如下：

ALTER TABLE `your_table`
MODIFY COLUMN `copyOfId` INT GENERATED ALWAYS AS (`Id`) STORED;

该方案优势：

• 无业务侵入：不需要修改任何现有插入逻辑，不管Id是手动指定还是数据库自增生成，copyOfId都会自动同步为对应Id值
• 无额外性能开销：生成列的值在数据写入时自动计算存储，和普通字段性能完全一致
• 自动同步变更：如果后续业务逻辑修改了Id取值，copyOfId会自动同步更新，不需要额外开发逻辑

方案2：使用触发器同步 #

如果数据库版本过低不支持生成列，可以通过触发器实现自动赋值。注意多数数据库的BEFORE INSERT触发器触发时，自增ID还未完成分配，无法直接拿到最终生成的自增值，因此需要用AFTER INSERT触发器更新对应行：

以MySQL为例，触发器创建语句如下：

DELIMITER //
CREATE TRIGGER `trg_sync_copyOfId`
AFTER INSERT ON `your_table`
FOR EACH ROW
BEGIN
  UPDATE `your_table` SET `copyOfId` = NEW.`Id` WHERE `Id` = NEW.`Id`;
END //
DELIMITER ;

该方案缺点是每次插入会多执行一次UPDATE操作，性能略低于生成列方案；如果后续存在更新Id的业务场景，还需要额外创建UPDATE触发器同步copyOfId值。

方案3：插入后通过自增ID函数同步 #

如果不想修改表结构、也不想创建触发器，可以在插入语句执行后，通过数据库提供的 last insert id 函数获取本次生成的自增ID，再更新copyOfId字段，避免提前查最大ID带来的并发问题：

-- 插入时不指定Id和copyOfId，由数据库自动生成Id
INSERT INTO `your_table` (/* 其他业务字段 */) VALUES (/* 对应字段值 */);
-- 拿到刚生成的自增ID同步给copyOfId
UPDATE `your_table` SET `copyOfId` = 2107094 WHERE `Id` = 2107094;

该方案需要修改所有现有插入逻辑，每次插入多执行一次UPDATE，仅适合临时过渡场景使用。

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