修改重名字段，后面追加序号

#重名查询
SELECT su.`user_id`, su.user_name, su.`user_login`, a.cnt FROM
(SELECT u.`user_name`, COUNT(u.`user_name`) AS cnt
FROM `sys_user` u WHERE 1=1 AND u.`user_flag`=1 GROUP BY u.`user_name` HAVING(COUNT(u.`user_name`)>1)) a
LEFT JOIN sys_user su ON a.user_name=su.user_name
ORDER BY su.user_name;

对于重名的记录添加序号

SELECT @rownum :=0;
SELECT @partition_by :='';
SELECT b.*, CASE WHEN @partition_by = b.user_name THEN @rownum := @rownum + 1 
            ELSE @rownum :=1
       END AS `NO`,@partition_by := user_name AS partition_by
FROM (
SELECT su.`user_id`, su.user_name, su.`user_login`, a.cnt
FROM
(SELECT u.`user_name`, COUNT(u.`user_name`) AS cnt
FROM `sys_user` u WHERE 1=1 AND u.`user_flag`=1 GROUP BY u.`user_name` HAVING(COUNT(u.`user_name`)>1)) a
LEFT JOIN sys_user su ON a.user_name=su.user_name    
ORDER BY su.user_name
) b;

查询结果：

更新重名数据

UPDATE `sys_user` ,
(SELECT b.*, CASE WHEN @partition_by = b.user_name THEN @rownum := @rownum + 1 
            ELSE @rownum :=1
       END AS `NO`,@partition_by := user_name AS partition_by
FROM (
SELECT su.`user_id`, su.user_name, su.`user_login`, a.cnt
FROM
(SELECT u.`user_name`, COUNT(u.`user_name`) AS cnt
FROM `sys_user` u WHERE 1=1 AND u.`user_flag`=1 GROUP BY u.`user_name` HAVING(COUNT(u.`user_name`)>1)) a
LEFT JOIN sys_user su ON a.user_name=su.user_name    
ORDER BY su.user_name
) b INNER JOIN (SELECT @rownum :=0) r ON 1=1 INNER JOIN (SELECT @partition_by :='') p ON 1=1) c 
SET sys_user.`user_name`=CONCAT(sys_user.`user_name`, c.NO) WHERE sys_user.user_id=c.user_id;