mysql合服 更新相同的使用者名稱前追加伺服器編號

標籤：blog   cat   com   span   _id   bsp   相同   set   tab   

表結構:

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            ) b5 SET a.user_name = concat(‘s‘, server_id, ‘.‘, user_name) 6 WHERE a.user_id = b.user_id

 

mysql合服 更新相同的使用者名稱前追加伺服器編號