MySQL 5.7中如何高效执行多值JSON_CONTAINS查询？

针对你当前用多个OR结合JSON_CONTAINS导致查询效率低下的问题，以下是几种更高效的实现方案：

方案一：使用JSON_TABLE展开数组后匹配（MySQL 8.0.4+支持） #

将JSON数组展开为关系型临时表，再通过IN条件匹配目标id，这种方式能减少JSON遍历次数，且MySQL对IN的优化更友好：

SELECT DISTINCT t.*
FROM sometable t
JOIN JSON_TABLE(
    t.json,
    '$[*]' COLUMNS (
        id VARCHAR(255) PATH '$.id'  -- 根据实际id类型调整字段类型
    )
) jt
WHERE jt.id IN ('1', '2', '3');

DISTINCT用于避免原表行因JSON数组中多个匹配id而重复返回。

方案二：创建生成列并添加索引（MySQL 5.7+支持，8.0.17+体验更佳） #

如果需要频繁按此类条件查询，建议创建存储型生成列存储所有id的数组，再通过索引加速查询：

1. 新增生成列并建立索引：

-- 提取JSON数组中所有id生成新的JSON数组列
ALTER TABLE sometable ADD COLUMN json_ids JSON GENERATED ALWAYS AS (JSON_EXTRACT(json, '$[*].id')) STORED;
-- 为生成列添加索引
CREATE INDEX idx_json_ids ON sometable(json_ids);

2. 使用JSON_OVERLAPS（MySQL 8.0.17+）查询数组交集：

SELECT *
FROM sometable
WHERE JSON_OVERLAPS(json_ids, JSON_ARRAY('1', '2', '3'));

若使用MySQL 5.7，可改用JSON_CONTAINS组合查询：

SELECT *
FROM sometable
WHERE JSON_CONTAINS(json_ids, JSON_ARRAY('1'))
   OR JSON_CONTAINS(json_ids, JSON_ARRAY('2'))
   OR JSON_CONTAINS(json_ids, JSON_ARRAY('3'));

生成列的索引能让MySQL直接通过索引过滤数据，无需逐行解析原JSON列，效率提升明显。

方案三：使用JSON_SEARCH简化匹配（MySQL 5.7+支持） #

通过JSON_SEARCH直接查找是否存在目标id，相比原JSON_CONTAINS，单次调用即可检查一个id的存在性：

SELECT *
FROM sometable
WHERE JSON_SEARCH(json, 'one', '1', NULL, '$[*].id') IS NOT NULL
   OR JSON_SEARCH(json, 'one', '2', NULL, '$[*].id') IS NOT NULL
   OR JSON_SEARCH(json, 'one', '3', NULL, '$[*].id') IS NOT NULL;

'one'参数表示找到第一个匹配即停止，减少不必要的遍历。

原查询效率低下的原因 #

多次OR连接的JSON_CONTAINS会让MySQL对每行JSON列重复执行多次完整遍历，且OR条件可能导致优化器无法有效利用潜在索引，最终导致总耗时远超三次单独查询的总和。

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