MySQL LEFT JOIN关联JSON字段查询用户所属组名求助

我来帮你搞定这个通过JSON字段关联查询用户所属组名称的问题！首先咱们先把缺失的groups表测试数据补上，这样能完整验证查询效果：

INSERT INTO `groups` (`id`, `name`) VALUES
('G-9001', 'Engineering Team'),
('G-9002', 'Marketing Team'),
('G-9003', 'Product Team');

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

MySQL 8.0及以上支持JSON_TABLE函数，能把JSON数组直接拆分成行数据，这是处理这类关联最优雅高效的方式。下面是完整的LEFT JOIN查询语句：

SELECT 
    u.id AS user_id,
    u.name AS user_name,
    g.name AS group_name
FROM 
    users u
LEFT JOIN 
    JSON_TABLE(
        u.groups,
        '$[*]' COLUMNS(group_id VARCHAR(255) PATH '$')
    ) jt ON TRUE
LEFT JOIN 
    groups g ON jt.group_id = g.id
ORDER BY 
    u.id, g.name;

语句解释： #

1. JSON_TABLE(u.groups, '$[*]' COLUMNS(group_id VARCHAR(255) PATH '$'))：把每个用户的groups JSON数组拆分成单独的行，每行对应一个组ID（别名jt作为中间表）。
2. 用LEFT JOIN关联中间表jt和groups表，通过组ID匹配，这样即使某个组ID在groups表中不存在（比如用户数组里有个不存在的G-9004），也会保留用户信息，组名称显示为NULL，符合LEFT JOIN的预期。
3. 最终结果会列出每个用户对应的所有组名称，一行一个关联关系。

兼容MySQL 5.7的替代方案 #

如果你的MySQL版本是5.7（不支持JSON_TABLE），可以用字符串转换+FIND_IN_SET的方式实现，但这种方法性能较差（无法利用索引），且对JSON格式的一致性要求较高：

SELECT 
    u.id AS user_id,
    u.name AS user_name,
    g.name AS group_name
FROM 
    users u
LEFT JOIN 
    groups g ON FIND_IN_SET(
        g.id, 
        REPLACE(REPLACE(REPLACE(u.groups, '[', ''), ']', ''), '"', '')
    ) > 0
ORDER BY 
    u.id, g.name;

语句解释： #

• REPLACE(REPLACE(REPLACE(u.groups, '[', ''), ']', ''), '"', '')：把JSON数组格式的字符串转换成逗号分隔的纯ID字符串（比如["G-9001", "G-9003"]变成G-9001,G-9003）。
• FIND_IN_SET(g.id, ...) > 0：判断组ID是否在转换后的字符串中，从而实现关联。

注意： #

这种方法只适合临时查询或数据量较小的场景，因为FIND_IN_SET无法使用groups.id上的索引，数据量大时查询会很慢。

查询结果示例 #

针对你提供的用户数据，执行推荐方案后会得到如下结果：

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