MySQL中对GROUP BY结果执行SELECT INTO时如何使用自增序列?

方案1：MySQL 8.0+ 优先使用原生窗口函数 #

ROW_NUMBER() 窗口函数会在分组、排序完成后再生成连续行号，完全规避旧变量法的跳号问题，示例代码如下：

SELECT 
    ROW_NUMBER() OVER (ORDER BY firstName, lastName) AS num, 
    firstName, 
    lastName
FROM
    employees
GROUP BY firstName, lastName;

如果需要分组内排序生成行号，只需要在OVER参数里加PARTITION BY指定分组字段即可。

方案2：目标表存在自增列的SELECT INTO场景专属适配 #

这种场景完全不需要手动写行号生成逻辑，利用MySQL自增列的自动填充特性即可获得连续行号：

1. 提前建好带自增列的目标表，将自增列设为非空主键，示例表结构：

CREATE TABLE IF NOT EXISTS target_table (
    num INT AUTO_INCREMENT PRIMARY KEY,
    firstName VARCHAR(100) NOT NULL,
    lastName VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 执行INSERT ... SELECT（MySQL原生SELECT ... INTO语法不支持直接插入新表并保留自增列属性）时，不指定自增列字段即可：

INSERT INTO target_table (firstName, lastName)
SELECT firstName, lastName 
FROM employees 
GROUP BY firstName, lastName
ORDER BY firstName, lastName;

执行完成后target_table的num字段就是连续无跳跃的行号。

方案3：MySQL 5.x及更低版本无自增列场景兼容方案 #

旧版本没有窗口函数，也不想依赖目标表自增列的情况下，把分组逻辑封装到子查询内，外层再做变量计数即可解决跳号问题，示例：

SET @row_number = 0; 
SELECT 
    (@row_number:=@row_number + 1) AS num, 
    t.firstName, 
    t.lastName
FROM
    (SELECT firstName, lastName FROM employees GROUP BY firstName, lastName ORDER BY firstName, lastName) AS t;

原来的变量法跳号是因为变量计数发生在GROUP BY聚合之前，分组合并行时已经计数的行被丢弃，就会出现行号跳跃的情况，把聚合逻辑前置到子查询后就不会出现该问题。

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