MySQL实现重复ID对应列计数器递增，非重复ID设为0

搞定这个MySQL的计数器需求很简单，我给你一步步讲清楚怎么实现👇

假设你的表名为your_table，需要处理的目标列最终要生成类似dell1、dell0这样的格式，我们可以用窗口函数来高效实现这个需求，不需要复杂的循环或者存储过程。

1. 查询生成结果（不修改原表） #

如果只是想先查看预期结果，执行下面的SQL语句即可：

SELECT 
    id,
    CONCAT('dell', 
        CASE 
            -- 判断当前ID是否重复（出现次数>1）
            WHEN COUNT(*) OVER (PARTITION BY id) > 1 THEN 
                -- 对重复ID组生成从1开始的递增行号
                ROW_NUMBER() OVER (PARTITION BY id ORDER BY id)
            ELSE 0
        END
    ) AS target_column
FROM your_table;

关键函数解释： #

• COUNT(*) OVER (PARTITION BY id)：按ID分组，统计每个ID在表中出现的总次数，用来判断是否是重复ID。
• ROW_NUMBER() OVER (PARTITION BY id ORDER BY id)：对每个重复的ID分组，生成从1开始的连续行号，作为后缀数字。
• CASE语句：根据ID是否重复，选择用行号还是0作为后缀，再和dell拼接成最终的目标列值。

2. 更新原表中的目标列 #

如果需要直接修改原表的列值，我们可以借助CTE（公共表表达式）来先计算出目标值，再关联更新：

WITH ranked_data AS (
    SELECT 
        -- 如果表有主键（比如id不是唯一的，假设有主键id_pk），最好带上主键确保更新准确
        id_pk,
        CONCAT('dell', 
            CASE 
                WHEN COUNT(*) OVER (PARTITION BY id) > 1 THEN 
                    ROW_NUMBER() OVER (PARTITION BY id ORDER BY id)
                ELSE 0
            END
        ) AS new_target_value
    FROM your_table
)
UPDATE your_table t
JOIN ranked_data rd ON t.id_pk = rd.id_pk 
SET t.target_column = rd.new_target_value;

注意：如果你的表没有主键，建议用能唯一标识每一行的字段来关联，避免因为ID重复导致更新错误。

验证结果 #

假设你的表原始数据是：

执行查询后会得到：

完全符合你预期的输出效果！

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