从名字和姓氏生成唯一的用户名? [英] Generate unique username from first and last name?

问题描述

我的数据库中有很多用户，我想将其所有用户名重设为其名字的首字母以及全名.您可以想象，有一些骗子.在这种情况下，我想在用户名的末尾添加"2"或"3"或其他内容.我将如何编写查询以生成像这样的唯一用户名?

I've got a bunch of users in my database and I want to reset all their usernames to the first letter of their first name, plus their full last name. As you can imagine, there are some dupes. In this scenario, I'd like to add a "2" or "3" or something to the end of the username. How would I write a query to generate a unique username like this?

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,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中的每条记录，我们只计算在foo中发现的重复项，且id小于bar.id.

This relies on a primary key id, so for each record inserted into bar, we only count duplicates found in foo with id less than bar.id.

给出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转换为bar，结果:

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"，

---

To remove the "1" from the end of user names,

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)

这篇关于从名字和姓氏生成唯一的用户名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！