如何向数据表中插入按添加顺序生成递加编号的新记录

原脚本失效原因 #

• 所有union分支的max(id)、max(price)都是基于插入前的原表数据计算，三个分支得到的id均为5+1=6，price均为1001+1=1002，插入时要么触发主键冲突，要么结果不符合需求。
• 额外注意：table是SQL保留关键字，作为表名使用时需要用反引号包裹，否则会触发语法错误。

正确实现方案 #

方案1：支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等通用） #

只查询一次原表的最大值，结合ROW_NUMBER生成增量偏移量，一次性计算出所有新记录的字段值：

INSERT INTO `table` (id, name, price)
SELECT 
  base.max_id + ROW_NUMBER() OVER(ORDER BY new.name),
  new.name,
  base.max_price + ROW_NUMBER() OVER(ORDER BY new.name)
FROM (
  SELECT MAX(id) AS max_id, MAX(price) AS max_price FROM `table`
) base
CROSS JOIN (
  SELECT 'ABF' AS name UNION ALL
  SELECT 'ABG' UNION ALL
  SELECT 'ABH'
) new

方案2：兼容旧版本数据库（如MySQL 5.x等不支持窗口函数的场景） #

通过用户变量实现偏移量计数：

INSERT INTO `table` (id, name, price)
SELECT 
  base.max_id + @row := @row + 1,
  new.name,
  base.max_price + @row
FROM (
  SELECT MAX(id) AS max_id, MAX(price) AS max_price FROM `table`
) base
CROSS JOIN (
  SELECT 'ABF' AS name UNION ALL
  SELECT 'ABG' UNION ALL
  SELECT 'ABH'
) new
CROSS JOIN (SELECT @row := 0) init_var

内容的提问来源于stack exchange，提问作者Алексей Алексеев