MySQL 5.7中如何通过带引号的JSON字段关联两张表查询

针对你提出的需求，这里提供两种单行SQL实现方案，适配MySQL 5.7版本的带引号JSON数组关联场景：

方案一：使用JSON_CONTAINS函数（推荐，适用于groups字段为JSON类型） #

如果Users表的groups字段是JSON类型，可以直接利用MySQL 5.7支持的JSON_CONTAINS函数，将Groups表的id转换为带引号的字符串进行匹配：

SELECT g.*
FROM Groups g
INNER JOIN Users u 
  ON JSON_CONTAINS(u.groups, CONCAT('"', g.id, '"'))
WHERE u.id = 1; -- 替换为你要查询的用户ID

原理说明 #

Users表的groups数组元素是带引号的字符串（如"1"），而Groups表的id是数值类型，通过CONCAT('"', g.id, '"')将数值id拼接为带引号的字符串，再用JSON_CONTAINS判断该字符串是否存在于用户的groups数组中。

方案二：字符串替换+FIND_IN_SET（适用于groups字段为普通字符串类型） #

如果Users表的groups字段是普通字符串类型（存储的是类似["1","2"]的文本），可以先去除数组的括号和引号，再用FIND_IN_SET匹配：

SELECT g.*
FROM Groups g
INNER JOIN Users u 
  ON FIND_IN_SET(g.id, REPLACE(REPLACE(u.groups, '"', ''), '[', ']'))
WHERE u.id = 1; -- 替换为你要查询的用户ID

原理说明 #

通过两次REPLACE操作：先去掉所有双引号，再把左括号替换为空、右括号替换为空，将["1","2"]转换为1,2，之后用FIND_IN_SET判断Groups的id是否在这个逗号分隔的字符串中。

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