MySQL查询优化：找出sourceText与targetText存在数字差异的行

核心思路 #

由于MySQL不支持正则负向预查，我们先通过移除标签内容排除标签内的数字干扰，再提取并比较两边文本中的数字集合，覆盖以下场景：

• 一侧有数字，另一侧无数字
• 两侧都有数字，但数字内容/数量存在差异
  同时排除两侧均无数字的无效行（如SKILL示例）

优化后的查询语句（MySQL 8.0+） #

WITH cleaned_text AS (
    SELECT 
        sourceText,
        targetText,
        -- 移除<bpt>和<ept>标签，排除标签内的数字干扰
        REGEXP_REPLACE(sourceText, '<(bpt|ept)[^>]+/>', '') AS cleaned_source,
        REGEXP_REPLACE(targetText, '<(bpt|ept)[^>]+/>', '') AS cleaned_target
    FROM tmx_data
),
source_digits AS (
    SELECT 
        sourceText,
        targetText,
        -- 提取清理后文本中的所有有效数字（含小数），排序后拼接成字符串
        COALESCE(GROUP_CONCAT(num ORDER BY num), '') AS source_num_seq
    FROM cleaned_text
    JOIN JSON_TABLE(
        -- 将非数字/小数点字符替换为分隔符，转换为JSON数组便于提取
        CONCAT('["', REGEXP_REPLACE(cleaned_source, '[^0-9.]', '","'), '"]'),
        '$[*]' COLUMNS(num VARCHAR(255) PATH '$')
    ) AS digit_list
    WHERE num REGEXP '^[0-9]+(\\.[0-9]+)?$' -- 筛选有效数字格式
    GROUP BY sourceText, targetText
),
target_digits AS (
    SELECT 
        sourceText,
        targetText,
        COALESCE(GROUP_CONCAT(num ORDER BY num), '') AS target_num_seq
    FROM cleaned_text
    JOIN JSON_TABLE(
        CONCAT('["', REGEXP_REPLACE(cleaned_target, '[^0-9.]', '","'), '"]'),
        '$[*]' COLUMNS(num VARCHAR(255) PATH '$')
    ) AS digit_list
    WHERE num REGEXP '^[0-9]+(\\.[0-9]+)?$'
    GROUP BY sourceText, targetText
)
SELECT s.sourceText, s.targetText
FROM source_digits s
INNER JOIN target_digits t 
    ON s.sourceText = t.sourceText 
    AND s.targetText = t.targetText
WHERE 
    -- 数字序列不一致（含一侧为空、一侧非空的情况）
    s.source_num_seq <> t.target_num_seq
    -- 排除两侧均无数字的无效行
    AND NOT (s.source_num_seq = '' AND t.target_num_seq = '');

关键逻辑说明 #

1. 清理文本：用REGEXP_REPLACE移除<bpt>和<ept>标签，确保只处理标签外的数字
2. 提取数字：通过JSON_TABLE将清理后的文本转换为JSON数组，筛选出有效数字（支持整数和小数），再用GROUP_CONCAT排序拼接成数字序列字符串
3. 比较差异：对比两侧的数字序列字符串，不一致则判定为存在数字差异，同时排除两侧均无数字的行

兼容MySQL 5.x的替代方案 #

如果使用MySQL 5.x（无CTE和JSON_TABLE），可以自定义一个提取数字序列的函数，再进行比较：

DELIMITER //
CREATE FUNCTION extract_digits(str TEXT) RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE result TEXT DEFAULT '';
    DECLARE temp_str TEXT DEFAULT str;
    DECLARE digit_match VARCHAR(255);
    
    WHILE temp_str REGEXP '[0-9]+(\\.[0-9]+)?' DO
        SET digit_match = REGEXP_SUBSTR(temp_str, '[0-9]+(\\.[0-9]+)?');
        SET result = CONCAT(result, ',', digit_match);
        SET temp_str = REGEXP_REPLACE(temp_str, '[0-9]+(\\.[0-9]+)?', '', 1, 1);
    END WHILE;
    
    RETURN TRIM(BOTH ',' FROM result);
END //
DELIMITER ;

-- 使用函数查询
SELECT sourceText, targetText
FROM tmx_data
WHERE 
    extract_digits(REGEXP_REPLACE(sourceText, '<(bpt|ept)[^>]+/>', '')) 
    <> extract_digits(REGEXP_REPLACE(targetText, '<(bpt|ept)[^>]+/>', ''))
    AND NOT (
        extract_digits(REGEXP_REPLACE(sourceText, '<(bpt|ept)[^>]+/>', '')) = ''
        AND extract_digits(REGEXP_REPLACE(targetText, '<(bpt|ept)[^>]+/>', '')) = ''
    );

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