MySQL中使用参考表批量替换文本列子字符串的实现方案咨询
实现方案
方案1:MySQL 8.0及以上版本(递归CTE实现,无需自定义函数)
利用递归CTE逐条应用映射表规则完成批量替换,支持动态适配映射表的规则增减,不需要修改查询语句结构。
WITH RECURSIVE mapping_ordered AS ( -- 给映射规则加排序序号,长字符串优先替换,避免短串先替换导致长串匹配失效 SELECT refColumn1, refColumn2, ROW_NUMBER() OVER (ORDER BY LENGTH(refColumn1) DESC) AS rn FROM referenceTable ), max_mapping AS ( SELECT MAX(rn) AS max_rn FROM mapping_ordered ), recursive_replace AS ( -- 初始层读取原始业务数据 SELECT t.Farms, t.Animals AS replaced_animals, 1 AS current_rn FROM table1 t UNION ALL -- 递归层逐行应用替换规则 SELECT r.Farms, REPLACE(r.replaced_animals, m.refColumn1, m.refColumn2) AS replaced_animals, r.current_rn + 1 AS current_rn FROM recursive_replace r JOIN mapping_ordered m ON r.current_rn = m.rn WHERE r.current_rn <= (SELECT max_rn FROM max_mapping) ) -- 取全部规则应用完成的最终结果 SELECT Farms, replaced_animals AS Animals FROM recursive_replace WHERE current_rn = (SELECT max_rn + 1 FROM max_mapping) ORDER BY Farms;
方案2:MySQL 5.x版本适配
方式A:自定义函数实现(适合频繁查询场景)
-- 第一步创建批量替换函数 DELIMITER // CREATE FUNCTION batch_replace(input_str VARCHAR(2048)) RETURNS VARCHAR(2048) DETERMINISTIC BEGIN DECLARE done INT DEFAULT 0; DECLARE old_str VARCHAR(255); DECLARE new_str VARCHAR(255); -- 游标遍历映射表 DECLARE cur CURSOR FOR SELECT refColumn1, refColumn2 FROM referenceTable ORDER BY LENGTH(refColumn1) DESC; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; read_loop: LOOP FETCH cur INTO old_str, new_str; IF done THEN LEAVE read_loop; END IF; SET input_str = REPLACE(input_str, old_str, new_str); END LOOP; CLOSE cur; RETURN input_str; END // DELIMITER ; -- 第二步调用函数查询 SELECT Farms, batch_replace(Animals) AS Animals FROM table1 ORDER BY Farms;
函数中的VARCHAR长度可以根据业务表Animals列的实际长度调整,避免数据截断。
方式B:动态生成SQL(无创建函数权限时使用,适合一次性查询场景)
-- 执行以下语句生成最终查询SQL SELECT CONCAT('SELECT Farms, ', GROUP_CONCAT('REPLACE(' SEPARATOR ''), 'Animals', GROUP_CONCAT(CONCAT(', ''', refColumn1, ''', ''', refColumn2, ''')') ORDER BY LENGTH(refColumn1) DESC SEPARATOR ''), ' AS Animals FROM table1 ORDER BY Farms;') AS exec_sql FROM referenceTable;
将上述语句输出的exec_sql结果复制后直接运行,即可得到预期输出。
内容的提问来源于stack exchange,提问作者Reeves Bell
相关产品推荐
相关产品推荐

