如何编写SQL脚本实现表内指定列数据按规则跨行打乱？

需求明确：

• c1 为主键，保持不变
• c2、c3 单独跨行随机打乱（列内数据随机重新分配）
• c4、c5 成对跨行随机打乱（两列的组合保持关联，整体随机分配）
• 主键为随机唯一标识符，与行号无关

打乱前后示例 #

打乱前：

打乱后：

MySQL 实现脚本 #

通过临时表生成各列（或列对）的随机排序映射，关联主键执行更新：

-- 1. 备份原表（非生产环境也建议操作）
CREATE TABLE original_table_backup LIKE your_table_name;
INSERT INTO original_table_backup SELECT * FROM your_table_name;

-- 2. 生成c2的随机映射临时表
CREATE TEMPORARY TABLE temp_c2 AS
SELECT 
    c1,
    (SELECT c2 FROM your_table_name ORDER BY RAND() LIMIT 1 OFFSET rn-1) AS shuffled_c2
FROM (
    SELECT c1, ROW_NUMBER() OVER () AS rn FROM your_table_name
) AS t1;

-- 3. 生成c3的随机映射临时表
CREATE TEMPORARY TABLE temp_c3 AS
SELECT 
    c1,
    (SELECT c3 FROM your_table_name ORDER BY RAND() LIMIT 1 OFFSET rn-1) AS shuffled_c3
FROM (
    SELECT c1, ROW_NUMBER() OVER () AS rn FROM your_table_name
) AS t2;

-- 4. 生成c4、c5成对的随机映射临时表
CREATE TEMPORARY TABLE temp_c4c5 AS
SELECT 
    t3.c1,
    t4.c4 AS shuffled_c4,
    t4.c5 AS shuffled_c5
FROM (
    SELECT c1, ROW_NUMBER() OVER () AS rn FROM your_table_name
) AS t3
JOIN (
    SELECT c4, c5, ROW_NUMBER() OVER (ORDER BY RAND()) AS rn FROM your_table_name
) AS t4 ON t3.rn = t4.rn;

-- 5. 执行更新
UPDATE your_table_name
JOIN temp_c2 ON your_table_name.c1 = temp_c2.c1
JOIN temp_c3 ON your_table_name.c1 = temp_c3.c1
JOIN temp_c4c5 ON your_table_name.c1 = temp_c4c5.c1
SET 
    your_table_name.c2 = temp_c2.shuffled_c2,
    your_table_name.c3 = temp_c3.shuffled_c3,
    your_table_name.c4 = temp_c4c5.shuffled_c4,
    your_table_name.c5 = temp_c4c5.shuffled_c5;

PostgreSQL 实现脚本 #

利用窗口函数生成随机排序的行号，通过行号关联实现打乱映射：

-- 1. 备份原表
CREATE TABLE original_table_backup AS SELECT * FROM your_table_name;

-- 2. 执行更新，一次性完成所有列的打乱
WITH 
shuffled_c2 AS (
    SELECT c2, ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn FROM your_table_name
),
shuffled_c3 AS (
    SELECT c3, ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn FROM your_table_name
),
shuffled_c4c5 AS (
    SELECT c4, c5, ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn FROM your_table_name
),
original_rn AS (
    SELECT c1, ROW_NUMBER() OVER () AS rn FROM your_table_name
)
UPDATE your_table_name t
SET 
    c2 = sc2.c2,
    c3 = sc3.c3,
    c4 = sc4c5.c4,
    c5 = sc4c5.c5
FROM original_rn or_n
JOIN shuffled_c2 sc2 ON or_n.rn = sc2.rn
JOIN shuffled_c3 sc3 ON or_n.rn = sc3.rn
JOIN shuffled_c4c5 sc4c5 ON or_n.rn = sc4c5.rn
WHERE t.c1 = or_n.c1;

注意事项 #

• 替换脚本中的 your_table_name 为实际表名
• 每次执行脚本会生成不同的打乱结果
• 仅用于测试数据匿名化，请勿在生产环境直接执行
• 数据量较大时，临时表方式（MySQL）比子查询更高效

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