MySQL IN子查询匹配逗号分隔字段未返回正确结果如何解决

你当前查询失效的核心原因是：IN关键字后接的子查询返回的是单个字符串类型的值"1,2,3"，而非三个独立的数值1、2、3。
数据库执行该语句时，实际判断逻辑等价于where book_id = '1,2,3'，仅会匹配表2中book_id值恰好等于字符串1,2,3的记录，自然无法返回book_id为1、2、3的三条对应数据。
这个问题的根源是表结构设计违反了数据库第一范式，在单个字段中用逗号拼接存储了多个关联值，没有将多对多关联关系拆分为独立的关系表。

分为兼容现有表结构的临时方案，和修正表结构的长期最优方案两类：

临时兼容方案（不修改现有表结构） #

根据你使用的数据库类型，选择对应的查询写法即可：

• 若使用MySQL：直接用内置的FIND_IN_SET函数匹配逗号分隔的值，示例代码如下：

SELECT * 
FROM table2 t2
WHERE EXISTS (
    SELECT 1 
    FROM table1 t1 
    WHERE t1.id = 1 
    AND FIND_IN_SET(t2.book_id, t1.book_ids)
);

• 若使用PostgreSQL：用字符串拆分函数将拼接字符串拆分为独立数值行后再匹配，示例代码如下：

SELECT * 
FROM table2 t2
WHERE t2.book_id IN (
    SELECT unnest(string_to_array(book_ids, ','))::INT 
    FROM table1 
    WHERE id = 1
);

• 跨数据库通用写法：通过拼接前后逗号的方式做模糊匹配，避免出现book_id=1时误匹配11、21这类值的问题，示例代码如下：

SELECT * 
FROM table2 t2
WHERE EXISTS (
    SELECT 1 
    FROM table1 t1
    WHERE t1.id = 1 
    AND CONCAT(',', t1.book_ids, ',') LIKE CONCAT('%,', t2.book_id, ',%')
);

长期最优方案（修正表结构） #

从根源上避免这类问题的方式是遵循数据库设计规范，拆掉用逗号存多值的字段，新建独立的关联关系表存储表1和图书的关联关系：

1. 新建关联表table1_book_rel，包含两个字段：table1_id（关联表1的id）、book_id（关联表2的book_id）
2. 将原表1中存储的逗号拼接值拆分为独立行存入关联表，比如id=1对应book_id1、2、3，就往关联表插入三条记录：(1,1)、(1,2)、(1,3)
3. 后续查询直接关联或用IN匹配即可正常返回结果，示例代码如下：

SELECT * 
FROM table2
WHERE book_id IN (
    SELECT book_id 
    FROM table1_book_rel 
    WHERE table1_id = 1
);

这种方案可以给关联字段加索引，查询性能远高于字符串匹配的写法，也不会出现值匹配错误的问题，是关系型数据库的标准设计方式。

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