请求基于MySQL创建实现格式转换的多替换存储过程或函数

针对你需要将旧格式联系偏好数据转换为新格式的需求，这里提供一个基于MySQL 8.0+的存储过程，利用正则表达式替代多次嵌套REPLACE，提升转换效率。

旧格式示例 #

1. Preferred Time: Monday (8-20), Tuesday (8-12), Wednesday (8-12), Saturday (8-12), Other Type Of Contact: SMS, EMAIL
2. Preferred Time: Monday (8-12,17-20), Tuesday (8-17), Wednesday (8-12), Thursday (8-20)

新格式示例 #

1. MON08-12, MON12-17, MON17-20, TUE08-12, WED08-12, SAT08-12, SMS, EMAIL
2. MON08-12, MON17-20, TUE08-12, TUE12-17, WED08-12, THU08-12, THU12-17, THU17-20

转换规则 #

• 修剪列值的前导/尾随空格
• 移除Preferred Time:和Other Type Of Contact:标签
• 将单独的数字8替换为08
• 展开时间区间：
  • (8-20) → 08-12, 12-17, 17-20
  • (8-17) → 08-12, 12-17
  • (12-20) → 12-17, 17-20
• 星期转换为缩写：Monday→MON，Tuesday→TUE，Wednesday→WED，Thursday→THU，Friday→FRI，Saturday→SAT
• Saturday的时间固定为SAT08-12，不参与区间展开

存储过程代码 #

DELIMITER //

CREATE PROCEDURE ConvertContactPreference(IN targetTable VARCHAR(64), IN targetColumn VARCHAR(64))
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE originalText TEXT;
    DECLARE resultText TEXT;
    DECLARE rowId INT; -- 若表主键不是id，请修改此处字段名

    -- 游标遍历非空记录
    DECLARE dataCursor CURSOR FOR 
        SELECT id, TRIM(targetColumn) 
        FROM targetTable 
        WHERE targetColumn IS NOT NULL AND targetColumn != '';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN dataCursor;
    process_loop: LOOP
        FETCH dataCursor INTO rowId, originalText;
        IF done THEN
            LEAVE process_loop;
        END IF;

        -- 移除多余标签
        SET resultText = REGEXP_REPLACE(originalText, 'Preferred Time: |Other Type Of Contact: ', '');
        
        -- 替换星期为缩写，处理Saturday固定时间
        SET resultText = REGEXP_REPLACE(resultText, 'Monday', 'MON');
        SET resultText = REGEXP_REPLACE(resultText, 'Tuesday', 'TUE');
        SET resultText = REGEXP_REPLACE(resultText, 'Wednesday', 'WED');
        SET resultText = REGEXP_REPLACE(resultText, 'Thursday', 'THU');
        SET resultText = REGEXP_REPLACE(resultText, 'Friday', 'FRI');
        SET resultText = REGEXP_REPLACE(resultText, 'Saturday \\(8-12\\)', 'SAT08-12');
        
        -- 将单8替换为08（避免影响区间内的数字）
        SET resultText = REGEXP_REPLACE(resultText, '(\\D)8(\\D)', '\\108\\2');
        
        -- 展开时间区间
        SET resultText = REGEXP_REPLACE(resultText, '\\((08-20)\\)', '08-12, 12-17, 17-20');
        SET resultText = REGEXP_REPLACE(resultText, '\\((08-17)\\)', '08-12, 12-17');
        SET resultText = REGEXP_REPLACE(resultText, '\\((12-20)\\)', '12-17, 17-20');
        
        -- 处理多时间区间（如MON (08-12,17-20) → MON08-12, MON17-20）
        SET resultText = REGEXP_REPLACE(resultText, '(MON|TUE|WED|THU|FRI) \\(([^)]+)\\)', 
            CONCAT('\\1', REPLACE('\\2', ',', ', \\1')));
        
        -- 清理格式：统一逗号分隔符，移除多余空格
        SET resultText = REGEXP_REPLACE(resultText, '\\s*,\\s*', ', ');
        SET resultText = TRIM(resultText);

        -- 更新当前记录
        SET @updateStmt = CONCAT('UPDATE ', targetTable, ' SET ', targetColumn, ' = ? WHERE id = ?');
        PREPARE sqlStmt FROM @updateStmt;
        SET @result = resultText;
        SET @id = rowId;
        EXECUTE sqlStmt USING @result, @id;
        DEALLOCATE PREPARE sqlStmt;
    END LOOP;

    CLOSE dataCursor;
END //

DELIMITER ;

使用步骤 #

1. 确认你的MySQL版本为8.0或以上（正则表达式函数依赖此版本）
2. 如果目标表的主键不是id，请修改游标中的主键字段名
3. 调用存储过程，传入表名和列名：

CALL ConvertContactPreference('your_contact_table', 'preference_column');

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