UPDATE userSET 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;
@H_404_1@这依赖于主键ID,因此对于插入到bar中的每个记录,我们只计算在ID小于bar.ID的foo中找到的重复项.@H_404_1@鉴于foo:@H_404_1@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 | +----+------+--------+--------+
@H_404_1@上面的INSERT进入吧,导致:@H_404_1@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 | +----+----------+--------+--------+
@H_404_1@要从用户名末尾删除“1”,@H_404_1@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)
总结 以上是内存溢出为你收集整理的mysql – 从名字和姓氏生成唯一的用户名?全部内容,希望文章能够帮你解决mysql – 从名字和姓氏生成唯一的用户名?所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)