MySQL 5.7中如何统计JSON数组中指定元素的出现次数？

针对你在MySQL 5.7中统计指定ID集合在JSON字段tags中出现次数的需求，推荐以下两种更优的方案，比你当前用多个OR拼接JSON_CONTAINS的方式更灵活、更高效：

方案一：构造虚拟表关联统计（推荐） #

这种方法可以直接在SQL中完成每个ID的次数统计，无需在代码中二次处理，且适配任意数量的输入ID：

SELECT 
    target.id,
    COUNT(t.id) AS usage_count
FROM 
    (
        -- 这里根据输入的ID集合动态生成UNION ALL语句
        SELECT '3467562849402896' AS id UNION ALL
        SELECT '3467562861985809' AS id UNION ALL
        SELECT '3465044211793921' AS id
    ) AS target
LEFT JOIN 
    your_table t ON JSON_CONTAINS(t.tags, CONCAT('"', target.id, '"'), '$')
GROUP BY 
    target.id;

优势： #

• 无需手动编写大量OR条件，输入ID数量变化时，只需动态扩展UNION ALL的部分即可
• 直接返回每个ID的使用次数，减少代码层的统计逻辑
• 基于JSON_CONTAINS判断，匹配准确，不会出现误匹配

方案二：生成列+正则匹配（适合性能优化场景） #

如果你的查询频率很高，可以给JSON字段创建生成列并加索引，再用正则匹配来提升性能：

1. 先创建生成列和索引：

-- 生成JSON数组的文本形式虚拟列
ALTER TABLE your_table ADD COLUMN tags_text TEXT GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(tags, '$'))) VIRTUAL;
-- 给生成列加索引
CREATE INDEX idx_tags_text ON your_table(tags_text);

2. 然后用正则匹配统计：

SELECT 
    target.id,
    COUNT(t.id) AS usage_count
FROM 
    (
        SELECT '3467562849402896' AS id UNION ALL
        SELECT '3467562861985809' AS id UNION ALL
        SELECT '3465044211793921' AS id
    ) AS target
LEFT JOIN 
    your_table t ON t.tags_text REGEXP CONCAT('"', target.id, '"')
GROUP BY 
    target.id;

注意： #

• 正则匹配要带上ID前后的引号，避免出现子串匹配的误判（比如ID123匹配到1234）
• 生成列是虚拟列，不会占用额外存储，索引可以提升正则匹配的效率

和你原有方案的对比 #

你原来的方法只能筛选出包含任意目标ID的记录，还需要在代码中遍历这些记录统计每个ID的出现次数；而上面的方案直接在SQL层完成统计，逻辑更简洁，效率也更高，尤其是当目标ID数量较多时，优势更明显。

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