批量插入多行时基于最大ID生成递增ID重复问题解决

我有masterTable（主表）和copyTable（复制表），需要将masterTable中inserted_dt字段值为当日的行插入到copyTable。要求插入时从copyTable当前最大id+1开始连续递增生成新id，但当前执行的SQL会导致所有插入行使用同一个id。

当前SQL示例 #

insert copyTable (id, name, cusId)
select (select max(id) + 1 from copyTable), mst.name, mst.cusId
from  copyTable cpy
right join masterTable mst on cpy.cusId = mst.cusId
where mst.inserted_dt = @todayDate

实际结果 #

预期结果 #

我曾尝试row_number()等方案但未解决，寻求可行的实现方法。

核心思路 #

先获取copyTable的最大id，再结合行号为每条待插入数据生成递增id。如果是高并发场景，优先用数据库自增列/序列，避免手动计算带来的冲突。

1. SQL Server 实现 #

先捕获当前最大id，再用ROW_NUMBER()生成偏移量：

DECLARE @maxId INT = (SELECT ISNULL(MAX(id), 0) FROM copyTable);

INSERT INTO copyTable (id, name, cusId)
SELECT 
    @maxId + ROW_NUMBER() OVER (ORDER BY mst.cusId) AS new_id,
    mst.name,
    mst.cusId
FROM masterTable mst
LEFT JOIN copyTable cpy ON cpy.cusId = mst.cusId
WHERE mst.inserted_dt = @todayDate
  AND cpy.cusId IS NULL; -- 对应原SQL的right join逻辑，只插入copyTable中没有的行

2. MySQL 实现 #

用用户变量实现连续递增：

SET @maxId = (SELECT IFNULL(MAX(id), 0) FROM copyTable);
SET @rowNum = 0;

INSERT INTO copyTable (id, name, cusId)
SELECT 
    @maxId + (@rowNum := @rowNum + 1) AS new_id,
    mst.name,
    mst.cusId
FROM masterTable mst
LEFT JOIN copyTable cpy ON cpy.cusId = mst.cusId
WHERE mst.inserted_dt = @todayDate
  AND cpy.cusId IS NULL;

3. PostgreSQL 实现 #

用CTE获取最大id，结合ROW_NUMBER()生成新id：

WITH max_id AS (
    SELECT COALESCE(MAX(id), 0) AS current_max FROM copyTable
)
INSERT INTO copyTable (id, name, cusId)
SELECT 
    current_max + ROW_NUMBER() OVER (ORDER BY mst.cusId) AS new_id,
    mst.name,
    mst.cusId
FROM masterTable mst, max_id
LEFT JOIN copyTable cpy ON cpy.cusId = mst.cusId
WHERE mst.inserted_dt = @todayDate
  AND cpy.cusId IS NULL;

如果copyTable的id是序列生成的，直接用nextval('copyTable_id_seq')生成id更安全，避免并发冲突。

优化建议 #

• 优先给copyTable的id设置为自增列（SQL Server的IDENTITY、MySQL的AUTO_INCREMENT、PostgreSQL的SERIAL/GENERATED AS IDENTITY），插入时无需手动计算id，数据库自动生成唯一递增值，从根源解决重复问题。
• 高并发场景下，手动计算max id可能出现间隙冲突（获取max id后到插入前，其他会话插入了数据），此时自增列/序列是更可靠的选择。

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