MySQL中能否用单条查询打乱字符串列内以分隔符存储的列表

可以通过单条SQL完成该需求，MySQL 8.0及以上版本支持CTE和窗口函数，实现起来更简洁，低版本可通过变通方案实现，但复杂度较高。

1. 查询打乱后的结果（不修改原表） #

核心思路是先递归拆分每一行的|分隔字符串为独立元素，给每个元素随机排序后重新拼接：

WITH RECURSIVE split_elements AS (
    SELECT 
        id, -- 替换为你的表实际主键字段
        list AS original_list,
        SUBSTRING_INDEX(list, '|', 1) AS element,
        SUBSTRING(list, LENGTH(SUBSTRING_INDEX(list, '|', 1)) + 2) AS remaining_str
    FROM your_table_name -- 替换为你的实际表名
    UNION ALL
    SELECT 
        id,
        original_list,
        SUBSTRING_INDEX(remaining_str, '|', 1),
        SUBSTRING(remaining_str, LENGTH(SUBSTRING_INDEX(remaining_str, '|', 1)) + 2)
    FROM split_elements
    WHERE remaining_str != ''
),
shuffled_elements AS (
    SELECT 
        id,
        element,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY RAND()) AS sort_order
    FROM split_elements
)
SELECT 
    id,
    GROUP_CONCAT(element ORDER BY sort_order SEPARATOR '|') AS shuffled_list
FROM shuffled_elements
GROUP BY id;

2. 直接更新原表字段 #

将上述查询逻辑和原表关联，直接更新list字段：

UPDATE your_table_name t
JOIN (
    WITH RECURSIVE split_elements AS (
        SELECT 
            id,
            list AS original_list,
            SUBSTRING_INDEX(list, '|', 1) AS element,
            SUBSTRING(list, LENGTH(SUBSTRING_INDEX(list, '|', 1)) + 2) AS remaining_str
        FROM your_table_name
        UNION ALL
        SELECT 
            id,
            original_list,
            SUBSTRING_INDEX(remaining_str, '|', 1),
            SUBSTRING(remaining_str, LENGTH(SUBSTRING_INDEX(remaining_str, '|', 1)) + 2)
        FROM split_elements
        WHERE remaining_str != ''
    ),
    shuffled_elements AS (
        SELECT 
            id,
            element,
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY RAND()) AS sort_order
        FROM split_elements
    )
    SELECT 
        id,
        GROUP_CONCAT(element ORDER BY sort_order SEPARATOR '|') AS shuffled_list
    FROM shuffled_elements
    GROUP BY id
) res ON t.id = res.id
SET t.list = res.shuffled_list;

• 上述写法默认你的表存在唯一主键id用来区分不同行，可根据实际业务替换为其他唯一标识字段
• MySQL默认CTE递归深度为1000，如果单条list字段的元素数量超过1000，需要先执行SET cte_max_recursion_depth = 你需要的最大数值;调整递归深度
• MySQL 5.7及更低版本不支持CTE语法，单条SQL实现复杂度极高，更建议升级到8.0+版本后使用上述方案
• 执行更新操作前请先备份数据，避免操作失误导致数据丢失

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