MySQL 5.5中如何在分组约束下生成确定性递增虚拟列

需求说明 #

• 现有单表仅包含col_1和col_2两列：col_2的每个值仅对应唯一col_1，但col_1可对应多个不同col_2；两列单独及组合均非唯一
• 需将col_1视为分组、col_2视为分组章节，为每个分组内的章节分配确定性递增编号（编号在表生命周期内保持稳定），查询结果需保证col_1+col_2唯一

示例输入 #

col_1    col_2    
=====    =====    
a        x       
a        y        
b        z        
c        w        
b        z        
c        w        

期望输出 #

col_1    col_2    generated_col
=====    =====    =============
a        y        1
a        x        2
b        z        1
c        w        1

实现方案 #

由于MySQL 5.5不支持窗口函数（如ROW_NUMBER()），可以通过用户自定义变量实现需求，完整SELECT语句如下：

SELECT 
    col_1,
    col_2,
    @row_num := CASE 
        WHEN @prev_col1 = col_1 THEN @row_num + 1 
        ELSE 1 
    END AS generated_col
FROM (
    -- 先去重并固定排序，确保编号稳定
    SELECT DISTINCT col_1, col_2 
    FROM your_table 
    ORDER BY col_1, col_2 DESC
) AS deduplicated_data,
(SELECT @row_num := 0, @prev_col1 := '') AS init_vars

关键说明 #

1. 去重处理：子查询deduplicated_data通过DISTINCT确保col_1+col_2唯一，同时通过ORDER BY col_1, col_2 DESC固定每组内col_2的排序顺序——这是保证编号稳定的核心，只要排序规则不变，每次查询生成的编号就会一致。
2. 变量控制编号：
   • @prev_col1用于记录上一行的分组标识col_1，判断是否需要切换分组
   • @row_num用于在同一分组内递增编号，当切换分组时重置为1

注意事项 #

如果需要调整编号的排序逻辑，只需修改子查询中的ORDER BY子句即可，比如要按col_2升序编号，将col_2 DESC改为col_2 ASC即可。

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