如何基于另一列的排序序列在MySQL表中添加自增ID

问题说明 #

现有一张未设置自增ID的表，表内数据未排序，需要基于CLASS列的字典序生成连续的ID列，示例如下：

当前表 #

添加ID后 #

实现方案 #

支持窗口函数的数据库（MySQL 8.0+/PostgreSQL/SQL Server/Oracle等） #

使用ROW_NUMBER()窗口函数，按CLASS列的字典序排序生成ID：

SELECT 
    ROW_NUMBER() OVER (ORDER BY CLASS ASC) AS ID,
    CLASS,
    ITEM
FROM your_table_name;

如果需要将ID永久添加到表中，可执行以下操作：

1. 新增ID列：

ALTER TABLE your_table_name ADD COLUMN ID INT;

2. 更新ID值：

WITH ranked_data AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY CLASS ASC) AS new_id,
        CLASS,
        ITEM
    FROM your_table_name
)
UPDATE your_table_name t
JOIN ranked_data rd ON t.CLASS = rd.CLASS AND t.ITEM = rd.ITEM
SET t.ID = rd.new_id;

注意：若表中存在重复的CLASS+ITEM组合，需调整关联条件保证唯一匹配。

旧版MySQL（无窗口函数） #

使用用户变量生成ID：

SELECT 
    @row_num := @row_num + 1 AS ID,
    CLASS,
    ITEM
FROM your_table_name, (SELECT @row_num := 0) AS init
ORDER BY CLASS ASC;

永久添加ID的步骤：

1. 新增ID列：

ALTER TABLE your_table_name ADD COLUMN ID INT;

2. 更新数据：

SET @row_num := 0;
UPDATE your_table_name 
SET ID = @row_num := @row_num + 1
ORDER BY CLASS ASC;

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