主页 / dba / 问题 / 210689
Accepted
user1525248
Asked: 2018-06-28 00:43:30 +0800 CST

如何用随机名称替换用户名？

  • 772

如何仅在 mysql DB 上使用 SQL使用两个相关表中的随机名称替换users表中真实用户的名字和姓氏： random_first_names和random_last_names。我们的 users 表包含超过 25 万条记录，每个 rundome 表都包含超过 5000 个名称，应该为users表中的每条记录随机选择。是否可以仅使用 SQL 来实现？

[更新]

我根据 Rick 的输入解决了这个问题，并在下面分享了完整的 SQL 脚本。

mysql random

3 个回答

  1. Best Answer

    如果你觉得更新太慢，我建议下面会快1000倍左右。

    循环（可以在存储过程中完成）...

    1. [重新]使用随机排序的集合 first_names 创建一个表，其 `PRIMARY KEY 为 1..5000。last_names 同上（第二个表）。
    2. 多表UPDATE连接到两个随机表的“下一个”5000 行。使用ON Users.id % 5000 = RandomFirstNames.id（等）

    结束循环

    洗牌（循环的第 1 步）类似于

    CREATE TABLE RandomFirstNames (
    id SMALLINT UNSIGNED AUTO_INCREMENT,
    first_name VARCHAR(...),
    PRIMARY KEY(id) )
SELECT first_name FROM FirstNames ORDER BY RAND();

    在 OP 的 UPDATE 之后

    不要做

    SELECT count(id) INTO count_names FROM _RandomFirstNames;

    相反，请执行一次：

    SELECT @mask_ct := COUNT(*) FROM _masked_names;

    并使用@mask_ct代替count_names;

    至于跳过CREATE TABLE _RandomFirstNames的id，没有id，那就ALTER TABLE _RandomFirstNames ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY;去获取id。这应该为您提供没有间隙的 id（除非您在任何类型的多主集群上）。

    • 2

  2. 您可以ORDER BY rand()结合使用LIMIT 1来选择随机名称表的随机行。

    UPDATE users
       SET first_name = (SELECT name
                                FROM random_first_names
                                ORDER BY rand()
                                LIMIT 1),
           last_name = (SELECT name
                               FROM random_last_names
                               ORDER BY rand()
                               LIMIT 1);
    • 1

  3. 感谢 Rick and Sticky Bit 的输入。Sticky Bit 的解决方案需要很长时间才能运行。Rick 的回答是最接近的，他的评论帮助我创建了我在下面分享的完整解决方案。

    首先，创建临时表来存储随机名称

    DROP TABLE IF EXISTS _RandomFirstNames;
CREATE TABLE _RandomFirstNames (first_name VARCHAR(255));

DROP TABLE IF EXISTS _RandomLastNames;
CREATE TABLE _RandomLastNames (last_name VARCHAR(255));

    然后创建了一个过程来用随机名称填充这些表，以确保每个可能的用户 ID 都有一个名字和一个姓氏。

    DELIMITER $$
DROP PROCEDURE IF EXISTS prepare_randon_names$$
CREATE PROCEDURE prepare_randon_names()
BEGIN
        SELECT @users := id FROM users ORDER BY id DESC LIMIT 0, 1;

        SELECT @mask_ct := COUNT(*) FROM _masked_names._firstnames;
        SELECT @loops := @users/@mask_ct;
        SELECT @count := 0;
        WHILE @count  < @loops DO
                INSERT INTO _RandomFirstNames (first_name)
                SELECT firstname FROM _masked_names._firstnames ORDER BY RAND();

                SELECT @count := @count+1;
        END WHILE;

        SELECT @mask_ct := COUNT(*) FROM _masked_names._lastnames;
        SELECT @loops := @users/@mask_ct;
        SELECT @count := 0;
        WHILE @count  < @loops DO
                INSERT INTO _RandomLastNames (last_name)
                SELECT lastname FROM _masked_names._lastnames ORDER BY RAND();

                SELECT @count := @count+1;
        END WHILE;
END$$
DELIMITER;

    我们现在可以执行它并向填充的表添加增量 ID

    CALL prepare_randon_names();

ALTER TABLE _RandomFirstNames ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE _RandomLastNames ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY;

    现在我们可以通过使用我们上面创建的随机名称连接两个新表来使用随机名称更新用户表

    UPDATE users u
left join _RandomFirstNames f on f.id = u.id
left join _RandomLastNames l on l.id = u.id
       SET u.first_name = f.first_name,
           u.last_name = l.last_name;

    最后一步，删除具有随机名称的表，因为我们不再需要这些表

    DROP TABLE IF EXISTS _RandomFirstNames;
DROP TABLE IF EXISTS _RandomLastNames;

    笔记摘要

    • 在用随机名称填充表后添加主键解决了索引跳过计数的问题。例如，在 _RandomFirstNames 中，ID 顺序增加直到 ID 5163，然后跳到 8192（增加 3,029），然后顺序增加直到 13354，然后再次跳到 3,029 到 16383。_RandomFirstNames 是根据 _masked_names._firstnames 生成的，其中包含5163 个名字。

    • users当针对具有 250,000 条记录的表运行时，避免在 while 循环中使用 count(...) 将速度提高一秒

    • 1

相关问题