MySQL中按分组生成重置式自增计数器的实现方法咨询

这问题太常见了！MySQL确实没有原生的序列生成器，但用用户变量或者窗口函数（MySQL 8.0+）都能完美实现你要的「同组自增、换组重置」的计数器效果，我给你两种实用方案：

方案一：兼容所有MySQL版本（用用户变量） #

这种方法适配所有版本的MySQL，核心是利用用户变量追踪上一行的分组值和当前计数器状态：

SELECT 
    name,
    age,
    hobby,
    @counter := CASE 
        WHEN @prev_name = name THEN @counter + 1 
        ELSE 0 
    END AS counter
FROM 
    t,
    -- 初始化变量：prev_name存上一行的name，counter初始设为-1确保第一个条目得到0
    (SELECT @prev_name := '', @counter := -1) AS vars
ORDER BY 
    name;

逻辑拆解： #

• 先通过子查询初始化两个用户变量：@prev_name用来记录上一条记录的name，@counter作为计数器载体
• 用CASE做判断：如果当前行的name和上一行一致，计数器加1；如果是新分组（name不同），就重置为0
• 必须加ORDER BY name：确保同一个name的记录是连续排列的，否则计数器会出现混乱

如果不想暴露变量赋值的过程，可以把逻辑包在子查询里，只返回需要的字段：

SELECT name, age, hobby, counter
FROM (
    SELECT 
        name,
        age,
        hobby,
        @counter := CASE 
            WHEN @prev_name = name THEN @counter + 1 
            ELSE 0 
        END AS counter,
        @prev_name := name
    FROM 
        t,
        (SELECT @prev_name := '', @counter := -1) AS vars
    ORDER BY 
        name
) AS sub;

方案二：MySQL 8.0+ 专属（窗口函数更简洁） #

如果你用的是MySQL 8.0或更高版本，直接用窗口函数ROW_NUMBER()会更直观，代码也更清爽：

SELECT 
    name,
    age,
    hobby,
    -- 按name分组，组内按hobby排序（可换成你需要的字段），生成从1开始的序号后减1得到0起始的计数器
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY hobby) - 1 AS counter
FROM 
    t
ORDER BY 
    name;

逻辑拆解： #

• PARTITION BY name：把数据按name拆分成独立的分组
• ORDER BY hobby：指定组内的排序规则（你可以换成age或者其他字段，保证同组内顺序稳定）
• ROW_NUMBER()：给每个组内的记录生成从1开始的序号，减1就得到你要的从0起始的计数器

两种方案跑出来的结果都完全匹配你的期望：

+------------+------+--------------+---------+
| name | age | hobby | counter |
+------------+------+--------------+---------+
| Mark | 21 | Soccer | 0 |
| Mark | 21 | Football | 1 |
| pizzipizzi | 7 | cronchcronch | 0 |
| Steve | 23 | Basketball | 0 |
| Steve | 23 | Coding | 1 |
| Steve | 23 | Soccer | 2 |
+------------+------+--------------+---------+

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