如何在MySQL表中插入不存在于目标列的随机数？现有实现存在插入0、非随机等问题

$sql = "INSERT INTO foldertable ( folderid )
             SELECT (
                        SELECT random_num
                        FROM (
                                SELECT FLOOR(RAND() * 4 + 1) AS random_num
                        ) AS numbers_mst_plus_1
                        WHERE random_num NOT IN (
                            0,1
                        )
                                  )";

存在的问题 #

该查询可以插入随机数，但有时会直接插入0。

问题排查结论 #

当以下生成随机数的子查询返回的数值为0或1时，会查不到有效结果，最终导致插入0：

SELECT random_num
FROM (
    SELECT FLOOR(RAND() * 4 + 1) AS random_num
) AS numbers_mst_plus_1
WHERE random_num NOT IN (0,1)

核心需求 #

需要查询语句能筛选出未在目标列存在的数值，直到获取到唯一的有效值。

我的尝试 #

首先构造了枚举1、2、3、4、5的查询语句：

SELECT random_num
FROM (
   SELECT 1 AS random_num
   UNION 
   SELECT 2 AS random_num
   UNION 
   SELECT 3 AS random_num
   UNION 
   SELECT 4 AS random_num
   UNION 
   SELECT 5 AS random_num
) AS numbers_mst_plus_1

随后尝试将其实现到插入语句中：

$sql = "INSERT INTO foldertable ( folderid )
            SELECT (SELECT random_num FROM (SELECT 1 AS random_num UNION SELECT 2 AS random_num) AS temp_table WHERE random_num NOT IN (1) LIMIT 1
                    )

上述查询会固定向表中插入2，符合预期。

但如果NOT IN子句包含目标列所有已存在的folderid（即实际场景中NOT IN子句包含表中所有已存在的folderid），就会出现问题：

$sql = "INSERT INTO foldertable ( folderid )
            SELECT (SELECT random_num FROM (SELECT 1 AS random_num UNION SELECT 2 AS random_num) AS temp_table WHERE random_num NOT IN (1 , 2) LIMIT 1
                    )
            ";

此时该查询又会向folderid列插入0。

因此给查询添加了WHERE子句，在所有ID都被用完时不再新增行：

$sql = "INSERT INTO foldertable ( folderid )
            SELECT (SELECT random_num FROM
                                        (SELECT 1 AS random_num UNION SELECT 2 AS random_num UNION SELECT 3 AS random_num )
                                        AS temp_table
                                        WHERE random_num
                                        NOT IN (
                                                SELECT folderid FROM foldertable
                                                )
                                        LIMIT 1
                    )
            WHERE ((SELECT COUNT(*) FROM foldertable) < 3)
            ";

现在当所有ID被用完时就不会新增行，但手动枚举大量UNION子句的操作十分繁琐，需要编写如下重复内容：

SELECT 1 AS random_num
UNION 
SELECT 2 AS random_num
UNION 
SELECT 3 AS random_num
UNION 
SELECT 4 AS random_num
UNION 
SELECT 5 AS random_num
.... 100000000 times 
UNION
SELECCT 100000006 AS random_num

该操作耗费大量时间，因此改用FOR LOOP动态生成枚举数值的字符串，再拼接成插入语句：

$randomNoString = "";
for ($i=0; $i < ($maxFolderId - $minFolderId + 1 ); $i++) { 
    if($i === 0){
        $randomNoString ="SELECT ".($minFolderId + $i) . " AS random_num" ;
    }
    else {
        $randomNoString =  $randomNoString ." UNION " . "SELECT ".($minFolderId + $i) . " AS random_num";
    }
}

$sql = "INSERT INTO foldertable ( folderid )
        SELECT (SELECT random_num FROM
                                    ({$randomNoString})
                                    AS temp_table
                                    WHERE random_num
                                    NOT IN (
                                            SELECT folderid FROM foldertable
                                            )
                                    LIMIT 1
                )
        WHERE ((SELECT COUNT(*) FROM foldertable) < {$maxFolderCount})
        ";

当前方案的问题 #

• 生成的folderid虽然唯一，但失去了随机性
• 拼接生成的SQL字符串过长，大概率存在性能问题

限制条件 #

• 无法给folderid列设置唯一约束

如果需要生成6位的随机字符串，应该如何实现？

内容的提问来源于stack exchange，提问作者S B RAKESH RATH