MySQL如何在WHERE..IN子句中使用JSON_ARRAY？

嘿，这个问题我之前也踩过坑！直接像你写的那样SELECT * FROM state WHERE code IN (@codes)是行不通的，原因很简单：你定义的@codes是一个JSON类型的数组，而IN子句期待的是一组离散的、逗号分隔的值（比如IN ('CA','NY','TX')），MySQL会把整个JSON数组当成一个单一的字符串值去匹配，相当于找code等于["CA","NY","TX"]的记录，自然查不到结果。

下面根据不同的MySQL版本，给你几种靠谱的解决方案：

方案1：用JSON_TABLE（MySQL 8.0+推荐） #

MySQL 8.0及以上版本支持JSON_TABLE函数，可以把JSON数组转换成一张临时表，这样就能和原表关联或者用在IN子句里了。

示例代码：

-- 定义JSON数组变量
SET @codes := JSON_ARRAY('CA' , 'NY' , 'TX');

-- 方法A：JOIN临时表查询
SELECT s.*
FROM `state` s
JOIN JSON_TABLE(
  @codes,
  '$[*]' COLUMNS(code VARCHAR(2) PATH '$') -- 把JSON数组的每个元素映射成code列
) jt ON s.code = jt.code;

-- 方法B：IN子句结合子查询
SELECT *
FROM `state`
WHERE code IN (
  SELECT code
  FROM JSON_TABLE(
    @codes,
    '$[*]' COLUMNS(code VARCHAR(2) PATH '$')
  ) jt
);

这种方法最规范，也能处理复杂的JSON结构，推荐优先使用。

方案2：用JSON_CONTAINS（MySQL 5.7兼容） #

如果你的MySQL版本是5.7（不支持JSON_TABLE），可以用JSON_CONTAINS函数来判断字段值是否存在于JSON数组中：

SET @codes := JSON_ARRAY('CA' , 'NY' , 'TX');
SELECT * FROM `state` WHERE JSON_CONTAINS(@codes, JSON_QUOTE(code));

这里要注意用JSON_QUOTE(code)把code字段的值转换成JSON字符串格式，不然JSON_CONTAINS会因为类型不匹配返回false。

方案3：转成字符串用FIND_IN_SET（不推荐，仅作应急） #

还有一种取巧的方法：把JSON数组转成逗号分隔的字符串，再用FIND_IN_SET函数，但这种方法有局限性（比如code里包含逗号就会出错），只适合简单场景应急：

SET @codes := JSON_ARRAY('CA' , 'NY' , 'TX');
-- 把JSON数组转成逗号分隔的字符串（去掉[]和引号）
SET @codes_str := REPLACE(REPLACE(JSON_UNQUOTE(@codes), '[', ''), ']', '');
SELECT * FROM `state` WHERE FIND_IN_SET(code, @codes_str);

总结 #

• 直接在IN子句里用JSON_ARRAY变量不可行，因为类型不匹配
• MySQL 8.0+优先用JSON_TABLE，最规范可靠
• MySQL 5.7用JSON_CONTAINS兼容
• 尽量避免用字符串转换的方法，容易踩坑

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