php - MySQL - 有效地插入 70000 个随机唯一字符串

Question

我正在做一个项目，我应该在其中生成至少 70000 个包含 8 个字母数字字符的代码。代码必须是唯一的。目前我正在使用 php 通过以下函数生成这些代码:

function random_unique_serial($length, PDO $conn) {
    $codeCheck=FALSE;
    while (!$codeCheck) {
        $characters = '0123456789abcdefghijklmnopqrstuvwxyz';
        $charactersLength = strlen($characters);
        $randomCode = '';
        for ($i = 0; $i < $length; $i++) {
            $randomCode .= $characters[rand(0, $charactersLength - 1)];
        }
        $sql = "SELECT * FROM codes WHERE code=:code";
        $st = $conn->prepare($sql);
        $st->bindvalue(":code", $randomCode, PDO::PARAM_STR);
        $st->execute();
        $count = $st->rowcount();
        if ($count==0) {
            $codeCheck=TRUE;
        } else {
            $codeCheck=FALSE;
        }
    }
    return $randomCode;
}

如您所见，此代码会检查数据库中生成的每个代码，以确保它不是重复的。这在理论上应该可行。然而，这非常慢并且会导致请求超时。我尝试增加执行时间，但这也没有帮助。

然后我决定使用数据库方面的方法并使用了这个解决方案: Generating a random & unique 8 character string using MySQL

这也很慢，一些生成的代码长度不到 8 个字符。

您能提出一个更好的解决方案吗？

Answer 1

创建表结构:

CREATE TABLE t (code CHAR(8) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL UNIQUE);

定义一个 PHP 函数来生成随机字符串:

function random_string(integer $length = 8): string {
    return bin2hex(mcrypt_create_iv(ceil($length/2), MCRYPT_DEV_URANDOM));
}

使用 PHP 构建一个多值 INSERT 语句，将其 ram 到数据库中，计算插入的数量，并重复直到插入所需的数量:

function insert_records(\PDO $pdo, integer $need = 70000): void {
    $have = 0;
    while ($have < $need) {
        // generate multi value INSERT
        $sql = 'INSERT IGNORE INTO t VALUES ';
        for ($i = 1; $i < $need; $i++) {
            $sql .= sprintf('("%s"),', random_string());
        }
        $sql .= sprintf('("%s");', random_string());

        // pass to database and ask how many records were inserted
        $result = $pdo->query($sql);
        $count  = $result->rowCount();

        // adjust bookkeeping values so we know how many we have and how many
        // we need
        $need -= $count;
        $have += $count;
    }
}

在我的机器 (Amazon Linux c2.small) 上，70k 条记录的运行时间约为 2 秒:

real    0m2.136s
user    0m1.256s
sys     0m0.212s

这段代码中的相关技巧是:

• 发送最少数量的 SQL 语句以生成所需数量的记录。使用多值插入 - INSERT INTO ... VALUES (), (), ... (); - 确实有帮助，因为它最大限度地减少了 MySQL 必须执行的语句处理总量 < em>并且它告诉我们插入了多少条记录，而无需执行其他查询。
• 使用 INSERT IGNORE 来避免必须检查我们插入的每个代码是否存在，这确实非常昂贵。
• 使用尽可能快的字符串生成函数来满足我们的需求。以我的经验，mcrypt_create_iv 是一种快速的加密安全生成器，因此它提供了安全性和性能的理想平衡。
• 使用 ASCII 字符集和固定宽度 CHAR 来消除不必要的字节开销，并使用 UNIQUE 来强制重复数据删除。