MySQL中按user_id重置SELECT自增计数器的实现方法

现代MySQL版本（8.0+）：用窗口函数ROW_NUMBER() #

这是最直观高效的方案，窗口函数专门解决这类分组排序计数的需求，完美匹配你要的按user_id分组重置自增的要求！

直接上查询语句：

SELECT 
    id,
    user_id,
    name,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id) AS increment
FROM 
    your_table_name
ORDER BY 
    user_id, increment;

关键部分解释： #

• ROW_NUMBER() OVER (PARTITION BY user_id ...)：PARTITION BY user_id会把数据按user_id拆分成独立的分组，每个分组内单独计算行号，自然就实现了按用户重置计数的效果。
• ORDER BY id：确保每个用户组内的行按照id从小到大排序，保证自增计数的顺序和你期望的一致。
• 最后的ORDER BY user_id, increment是为了让结果和你给出的示例格式对齐，把同一个用户的行集中展示，按计数顺序排列。

把你的示例数据代入这个查询，得到的结果完全符合预期：

id user_id name increment
1 1 A 1
4 1 D 2
7 1 G 3
2 2 B 1
5 2 E 2
6 2 F 3
3 3 C 1
8 3 H 2

而且不管同一个user_id新增多少行，这个计数都会持续递增，完全满足你的需求。

旧版本MySQL（5.x及以下）：用用户变量实现 #

如果你的MySQL版本不支持窗口函数，也可以用用户变量模拟分组重置的逻辑：

SELECT 
    id,
    user_id,
    name,
    @counter := IF(@prev_user = user_id, @counter + 1, 1) AS increment,
    @prev_user := user_id
FROM 
    your_table_name,
    (SELECT @prev_user := NULL, @counter := 0) AS init_vars
ORDER BY 
    user_id, id;

逻辑说明： #

• 我们初始化两个变量：@prev_user用来记录上一行的user_id，@counter用来记录当前的计数。
• IF(@prev_user = user_id, @counter + 1, 1)：如果当前行的user_id和上一行相同，计数加1；如果切换到新用户，就把计数重置为1。
• 必须按user_id, id排序，这样才能保证同一个用户的行是按顺序处理的，计数才不会出错。

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