MySQL中WHERE IN子句中的子查询无法生效问题排查

现有两张表结构如下：

Jobcard表 #

User表 #

期望输出：

尝试的查询及问题 #

1. 第一种查询：

SELECT GROUP_CONCAT(U.name) name
FROM jobcards J
JOIN users U ON FIND_IN_SET(J.advisor_ids, U.id)
GROUP BY J.id;

该查询未得到正确结果，因为FIND_IN_SET参数顺序错误，且未处理advisorId中的方括号。

2. 第二种查询思路：先格式化advisorId使其符合WHERE IN格式，单独执行格式化查询：

SELECT replace(replace(replace(advisor_ids,'[',"'"),']',"'"),",","','") AS id from jobcards where id ='f82d6c76-b344-4f58-8fe9-a405c9b968d1'

得到结果：'5d414796-935d-414d-8f7a-952c7806d7e3','627433fe-b6ca-465e-be66-f53cbc3c6d86'
将此结果直接代入WHERE IN能得到正确结果：

SELECT * FROM users U WHERE id IN('5d414796-935d-414d-8f7a-952c7806d7e3','627433fe-b6ca-465e-be66-f53cbc3c6d86') 

但将格式化查询作为子查询放入WHERE IN时，无法得到结果：

SELECT * FROM users U WHERE id IN(SELECT replace(replace(replace(advisor_ids,'[',"'"),']',"'"),",","','") AS id from jobcards where id ='f82d6c76-b344-4f58-8fe9-a405c9b968d1') 

子查询返回的是单个完整字符串（比如'a','b'是一个整体），而IN子句需要的是多个独立取值。数据库会把这个字符串当作一个整体去和user.id匹配，显然没有用户ID等于这个长字符串，因此无法查询到结果。

方案1：修正FIND_IN_SET用法 #

先去除advisorId中的方括号，再调整FIND_IN_SET参数顺序（FIND_IN_SET(要查找的值, 逗号分隔的字符串列表)），结合GROUP_CONCAT得到期望结果：

SELECT J.jobcardId, GROUP_CONCAT(U.name) AS advisor_names
FROM jobcards J
JOIN users U ON FIND_IN_SET(U.id, REPLACE(REPLACE(J.advisorId, '[', ''), ']', ''))
WHERE J.jobcardId = 'f82d6c76-b344-4f58-8fe9-a405c9b968d1'
GROUP BY J.jobcardId;

方案2：利用JSON解析（MySQL 8.0+） #

由于advisorId格式符合JSON数组，可直接用JSON_TABLE将其拆分为多行ID，再关联User表：

SELECT J.jobcardId, GROUP_CONCAT(U.name) AS advisor_names
FROM jobcards J
JOIN JSON_TABLE(
    J.advisorId,
    '$[*]' COLUMNS(user_id CHAR(36) PATH '$')
) AS ids
JOIN users U ON U.id = ids.user_id
WHERE J.jobcardId = 'f82d6c76-b344-4f58-8fe9-a405c9b968d1'
GROUP BY J.jobcardId;

方案3：动态SQL（不推荐） #

如果必须用IN子句方式，可通过动态SQL拼接执行，但存在SQL注入风险，不建议使用：

SET @ids = (SELECT REPLACE(REPLACE(advisorId, '[', ''), ']', '') FROM jobcards WHERE jobcardId = 'f82d6c76-b344-4f58-8fe9-a405c9b968d1');
SET @sql = CONCAT('SELECT J.jobcardId, GROUP_CONCAT(U.name) AS advisor_names FROM jobcards J JOIN users U ON U.id IN(', @ids, ') WHERE J.jobcardId = ''f82d6c76-b344-4f58-8fe9-a405c9b968d1'' GROUP BY J.jobcardId');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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