MySQL JSON_OVERLAPS 判断数组交集的查询方案

把标签、权限或适用范围存成 JSON 数组后，最常见的查询不是“一个数组是否完整包含另一个数组”，而是“两个数组有没有至少一个共同值”。这时用 JSON_OVERLAPS() 更贴合语义：数组有交集返回 1，没有交集返回 0。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/

先把“有交集”与“完全包含”分开

假设商品表的 tags 列保存 ["mysql", "json", "index"]，页面传入的筛选标签是 ["json", "redis"]。两边虽然不完全相同，但共同值是 json，所以这条商品应当命中。

JSON_OVERLAPS() 的判断重点是“至少一个共同元素”。如果业务要求查询数组里的每一项都在商品数组中，则应考虑 JSON_CONTAINS()；不要只把函数名换掉后期待结果一致。

用 JSON_OVERLAPS 写出数组交集查询

查询参数应保持 JSON 数组形状，SQL 中可以用 JSON_ARRAY() 构造一组标量值。下面的表结构和查询只演示语义，实际项目还要根据字段长度、数据量和写入频率决定是否把标签拆成关联表。

-- 建立保存商品标签的最小示例，tags 必须是合法 JSON
CREATE TABLE product_catalog (
    id BIGINT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    tags JSON NOT NULL
);

-- 任意命中 json 或 redis 的商品都会返回
SELECT id, name, tags
FROM product_catalog
WHERE JSON_OVERLAPS(tags, JSON_ARRAY('json', 'redis'));

-- 外部参数已经是 JSON 数组时，直接作为第二个文档传入
SELECT id, name
FROM product_catalog
WHERE JSON_OVERLAPS(tags, CAST('["json", "redis"]' AS JSON));

第一种写法适合固定数量的参数，第二种适合应用层已经完成 JSON 编码的筛选条件。生产代码中不要把未校验的字符串直接拼接进 SQL；参数绑定既能避免引号问题，也能减少把非法 JSON 带入查询的风险。

类型、对象和空值是最容易误判的地方

JSON 比较不会把数字和字符串随意当成同一个值。比如数组中是数字 6，查询参数写成字符串 "6"，两者不构成交集。对象比较则看共同的键值对；嵌套数组也按完整元素比较，外层数组中出现部分数字，不代表与另一个嵌套数组部分重叠。

另外，任一参数为 NULL 时结果也是 NULL，不是布尔值 0。空数组与任何数组都没有元素交集，通常得到 0。如果业务把 NULL 当成“没有标签”，可以显式归一化：

-- 用 COALESCE 把 NULL 统一成空数组，再进行交集判断
SELECT id,
       COALESCE(JSON_OVERLAPS(tags, CAST(? AS JSON)), 0) AS has_overlap
FROM product_catalog;

-- 这个例子提醒类型必须一致：数字 6 与字符串 "6" 不相等
SELECT JSON_OVERLAPS('[4, 5, 6]', '[6]') AS number_hit,
       JSON_OVERLAPS('[4, 5, 6]', '["6"]') AS string_miss;

在接口层最好先约定标签统一使用字符串，权限编号统一使用数字，并在入库时执行一次清洗。这样比在每条查询里尝试转换类型更容易维护。

多值索引只能解决性能，不能修正语义

当 JSON 数组存储在 InnoDB 表中、查询条件稳定且数据量足够大时，可以评估多值索引。它针对数组元素建立索引，不等于普通 JSON 列自动获得了一个万能索引；对象路径、复杂表达式和不匹配的写法仍可能无法利用索引。

-- 为 JSON 数组中的字符串元素建立多值索引
CREATE INDEX idx_product_tags
ON product_catalog ((CAST(tags->'$[*]' AS CHAR(32) ARRAY)));

-- 先观察优化器是否选择索引，再决定是否保留它
EXPLAIN
SELECT id
FROM product_catalog
WHERE JSON_OVERLAPS(tags, JSON_ARRAY('json', 'redis'));

检查执行计划时关注访问类型、候选索引和实际扫描规模。若数组很长、候选值很多，索引收益未必稳定；若标签本身是高频更新且查询模式多变，独立的商品标签关系表可能更直观。我的实践顺序是先用小数据验证 1/0/NULL 语义，再用真实分布评估计划，避免为了一个函数过早改表结构。

常见问题

JSON_OVERLAPS 能判断数组必须全部匹配吗？

不能。它只需要找到一个共同元素；要求查询数组全部被包含时，应改用 JSON_CONTAINS 或重新建模。

为什么数字 6 和字符串 "6" 没有命中？

JSON_OVERLAPS 按 JSON 类型比较，不会替你做字符串到数字的隐式转换。入库和查询参数要统一类型。

JSON_OVERLAPS 返回 NULL 应该怎么处理？

先判断业务是否把 NULL 解释为“没有标签”。若是，可以用 COALESCE 转成 0；若 NULL 表示数据缺失，则应保留并单独告警。