编写MySQL查询识别temp1表中不符合规则的损坏标签数据

现有两张MySQL表：

表1：temp1 #

包含字段id（int类型）、tags（varchar类型），数据如下：

表2：temp2 #

包含字段id（int类型）、references（varchar类型），数据如下：

预期查询结果 #

需求 #

编写一条MySQL查询语句返回上述预期结果。说明：temp1的标签应来自temp2表references字段的valuesMeta列表，现存在标签不在该列表的损坏数据，需找出对应记录ID及损坏标签。

以下是满足需求的MySQL查询语句：

WITH valid_tags AS (
    SELECT JSON_TABLE(
        JSON_EXTRACT(t2.references, '$.valuesMeta'),
        '$[*]' COLUMNS(tag VARCHAR(255) PATH '$')
    ) AS tag
    FROM temp2 t2
),
split_temp1_tags AS (
    SELECT 
        t1.id,
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.tags, ',', n.n), ',', -1)) AS tag
    FROM temp1 t1
    JOIN (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    ) n 
        ON CHAR_LENGTH(t1.tags) - CHAR_LENGTH(REPLACE(t1.tags, ',', '')) >= n.n
    WHERE TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.tags, ',', n.n), ',', -1)) != ''
)
SELECT 
    st.id,
    GROUP_CONCAT(st.tag ORDER BY st.tag SEPARATOR ',') AS corruptedTags
FROM split_temp1_tags st
LEFT JOIN valid_tags vt ON st.tag = vt.tag
WHERE vt.tag IS NULL
GROUP BY st.id
ORDER BY st.id;

语句说明 #

1. valid_tags CTE：从temp2的references字段中提取valuesMeta数组内的所有有效标签，通过JSON_EXTRACT解析JSON结构，再用JSON_TABLE将数组转为行格式的标签数据。
2. split_temp1_tags CTE：把temp1中以逗号分隔的tags字符串拆分为单独的标签行。这里用了一个数字辅助表（n）来处理拆分，数字数量需覆盖temp1中最多的标签个数。
3. 最终查询：将拆分后的temp1标签与有效标签左连接，筛选出不在有效列表内的标签，再按id分组，用GROUP_CONCAT拼接损坏标签。

注意：若你的MySQL版本低于8.0，不支持CTE和JSON_TABLE，需用替代方案处理JSON解析与字符串拆分，比如自定义拆分函数、正则表达式结合变量的方式。

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