如何在 MySQL 5.7 中统计 JSON 数组中特定值的计数？

在 MySQL 5.7 中，由于不支持 JSON_TABLE 函数，可通过结合 JSON_SEARCH 和条件判断实现统计 JSON 数组中特定值的计数。以下是具体方法及优化建议：

1. 基础查询逻辑使用 JSON_SEARCH 定位特定值在 JSON 数组中的位置，结合 COUNT 统计出现次数。例如，统计表 your_table 中 tags 列包含值 "3467562849402896" 的记录数：

   SELECT COUNT(*) AS count_3467562849402896FROM your_tableWHERE JSON_SEARCH(tags, 'one', '3467562849402896') IS NOT NULL;

   参数说明：

   tags：JSON 类型列，存储数组数据（如 ["3467562849402896", "123"]）。

   'one'：表示仅返回第一个匹配项的路径（若需所有匹配项，需结合其他方法）。

   '3467562849402896'：目标值。

   结果：返回包含该值的记录总数。

2. 多值统计优化若需同时统计多个值，可通过 UNION ALL 合并查询，避免重复扫描表：

   SELECT '3467562849402896' AS target_value, COUNT(*) AS countFROM your_tableWHERE JSON_SEARCH(tags, 'one', '3467562849402896') IS NOT NULLUNION ALLSELECT '3467562861985809' AS target_value, COUNT(*) AS countFROM your_tableWHERE JSON_SEARCH(tags, 'one', '3467562861985809') IS NOT NULLUNION ALLSELECT '3465044211793921' AS target_value, COUNT(*) AS countFROM your_tableWHERE JSON_SEARCH(tags, 'one', '3465044211793921') IS NOT NULL;

   优势：清晰展示每个值的统计结果，便于扩展。

3. 精确计数（值出现次数而非记录数）若需统计每个值在所有记录的 JSON 数组中出现的总次数（如某值在 10 条记录的数组中出现 15 次），需使用存储过程或应用层处理。MySQL 5.7 无法直接实现，但可通过以下步骤模拟：

   步骤 1：提取 JSON 数组为字符串，使用 SUBSTRING_INDEX 和 REPLACE 拆分（复杂且低效）。

   步骤 2：在应用层（如 Python、PHP）解析 JSON 并统计。

   推荐方案：升级到 MySQL 8.0+ 使用 JSON_TABLE，或修改数据结构为关系型表。

• 性能限制：JSON_SEARCH 对每条记录的 JSON 数据进行全文扫描，大数据量时性能较差。
• 索引无效：MySQL 5.7 的 JSON 列无法直接为数组内元素创建索引，需考虑全文索引或升级版本。
• 数据格式要求：JSON 数组中的值必须为字符串或数字，且格式严格（如引号包裹字符串）。

1. 升级到 MySQL 8.0+使用 JSON_TABLE 将 JSON 数组展开为行，直接通过 GROUP BY 统计：

   SELECT j.value, COUNT(*) AS countFROM your_table, JSON_TABLE(tags, '$[*]' COLUMNS (value VARCHAR(100) PATH '$')) AS jWHERE j.value = '3467562849402896'GROUP BY j.value;

2. 修改数据结构将 JSON 数组拆分为关联表（如 tags_relations），包含 record_id 和 tag_value 列，通过 SQL 直接统计：

   SELECT tag_value, COUNT(*) AS countFROM tags_relationsWHERE tag_value IN ('3467562849402896', '3467562861985809')GROUP BY tag_value;

• MySQL 5.7 限制下：使用 JSON_SEARCH + COUNT 统计包含特定值的记录数，或通过 UNION ALL 合并多值查询。
• 精确计数需求：需依赖应用层处理或升级数据库版本。
• 长期优化：建议重构数据结构为关系型表，以提升查询效率。