我的数据库中有很多用户,我想将所有用户名重置为他们的名字的第一个字母,加上他们的完整姓氏.你可以想象,有一些欺骗.在这种情况下,我想在用户名的末尾添加“2”或“3”或其他内容.如何编写查询以生成这样的唯一用户名？
UPDATE user
SET username=lower(concat(substring(first_name,1,1),last_name),UNIQUETHINGHERE)
最佳答案
CREATE TABLE bar LIKE foo;
INSERT INTO bar (id,user,first,last)
(SELECT f.id,CONCAT(SUBSTRING(f.first,f.last,(SELECT COUNT(*) FROM foo f2
WHERE SUBSTRING(f2.first,1) = SUBSTRING(f.first,1)
AND f2.last = f.last AND f2.id <= f.id
)),f.first,f.last from foo f);
DROP TABLE foo;
RENAME TABLE bar TO foo;
这依赖于主键id,因此对于插入到bar中的每个记录,我们只计算在id小于bar.id的foo中找到的重复项.
鉴于foo：
select * from foo;
+----+------+--------+--------+
| id | user | first | last |
+----+------+--------+--------+
| 1 | aaa | Roger | Hill |
| 2 | bbb | Sally | Road |
| 3 | ccc | Fred | Mount |
| 4 | ddd | Darren | Meadow |
| 5 | eee | Sharon | Road |
+----+------+--------+--------+
上面的INSERT进入吧,导致：
select * from bar;
+----+----------+--------+--------+
| id | user | first | last |
+----+----------+--------+--------+
| 1 | RHill1 | Roger | Hill |
| 2 | SRoad1 | Sally | Road |
| 3 | FMount1 | Fred | Mount |
| 4 | DMeadow1 | Darren | Meadow |
| 5 | SRoad2 | Sharon | Road |
+----+----------+--------+--------+
INSERT INTO bar (id,last)
(SELECT f3.id,CONCAT(
SUBSTRING(f3.first,f3.last,CASE f3.cnt WHEN 1 THEN '' ELSE f3.cnt END),f3.first,f3.last
FROM (
SELECT
f.id,(
SELECT COUNT(*)
FROM foo f2
WHERE SUBSTRING(f2.first,1)
AND f2.last = f.last AND f2.id <= f.id
) as cnt
FROM foo f) f3)