MySQL批量更新旧数据：拆分selprod_code排序后重新拼接

核心思路 #

通过递归CTE拆分selprod_code的各段ID，保留首位主产品ID，将后续选项ID按升序排序后重新拼接，最终更新回原表。

具体SQL实现 #

1. 先验证拆分与排序逻辑（可选，用于确认结果）

WITH RECURSIVE split_codes AS (
    -- 初始拆分：提取主产品ID和第一个选项ID（如果有）
    SELECT
        selprod_id, -- 替换为表的唯一主键，无主键则用能唯一标识行的字段
        selprod_code,
        SUBSTRING_INDEX(selprod_code, '_', 1) AS main_id,
        SUBSTRING(selprod_code, LENGTH(SUBSTRING_INDEX(selprod_code, '_', 1)) + 2) AS remaining_options,
        SUBSTRING_INDEX(SUBSTRING(selprod_code, LENGTH(SUBSTRING_INDEX(selprod_code, '_', 1)) + 2), '_', 1) AS option_id,
        1 AS level
    FROM tbl_seller_products
    WHERE selprod_code LIKE '%_%' -- 筛选包含选项ID的记录
    
    UNION ALL
    
    -- 递归拆分剩余的选项ID
    SELECT
        selprod_id,
        selprod_code,
        main_id,
        SUBSTRING(remaining_options, LENGTH(option_id) + 2),
        SUBSTRING_INDEX(SUBSTRING(remaining_options, LENGTH(option_id) + 2), '_', 1),
        level + 1
    FROM split_codes
    WHERE remaining_options != ''
),
-- 收集每个记录的所有选项ID并排序
sorted_options AS (
    SELECT
        selprod_id,
        main_id,
        GROUP_CONCAT(option_id ORDER BY option_id ASC SEPARATOR '_') AS sorted_options_str
    FROM split_codes
    GROUP BY selprod_id, main_id
),
-- 拼接最终的selprod_code
final_codes AS (
    SELECT
        sp.selprod_id,
        CONCAT(so.main_id, '_', so.sorted_options_str) AS new_selprod_code
    FROM tbl_seller_products sp
    JOIN sorted_options so ON sp.selprod_id = so.selprod_id
    -- 同时处理无选项ID的记录（直接保留原主ID）
    UNION ALL
    SELECT
        selprod_id,
        selprod_code AS new_selprod_code
    FROM tbl_seller_products
    WHERE selprod_code NOT LIKE '%_%'
)
-- 查询验证结果
SELECT selprod_id, selprod_code, new_selprod_code
FROM final_codes;

2. 执行UPDATE操作

确认上述SELECT结果符合预期后，执行以下更新语句：

WITH RECURSIVE split_codes AS (
    SELECT
        selprod_id,
        selprod_code,
        SUBSTRING_INDEX(selprod_code, '_', 1) AS main_id,
        SUBSTRING(selprod_code, LENGTH(SUBSTRING_INDEX(selprod_code, '_', 1)) + 2) AS remaining_options,
        SUBSTRING_INDEX(SUBSTRING(selprod_code, LENGTH(SUBSTRING_INDEX(selprod_code, '_', 1)) + 2), '_', 1) AS option_id,
        1 AS level
    FROM tbl_seller_products
    WHERE selprod_code LIKE '%_%'
    
    UNION ALL
    
    SELECT
        selprod_id,
        selprod_code,
        main_id,
        SUBSTRING(remaining_options, LENGTH(option_id) + 2),
        SUBSTRING_INDEX(SUBSTRING(remaining_options, LENGTH(option_id) + 2), '_', 1),
        level + 1
    FROM split_codes
    WHERE remaining_options != ''
),
sorted_options AS (
    SELECT
        selprod_id,
        main_id,
        GROUP_CONCAT(option_id ORDER BY option_id ASC SEPARATOR '_') AS sorted_options_str
    FROM split_codes
    GROUP BY selprod_id, main_id
)
UPDATE tbl_seller_products sp
JOIN sorted_options so ON sp.selprod_id = so.selprod_id
SET sp.selprod_code = CONCAT(so.main_id, '_', so.sorted_options_str)
WHERE sp.selprod_code LIKE '%_%';

-- 无选项ID的记录无需更新，可跳过

注意事项 #

• 必须确保表有唯一主键（如示例中的selprod_id），无主键则替换为其他能唯一标识每行的字段，避免批量更新出错。
• 执行UPDATE前务必备份数据，或先通过SELECT语句验证结果准确性。
• 若选项ID为数字类型，需按数值排序时，可将ORDER BY option_id ASC改为ORDER BY CAST(option_id AS UNSIGNED) ASC。

内容的提问来源于stack exchange，提问作者Death-is-the-real-truth