如何在MySQL中将括号内字符串替换为带序号的<span>标签

针对你的需求，分MySQL版本提供两种实现方案：

方案一：MySQL 8.0+ 用递归CTE（无需自定义函数） #

如果你的MySQL版本是8.0及以上，推荐用递归公共表表达式（CTE）处理，无需额外创建函数：

第一步：先预览替换结果（安全测试） #

WITH RECURSIVE cte AS (
    -- 初始查询：获取每条记录的原始内容、第一个变量的位置和初始序号
    SELECT 
        id,
        query AS original_text,
        query AS modified_text,
        LOCATE('[', query) AS start_pos,
        LOCATE(']', query, LOCATE('[', query)) AS end_pos,
        1 AS var_num
    FROM your_table
    WHERE query LIKE '%[%]%' -- 只处理包含变量的记录
    
    UNION ALL
    
    -- 递归替换：每次替换一个变量，然后找下一个变量的位置，序号递增
    SELECT 
        id,
        original_text,
        INSERT(modified_text, start_pos, end_pos - start_pos + 1, 
               CONCAT('<span class="variable-', var_num, '">', SUBSTRING(original_text, start_pos, end_pos - start_pos + 1), '</span>')
              ) AS modified_text,
        LOCATE('[', original_text, end_pos + 1) AS start_pos,
        LOCATE(']', original_text, LOCATE('[', original_text, end_pos + 1)) AS end_pos,
        var_num + 1 AS var_num
    FROM cte
    WHERE start_pos > 0 -- 还有未替换的变量时继续递归
)
-- 取每条记录最后一次替换的结果（即序号最大的那一行）
SELECT 
    id,
    modified_text AS final_query
FROM (
    SELECT 
        id,
        modified_text,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY var_num DESC) AS rn
    FROM cte
) t
WHERE rn = 1;

第二步：直接更新表数据 #

确认预览结果正确后，执行以下SQL更新表：

WITH RECURSIVE cte AS (
    SELECT 
        id,
        query AS original_text,
        query AS modified_text,
        LOCATE('[', query) AS start_pos,
        LOCATE(']', query, LOCATE('[', query)) AS end_pos,
        1 AS var_num
    FROM your_table
    WHERE query LIKE '%[%]%'
    
    UNION ALL
    
    SELECT 
        id,
        original_text,
        INSERT(modified_text, start_pos, end_pos - start_pos + 1, 
               CONCAT('<span class="variable-', var_num, '">', SUBSTRING(original_text, start_pos, end_pos - start_pos + 1), '</span>')
              ) AS modified_text,
        LOCATE('[', original_text, end_pos + 1) AS start_pos,
        LOCATE(']', original_text, LOCATE('[', original_text, end_pos + 1)) AS end_pos,
        var_num + 1 AS var_num
    FROM cte
    WHERE start_pos > 0
)
UPDATE your_table t
JOIN (
    SELECT 
        id,
        modified_text
    FROM (
        SELECT 
            id,
            modified_text,
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY var_num DESC) AS rn
        FROM cte
    ) c
    WHERE rn = 1
) cte ON t.id = cte.id
SET t.query = cte.modified_text;

方案二：MySQL 5.x 版本（自定义函数） #

如果你的MySQL是5.x版本，不支持递归CTE，需要创建自定义函数来处理：

第一步：创建替换函数 #

DELIMITER //

CREATE FUNCTION replace_variables(input_text TEXT) 
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE result TEXT DEFAULT input_text;
    DECLARE start_pos INT;
    DECLARE end_pos INT;
    DECLARE var_num INT DEFAULT 1;
    
    -- 循环查找所有带[]的变量，逐一替换
    WHILE LOCATE('[', result) > 0 DO
        SET start_pos = LOCATE('[', result);
        SET end_pos = LOCATE(']', result, start_pos);
        
        -- 确保找到闭合的]才替换，避免无效匹配
        IF end_pos > 0 THEN
            SET result = INSERT(result, start_pos, end_pos - start_pos + 1, 
                               CONCAT('<span class="variable-', var_num, '">', SUBSTRING(result, start_pos, end_pos - start_pos + 1), '</span>')
                              );
            SET var_num = var_num + 1;
        ELSE
            LEAVE; -- 没有闭合的]，退出循环
        END IF;
    END WHILE;
    
    RETURN result;
END //

DELIMITER ;

第二步：使用函数更新表 #

-- 先预览结果
SELECT id, replace_variables(query) AS final_query FROM your_table WHERE query LIKE '%[%]%';

-- 确认正确后更新表
UPDATE your_table 
SET query = replace_variables(query)
WHERE query LIKE '%[%]%';

注意事项 #

• 替换后字符串长度会增加，建议先把query列的类型从varchar(4096)改成TEXT，避免截断：ALTER TABLE your_table MODIFY query TEXT;
• 执行UPDATE前一定要先执行SELECT预览结果，确保符合预期再更新
• 自定义函数需要你有CREATE ROUTINE权限，如果没有需要联系管理员授权

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