如何用字段内的逗号分隔列表作为IN条件实现group_concat查询？

这个需求完全可以实现，问题出在当前查询没有正确处理逗号分隔的ID列表——直接把lookupIDs作为IN条件时，数据库会把整个字符串当成单个匹配值，自然只能命中第一个ID对应的结果。下面是具体的实现方案：

表结构与测试数据 #

先明确你提供的表结构和测试数据：

创建表语句 #

-- 建立testMain表
CREATE TABLE testMain (
    id INT PRIMARY KEY,
    lookupIDs VARCHAR(255)
);

-- 建立testLookup表
CREATE TABLE testLookup (
    id INT PRIMARY KEY,
    ValueField VARCHAR(255)
);

插入测试数据 #

-- 给testMain插入数据
INSERT INTO testMain VALUES (1, '1,2,3'), (2, '2,4');

-- 给testLookup插入数据
INSERT INTO testLookup VALUES (1, 'Value1'), (2, 'Value2'), (3, 'Value3'), (4, 'Value4');

具体实现方案 #

核心思路是先把lookupIDs里的逗号分隔ID拆成单个值，再关联testLookup表，最后用GROUP_CONCAT合并结果。

方案1：MySQL 8.0+版本（推荐） #

利用MySQL 8.0新增的JSON_TABLE函数拆分字符串，逻辑清晰高效：

SELECT 
    tm.id,
    GROUP_CONCAT(tl.ValueField SEPARATOR ', ') AS merged_values
FROM 
    testMain tm
JOIN 
    JSON_TABLE(
        -- 把逗号分隔的字符串转成JSON数组格式
        CONCAT('["', REPLACE(tm.lookupIDs, ',', '","'), '"]'),
        '$[*]' COLUMNS (lookup_id INT PATH '$')
    ) AS split_ids
JOIN 
    testLookup tl ON split_ids.lookup_id = tl.id
GROUP BY 
    tm.id;

方案2：适配MySQL 5.x版本 #

如果是低版本MySQL，没有JSON_TABLE，可以借助数字辅助表实现拆分：

1. 先创建一个数字辅助表（按需调整数字范围，覆盖你可能的ID数量）：

CREATE TABLE numbers (n INT);
INSERT INTO numbers VALUES (1), (2), (3), (4), (5);

2. 执行查询：

SELECT 
    tm.id,
    GROUP_CONCAT(tl.ValueField SEPARATOR ', ') AS merged_values
FROM 
    testMain tm
JOIN 
    numbers n 
    -- 计算当前行的ID数量，确保辅助表数字不超过这个数量
    ON n.n <= LENGTH(tm.lookupIDs) - LENGTH(REPLACE(tm.lookupIDs, ',', '')) + 1
JOIN 
    testLookup tl 
    -- 逐位拆分出单个ID
    ON tl.id = SUBSTRING_INDEX(SUBSTRING_INDEX(tm.lookupIDs, ',', n.n), ',', -1)
GROUP BY 
    tm.id;

最终结果 #

执行上述任一查询后，会得到你期望的输出：

而非当前仅返回单个值的实际输出：

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