如何使用 MySQL 统计 JSON 数组中特定元素的使用频率？

在 MySQL 中统计 JSON 数组中特定元素的使用频率，可通过以下方法实现，重点结合 JSON_SEARCH、JSON_CONTAINS 或 JSON_EXTRACT 等函数，具体步骤如下：

核心方法：使用 JSON_SEARCH 结合 COUNT

若需统计 JSON 数组中特定值的出现次数，且已知该值的类型（如字符串、数字），可通过 JSON_SEARCH 查找匹配路径，再结合 COUNT 统计结果。但需注意：

JSON_SEARCH 适用场景：查找 JSON 文档中第一个匹配指定值的路径，返回路径字符串（如 "$[0]"）。若值不存在则返回 NULL。
局限性：直接统计频率需对每个值单独查询，且无法一次性统计多个值的频率（需多次查询或动态 SQL）。

示例查询（统计单个值的出现次数）：

SELECT COUNT(JSON_SEARCH(tags, 'one', '3467562849402896')) AS count_3467562849402896FROM your_table_name;

说明：'one' 是 JSON 数组的键名（若数组无键名，可省略或使用 '$' 表示根路径）。若值存在，JSON_SEARCH 返回路径，COUNT 统计非 NULL 结果；若值不存在，结果为 0。

优化方法：使用 JSON_CONTAINS 结合 SUM

若需更高效地统计频率（尤其对多个值），推荐使用 JSON_CONTAINS 判断值是否存在，再通过 SUM 累加。

示例查询（统计单个值的出现次数）：

SELECT SUM(JSON_CONTAINS(tags, '"3467562849402896"', '$')) AS count_3467562849402896FROM your_table_name;

说明：
JSON_CONTAINS(target, val[, path])：检查 target 中 path 路径下是否包含 val，返回 1（是）或 0（否）。
若 JSON 数组无键名，path 参数设为 '$' 表示根路径。
SUM 对所有行的结果求和，得到总出现次数。

统计多个值的频率（需多次查询或动态 SQL）：

-- 查询多个值的频率（需分别执行）SELECT '3467562849402896' AS value, SUM(JSON_CONTAINS(tags, '"3467562849402896"', '$')) AS frequencyFROM your_table_nameUNION ALLSELECT '3467562861985809' AS value, SUM(JSON_CONTAINS(tags, '"3467562861985809"', '$')) AS frequencyFROM your_table_name;进阶方法：MySQL 8.0+ 使用 JSON_TABLE（推荐）

若使用 MySQL 8.0 及以上版本，可通过 JSON_TABLE 将 JSON 数组展开为行，再按值分组统计频率，效率更高且支持动态值列表。

示例查询：

SELECT jt.element_value, COUNT(*) AS frequencyFROM your_table_name, JSON_TABLE( tags, '$[*]' COLUMNS ( element_value VARCHAR(255) PATH '$' ) ) AS jtWHERE jt.element_value IN ('3467562849402896', '3467562861985809')GROUP BY jt.element_value;

说明：
JSON_TABLE 将 JSON 数组（tags）展开为多行，每行对应一个数组元素。
COLUMNS 定义如何提取元素值（此处直接提取为 element_value）。
WHERE 过滤需统计的值，GROUP BY 按值分组，COUNT(*) 统计频率。

注意事项

数据类型匹配：JSON 中字符串值需用双引号包裹（如 '"3467562849402896"'），数字可直接写（如 3467562849402896）。
性能优化：对大表统计时，建议在存储 JSON 的列上创建函数索引（MySQL 8.0+ 支持）。
版本兼容性：
MySQL 5.7：仅支持 JSON_SEARCH 和 JSON_CONTAINS，需多次查询统计多个值。
MySQL 8.0+：优先使用 JSON_TABLE，功能更强大且高效。

总结

MySQL 5.7：使用 JSON_CONTAINS + SUM 统计单个值频率，多次查询或动态 SQL 统计多个值。
MySQL 8.0+：使用 JSON_TABLE 展开数组后分组统计，效率最佳。
通用建议：若需频繁统计 JSON 数组频率，考虑将数据冗余存储至关系型列（如新增频率统计表），避免实时解析 JSON 的性能开销。

您可能感兴趣问答

Collapsible

热门标签

热点问答