使用SQL实现编号续接：从已有序列末尾递增编号

核心逻辑 #

要解决批量插入时编号重复的问题，关键是给临时表的每条记录分配独立的递增偏移量，而非共用同一个"最大编号+1"的值：

1. 先统计每个series对应的当前最大编号（从现有item字段中提取数字部分并取最大值）。
2. 给临时表中同一系列的每条记录分配从1开始的递增序号。
3. 将"最大编号 + 递增序号"格式化补零后，与系列名拼接成新的item值。
4. 将处理后的结果插入目标表。

MySQL 实现示例 #

假设目标表为inventory，临时表为temp_inventory：

INSERT INTO inventory (series, item)
SELECT 
    t.series,
    CONCAT(
        t.series,
        LPAD(
            (COALESCE(m.max_num, 0) + ROW_NUMBER() OVER (PARTITION BY t.series ORDER BY t.series)),
            3,
            '0'
        )
    ) AS new_item
FROM temp_inventory t
LEFT JOIN (
    SELECT 
        series,
        MAX(CAST(RIGHT(item, 3) AS UNSIGNED)) AS max_num
    FROM inventory
    GROUP BY series
) m ON t.series = m.series;

• RIGHT(item,3)：截取item末尾3位数字部分
• LPAD(...,3,'0')：将编号格式化为3位补零的字符串
• COALESCE：处理系列无现有数据的情况（此时从001开始编号）

PostgreSQL 实现示例 #

INSERT INTO inventory (series, item)
SELECT 
    t.series,
    CONCAT(
        t.series,
        TO_CHAR(
            (COALESCE(m.max_num, 0) + ROW_NUMBER() OVER (PARTITION BY t.series ORDER BY t.series)),
            'FM000'
        )
    ) AS new_item
FROM temp_inventory t
LEFT JOIN (
    SELECT 
        series,
        MAX(SUBSTRING(item FROM '.{3}$')::INT) AS max_num
    FROM inventory
    GROUP BY series
) m ON t.series = m.series;

• SUBSTRING(item FROM '.{3}$')：匹配item末尾3位字符
• TO_CHAR(..., 'FM000')：生成无多余空格的3位补零编号

SQL Server 实现示例 #

INSERT INTO inventory (series, item)
SELECT 
    t.series,
    CONCAT(
        t.series,
        FORMAT(
            (ISNULL(m.max_num, 0) + ROW_NUMBER() OVER (PARTITION BY t.series ORDER BY t.series)),
            '000'
        )
    ) AS new_item
FROM temp_inventory t
LEFT JOIN (
    SELECT 
        series,
        MAX(CAST(RIGHT(item, 3) AS INT)) AS max_num
    FROM inventory
    GROUP BY series
) m ON t.series = m.series;

• FORMAT(..., '000')：直接格式化生成3位补零的数字字符串
• ISNULL：处理系列无现有数据的边界场景

注意事项 #

• 如果编号位数不是固定3位，需同步调整数字截取长度和格式化规则。
• 若需要临时表记录按特定顺序分配编号，可在ROW_NUMBER()的ORDER BY后添加对应字段（如id）。
• 高并发插入场景下，建议包裹事务或添加行级锁，避免多进程同时插入导致编号重复。

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