mysql 姓名_mysql – 从名字和姓氏生成唯一的用户名？

CREATE TABLE bar LIKE foo;

INSERT INTO bar (id,user,first,last)

(SELECT f.id,CONCAT(SUBSTRING(f.first,1,1),f.last,

(SELECT COUNT(*) FROM foo f2

WHERE SUBSTRING(f2.first,1,1) = SUBSTRING(f.first,1,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 |

+----+----------+--------+--------+

要从用户名末尾删除“1”,

INSERT INTO bar (id,user,first,last)

(SELECT f3.id,

CONCAT(

SUBSTRING(f3.first,1,1),

f3.last,

CASE f3.cnt WHEN 1 THEN '' ELSE f3.cnt END),

f3.first,

f3.last

FROM (

SELECT

f.id,

f.first,

f.last,

(

SELECT COUNT(*)

FROM foo f2

WHERE SUBSTRING(f2.first,1,1) = SUBSTRING(f.first,1,1)

AND f2.last = f.last AND f2.id <= f.id

) as cnt

FROM foo f) f3)