MySQL 5.7.40：如何用WHERE IN匹配JSON字段sp_id数组的指定值

假设你的JSON字段名为json_data，存储格式如下：

{"sp_id": [1, 2, 113],"spd_id": [],"spclg_id": []}

以下是两种实现方式，对应你需要的类似PHP array_intersect 的交集验证逻辑：

1. 检查单个值是否存在于sp_id数组 #

使用JSON_CONTAINS函数直接验证单个值是否在数组中：

SELECT *
FROM your_table
WHERE JSON_CONTAINS(json_data->'$.sp_id', CAST(1 AS JSON));

• json_data->'$.sp_id'：提取JSON字段中的sp_id数组
• CAST(1 AS JSON)：将目标值转为JSON格式，确保与数组内的数据类型匹配（如果数组存的是数字，必须用JSON数字类型匹配，不能用字符串）

2. 检查数组与多个指定值是否存在交集（类似array_intersect有结果） #

使用JSON_TABLE将JSON数组拆分为行数据，再通过EXISTS子查询验证是否有重叠值：

SELECT t.*
FROM your_table t
WHERE EXISTS (
    SELECT 1
    FROM JSON_TABLE(
        t.json_data->'$.sp_id',
        '$[*]' COLUMNS(sp_id INT PATH '$')
    ) AS jt
    WHERE jt.sp_id IN (1, 113) -- 这里替换成你需要检查的目标值集合
);

• JSON_TABLE：把sp_id数组的每个元素拆成一行，生成临时表jt
• EXISTS：只要临时表中存在任意一个值在目标集合里，就返回原表的对应行

备选方案（值数量较少时） #

如果需要检查的目标值不多，也可以用多个JSON_CONTAINS通过OR连接：

SELECT *
FROM your_table
WHERE JSON_CONTAINS(json_data->'$.sp_id', CAST(1 AS JSON))
   OR JSON_CONTAINS(json_data->'$.sp_id', CAST(113 AS JSON));

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