首页 > 代码库 > mysql合服 更新相同的用户名前追加服务器编号

mysql合服 更新相同的用户名前追加服务器编号

表结构:

1 CREATE TABLE IF NOT EXISTS `user` (
2   `user_id` int(11) NOT NULL COMMENT 主键,
3   `user_level` int(11) NOT NULL DEFAULT 0 COMMENT 等级,
4   `user_name` varchar(32) NOT NULL DEFAULT 0 COMMENT 名称,
5   `server_id` int(11) NOT NULL DEFAULT 0 COMMIT 服务器编号,
6   PRIMARY KEY (`user_id`)
7 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

示例sql:

1 UPDATE user a,
2           (
3             SELECT a.user_id FROM user a,(SELECT user_name,count(user_id) as num FROM user GROUP BY user_name HAVING num > 1) b WHERE a.user_name = b.user_name 
4            ) b
5 SET a.user_name = concat(s, server_id, ., user_name) 
6 WHERE a.user_id = b.user_id

 

mysql合服 更新相同的用户名前追加服务器编号