MySQL更新存量行唯一6位非递增随机数，解决部分行值为空问题

你的SQL执行后出现部分行字段为NULL，是几个逻辑漏洞叠加导致的：

• 标识符引用错误：在WHERE子句中使用带双引号的"subIdentifier"做NOT IN判断，MySQL默认配置下双引号包裹的内容是字符串字面量，不是你在SELECT中定义的别名，这个判断条件从一开始就没有生效。
• 执行顺序逻辑冲突：SQL的执行顺序是WHERE阶段早于SELECT阶段，就算你不用双引号，在WHERE子句中引用SELECT里定义的subIdentifier别名，数据库根本无法识别对应值，属于无效过滤。
• 去重过滤误删行：内层CTE做GROUP BY subIdentifier时，只要生成的随机数出现重复、或者和表中已存在的uniqueIdentifier重复，这些重复值对应的行都会被直接剔除，进入CTE的行总数自然会小于全表2万条的规模；后续关联更新时，这些被剔除的行找不到对应的新编号，就会被直接更新为NULL。
• 多余的冗余处理：你用LPAD()包裹随机数属于无效操作，FLOOR(RAND() * (999999 - 100000) + 100000)本身生成的就是100000-999999区间的6位整数，不需要补位转字符串。

不需要逐行生成随机数再反复查库判重，利用窗口函数直接生成无重复的可用编号，性能更高、逻辑更稳定：

WITH pending_rows AS (
    -- 筛选所有待填充的行：根据你的表结构，初始默认值为1的行就是未被赋值的行，可根据实际情况调整条件
    SELECT id 
    FROM property 
    WHERE uniqueIdentifier = 1
),
id_pool AS (
    -- 生成全局不重复的新编号池
    SELECT 
        pr.id,
        -- 基于已使用的最大编号做连续偏移，从根源上避免编号重复
        100000 + ROW_NUMBER() OVER (ORDER BY RAND()) + used_cnt.offset AS new_unique_id
    FROM pending_rows pr
    CROSS JOIN (
        -- 计算当前已经使用了多少个合法的6位编号
        SELECT COUNT(*) AS offset 
        FROM property 
        WHERE uniqueIdentifier BETWEEN 100000 AND 999999
    ) used_cnt
)
-- 关联执行更新
UPDATE property p
INNER JOIN id_pool ip ON p.id = ip.id
SET p.uniqueIdentifier = ip.new_unique_id;

• 用窗口函数ROW_NUMBER()给每个待更新的行分配连续的偏移值，搭配ORDER BY RAND()打乱编号和行的顺序关联，实现非递增的随机分布效果，从根源上避免随机数碰撞，完全不需要嵌套子查询判重，执行效率比原逻辑高一个数量级。
• 采用INNER JOIN做关联更新，只有匹配到合法新编号的行才会被更新，不会出现CTE缺行导致字段被置为NULL的问题。
• 编号生成逻辑自动跳过已经使用过的数值，不会和存量的uniqueIdentifier产生冲突。
• 对于2万行的规模，6位编号的总可用空间是90万个，随机碰撞概率极低，完全可以覆盖存量需求。

注意：后续写SQL时，列名、表名等标识符需要用反引号`包裹做转义，不要使用双引号，避免被数据库识别为字符串字面量导致逻辑错误。

内容的提问来源于stack exchange，提问作者Piyush