MySQL不使用PHP循环生成唯一类随机6位数字的实现方法

方案背景 #

核心需求：不依赖PHP端循环校验，在MySQL体系内生成与全表已有记录值不重复、外观无规律的唯一数字，适配6位数字业务场景，支撑百万级数据量下的高性能读写，同时支持已删除ID的自动复用。

常见错误实现缺陷 #

网上流传的类如下随机查询方案，存在偶发重复值、无法遍历校验全表存量值的问题，数据量增长后性能会指数级下降，完全无法满足生产要求：

SELECT *, FLOOR(RAND() * 9) AS random_number FROM Table1 
WHERE "random_number" NOT IN (SELECT tracker FROM Table1)

而预生成数字池的优化思路，因为缺乏可落地的记录流转、复用机制，实际生产中很难落地执行。 #

最终落地方案（基于@Wiimm 实现思路） #

该方案完全不需要PHP循环做重复校验，最多支持填充999999个外观随机的唯一6位数字，自带已删除行ID复用机制，随机数生成复杂度为O(1)，性能不会随数据量增长出现衰减，部署步骤如下：

• 复制原业务表的全量结构（含字段、索引、约束），命名为table_deleted，作为已删除记录的暂存表，用于后续ID复用。
• 在MySQL中创建删除前置触发器，原表执行删除操作时，自动将待删除行迁移到table_deleted表留存，代码如下：

DELIMITER $$
CREATE TRIGGER `table_before_delete` BEFORE DELETE
ON `your_table` FOR EACH ROW
BEGIN
    INSERT INTO table_deleted
    SELECT * FROM your_table WHERE id = old.id;
END ; $$
DELIMITER ;

• 创建第二个更新后置触发器，table_deleted表的记录被更新后，自动将对应行迁回原业务表，完成ID复用流程，代码如下：

DELIMITER $$
CREATE TRIGGER `table_after_update` AFTER UPDATE
ON `your_table` FOR EACH ROW
BEGIN
    INSERT INTO your_table
    SELECT * FROM table_deleted WHERE id = old.id;
END ; $$
DELIMITER ;

• 编写PHP端业务逻辑，注意提前将原业务表中存储随机数的字段默认值设置为NULL，代码如下：

// 检查暂存表是否存在可复用的已删除记录
$deleted = $db->query('SELECT COUNT(*) AS num_rows FROM table_deleted');
$deletedcount = $deleted->fetchColumn();

if($deletedcount > 0) {
    // 优先复用ID最小的已删除记录，更新业务字段
    $update = $db->prepare("UPDATE table_deleted SET value1= ?, value2= ?, value3= ?, value4= ? ORDER BY id LIMIT 1");
    $update->execute(array($value1, $value2, $value3, $value4));
    // 更新完成后删除暂存表对应记录，触发器会自动将更新后的行迁回原业务表
    $delete = $db->prepare("DELETE from table_deleted ORDER BY id LIMIT 1");
    $delete->execute();
} else {
    // 暂存表无可复用记录时，直接插入新的业务数据，随机数字段留空
    $query = $db->prepare("INSERT INTO your_table SET value1= ?, value2= ?, value3= ?, value4= ?");
    $query->execute(array($value1, $value2, $value3, $value4));
    // 基于互质乘数取模算法生成外观随机的唯一6位数字，与自增ID一一对应，绝对无重复
    $last_id = $db->lastInsertId();
    $number = str_pad($last_id * 683567 % 1000000, 6, '0', STR_PAD_LEFT);
    // 将生成的随机数回写到当前新增行
    $insertnumber = $db->prepare("UPDATE your_table SET number= :number where id = :id");
    $insertnumber->execute(array("number" => $number, "id" => $last_id));
}

注意：所有配置完成后，MySQL触发器会自动完成已删除记录留存、复用记录迁回的全流程数据流转，不需要额外编写循环校验逻辑。随机数生成逻辑选用的乘数683567与1000000互质，可保证0-999999区间内每个数字恰好出现一次，从根源上避免重复问题，同时生成的数字无连续规律，满足外观随机的要求。

内容的提问来源于stack exchange，提问作者Deniz Şafak