在 MySQL 5.7 中统计 JSON 数组中特定元素的使用次数

MySQL 5.7 引入了 JSON 数据类型支持，但要统计 JSON 数组中特定元素出现的次数需要一些技巧。以下是几种可行的方法：

方法一：使用 JSON_TABLE 函数（MySQL 8.0+）

虽然您提到的是 MySQL 5.7，但值得注意的是 MySQL 8.0+ 提供了更简单的解决方案：

SELECT COUNT(*) 
FROM JSON_TABLE(
    '[1, 2, 3, 2, 2, 4]',
    '$[*]' COLUMNS(value INT PATH '$')
) AS jt
WHERE value = 2;

方法二：MySQL 5.7 中的解决方案

对于 MySQL 5.7，您可以使用以下方法：

1. 使用 JSON_LENGTH 和 JSON_SEARCH

-- 假设有一个表包含JSON列
CREATE TABLE test_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    json_array JSON
);

-- 插入示例数据
INSERT INTO test_data (json_array) VALUES 
('[1, 2, 3, 2, 2, 4]'),
('[2, 2, 5, 6]'),
('[1, 3, 5, 7]');

-- 统计特定值(如2)在所有数组中的出现次数
SELECT 
    SUM(
        (
            SELECT COUNT(*) 
            FROM (
                SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 
                UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
                UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
            ) AS numbers
            WHERE i < JSON_LENGTH(json_array)
            AND JSON_EXTRACT(json_array, CONCAT('$[', i, ']')) = 2
        )
    ) AS total_count_of_2
FROM test_data;

2. 使用存储过程

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

    SET array_length = JSON_LENGTH(arr);

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

    RETURN count;
END //
DELIMITER ;

-- 使用示例
SELECT count_json_array_element('[1, 2, 3, 2, 2, 4]', 2) AS count_of_2;

3. 使用临时数字表和 JSON_EXTRACT

-- 创建临时数字表
WITH RECURSIVE numbers AS (
    SELECT 0 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 9
)
SELECT 
    SUM(
        CASE 
            WHEN JSON_EXTRACT(json_array, CONCAT('$[', n, ']')) = 2 THEN 1 
            ELSE 0 
        END
    ) AS total_count_of_2
FROM test_data
JOIN numbers ON n < JSON_LENGTH(json_array);

注意事项

1. 这些方法在大型数据集上可能性能不佳，因为需要处理每个数组元素
2. 对于嵌套JSON结构，需要调整路径表达式
3. 考虑升级到MySQL 8.0+以获得更好的JSON支持
4. 如果频繁需要这种操作，考虑将JSON数据规范化到关系表中

希望这些方法能帮助您在MySQL 5.7中实现JSON数组元素的计数需求。