插件窝 干货文章 如何统计 MySQL 5.7 中 JSON 数组特定元素出现的次数?

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

JSON your 方法 COUNT 238    来源:    2025-03-31

统计 MySQL 5.7 中 JSON 数组特定元素出现次数的方法

在 MySQL 5.7 中,你可以使用 JSON 函数来统计 JSON 数组中特定元素出现的次数。以下是几种有效的方法:

方法一:使用 JSON_TABLE 函数(MySQL 8.0+)

虽然你提到的是 MySQL 5.7,但请注意 MySQL 5.7 没有 JSON_TABLE 函数(这是 MySQL 8.0 引入的)。如果是 8.0+ 版本,可以使用:

SELECT COUNT(*) as count
FROM your_table,
JSON_TABLE(your_json_column, '$[*]' COLUMNS(value JSON PATH '$')) as jt
WHERE jt.value = 'your_value';

方法二:使用 JSON_LENGTH 和 JSON_REMOVE(MySQL 5.7)

对于 MySQL 5.7,可以结合使用 JSON_LENGTH 和 JSON_REMOVE 来计算:

SELECT 
  JSON_LENGTH(your_json_column) - JSON_LENGTH(JSON_REMOVE(your_json_column, REPLACE(JSON_SEARCH(your_json_column, 'all', 'your_value'), '"', ''))) as count
FROM your_table;

方法三:使用存储过程

对于更复杂的情况,可以创建一个存储过程:

DELIMITER //
CREATE FUNCTION count_json_array_element(arr JSON, val JSON) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE count INT DEFAULT 0;
    DECLARE len INT;

    SET len = JSON_LENGTH(arr);

    WHILE i < len DO
        IF JSON_EXTRACT(arr, CONCAT('$[', i, ']')) = val THEN
            SET count = count + 1;
        END IF;
        SET i = i + 1;
    END WHILE;

    RETURN count;
END //
DELIMITER ;

-- 使用示例
SELECT count_json_array_element(your_json_column, '"your_value"') as count
FROM your_table;

方法四:使用字符串函数(简单情况)

如果 JSON 数组结构简单,也可以使用字符串函数:

SELECT 
  (LENGTH(your_json_column) - LENGTH(REPLACE(your_json_column, '"your_value"', ''))) / LENGTH('"your_value"') as count
FROM your_table;

注意事项

  1. 这些方法在大型数据集上可能性能不佳
  2. 方法二和方法四在某些边缘情况下可能不准确
  3. 如果可能,考虑升级到 MySQL 8.0 以获得更好的 JSON 处理能力
  4. 对于生产环境,建议先在测试数据上验证方法的准确性

希望这些方法能帮助你统计 JSON 数组中特定元素出现的次数!