MySQL中IN子查询匹配逗号分隔字段失效问题及解决方案咨询

临时解决办法 #

修复FIND_IN_SET的失效问题 #

你用FIND_IN_SET没返回结果，大概率是CommaSeparated字段里藏了空格（比如"7, 8"而非"7,8"），FIND_IN_SET会严格匹配逗号分隔的每一段，带空格就匹配不上。可以先去除空格再查询：

SELECT SomeValue FROM Table02 
WHERE FIND_IN_SET(Id2, REPLACE((SELECT CommaSeparated FROM Table01 WHERE Id1=3), ' ', '')) > 0;

拆分逗号字符串后关联（适配不同MySQL版本） #

MySQL 8.0+（支持STRING_SPLIT）

直接拆分字符串后做关联查询：

SELECT t2.SomeValue 
FROM Table02 t2
JOIN (
    SELECT TRIM(value) AS id 
    FROM STRING_SPLIT((SELECT CommaSeparated FROM Table01 WHERE Id1=3), ',')
) t1_split ON t2.Id2 = t1_split.id;

MySQL 8.0之前版本（用递归CTE拆分）

如果是老版本，用递归CTE把逗号字符串拆成单独的ID：

WITH RECURSIVE split_ids AS (
    SELECT 
        SUBSTRING_INDEX(CommaSeparated, ',', 1) AS id,
        SUBSTRING(CommaSeparated, LOCATE(',', CommaSeparated) + 1) AS remaining
    FROM Table01 WHERE Id1=3
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(remaining, ',', 1) AS id,
        SUBSTRING(remaining, LOCATE(',', remaining) + 1) AS remaining
    FROM split_ids WHERE remaining != ''
)
SELECT t2.SomeValue FROM Table02 t2 JOIN split_ids s ON t2.Id2 = s.id;

长期最优方案：数据规范化 #

把逗号分隔的字段改成关联表才是根本解决办法，符合数据库设计规范，能避免字符串处理的性能问题，还能保证数据一致性：

1. 创建关联表：

CREATE TABLE Table01_Table02_Link (
    Id1 INT,
    Id2 INT,
    PRIMARY KEY(Id1, Id2),
    FOREIGN KEY(Id1) REFERENCES Table01(Id1),
    FOREIGN KEY(Id2) REFERENCES Table02(Id2)
);

2. 把原Table01里的逗号数据拆分插入到关联表：比如给Id1=3插入两条记录(3,7)和(3,8)
3. 之后查询直接用JOIN，简单高效：

SELECT t2.SomeValue 
FROM Table01 t1
JOIN Table01_Table02_Link link ON t1.Id1 = link.Id1
JOIN Table02 t2 ON link.Id2 = t2.Id2
WHERE t1.Id1=3;

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