MySQL中JSON数组字段多值匹配的WHERE条件编写问题

你的category是存储字符串类型分类ID的JSON数组字段，结构示例如下：

原查询语句无法返回预期结果，核心问题出在JSON函数用法错误和类型不匹配，以下是可直接落地的实现方案。

原语句错误点 #

• JSON_CONTAINS指定了路径参数$[0]，仅校验数组第一个元素，无法匹配数组其他位置的目标值
• 关联category表时JSON_SEARCH未做类型对齐，字段内存储的是字符串格式ID，直接传入数字类型的pc.id会触发隐式转换，导致匹配失效
• 未区分多值匹配的两种常见逻辑：同时包含所有目标值、包含任意一个目标值

分场景实现代码 #

场景1：要求记录同时包含所有目标分类（如必须同时匹配49、27两个分类） #

使用JSON_CONTAINS做全数组匹配，关联分类表时做类型对齐保证匹配准确性：

SELECT l.*, pc.name as cat_name,u.name as uname
FROM listing l
LEFT OUTER JOIN package_purchased_history ph 
  ON ph.user_id = l.user_id 
  AND ph.expired_date >= 1656095400 
  AND ph.purchase_date <= 1656095400
LEFT OUTER JOIN user u ON u.id = l.user_id
INNER JOIN category pc 
  -- 类型对齐后匹配，避免隐式转换导致的匹配失败
  ON JSON_CONTAINS(l.categories, CONCAT('"', pc.id, '"')) 
  AND pc.parent = 26
WHERE 
  -- 同时匹配"49"、"27"两个分类，无需指定数组路径
  JSON_CONTAINS(l.categories, '["49","27"]')
  AND l.status = 'active'
GROUP BY l.id

注：如果使用MySQL 8.0.17及以上版本，关联条件可以替换为JSON_CONTAINS(l.categories, CAST(pc.id AS JSON), '$')，写法更简洁。

场景2：要求记录包含任意一个目标分类（如匹配49或27任意一个即可） #

使用JSON_OVERLAPS函数判断两个数组是否有交集，性能比多个JSON_SEARCH条件拼接更高：

-- 将WHERE条件中的JSON_CONTAINS替换为以下逻辑即可
WHERE 
  JSON_OVERLAPS(l.categories, '["49","27"]')
  AND l.status = 'active'

性能优化建议 #

• 如果categories字段是高频查询条件，MySQL 8.0.17及以上版本支持给JSON数组添加多值索引，可大幅提升查询速度，建索引语句如下：

-- 数组内存储字符串类型数字用UNSIGNED转换，纯字符串值调整为CHAR类型即可
ALTER TABLE listing ADD INDEX idx_listing_categories( (CAST(categories AS UNSIGNED ARRAY)) );

添加索引后，上述JSON_CONTAINS、JSON_OVERLAPS查询可自动命中索引，避免全表扫描。

• 存储JSON数组内的ID时尽量保持类型统一，要么全存数字要么全存字符串，避免隐式类型转换导致的匹配失效问题。

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