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

在 MySQL 中处理 JSON 数据时，统计 JSON 数组中特定元素的使用频率是一个常见需求。以下是几种实现方法：

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

SELECT 
    item,
    COUNT(*) AS frequency
FROM 
    your_table,
    JSON_TABLE(
        your_json_column,
        '$[*]' COLUMNS (
            item VARCHAR(255) PATH '$'
        )
    ) AS jt
GROUP BY 
    item
ORDER BY 
    frequency DESC;

方法二：使用 JSON_EXTRACT 和字符串函数（适用于简单数组）

SELECT 
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(
            REPLACE(REPLACE(your_json_column, '[', ''), ']', ''),
        ',', 
        numbers.n
    ), 
    ',', 
    -1
    ) AS item,
    COUNT(*) AS frequency
FROM 
    your_table
JOIN 
    (
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL 
        SELECT 3 UNION ALL SELECT 4 UNION ALL 
        SELECT 5 -- 根据数组最大可能长度扩展
    ) numbers
ON 
    numbers.n <= JSON_LENGTH(your_json_column)
GROUP BY 
    item
ORDER BY 
    frequency DESC;

方法三：统计特定元素的出现次数

如果只需要统计特定元素的出现次数：

SELECT 
    SUM(
        JSON_CONTAINS(your_json_column, JSON_QUOTE('specific_value'), '$')
    ) AS count_of_specific_value
FROM 
    your_table;

方法四：使用存储过程处理复杂情况

DELIMITER //
CREATE PROCEDURE count_json_array_elements()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE json_data JSON;
    DECLARE i INT;
    DECLARE max_len INT;
    DECLARE item_val VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT your_json_column FROM your_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 创建临时表存储结果
    DROP TEMPORARY TABLE IF EXISTS temp_results;
    CREATE TEMPORARY TABLE temp_results (
        item VARCHAR(255),
        frequency INT DEFAULT 0
    );

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO json_data;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET max_len = JSON_LENGTH(json_data);
        SET i = 0;

        WHILE i < max_len DO
            SET item_val = JSON_UNQUOTE(JSON_EXTRACT(json_data, CONCAT('$[', i, ']')));

            -- 插入或更新计数
            INSERT INTO temp_results (item, frequency) 
            VALUES (item_val, 1)
            ON DUPLICATE KEY UPDATE frequency = frequency + 1;

            SET i = i + 1;
        END WHILE;
    END LOOP;

    CLOSE cur;

    -- 返回结果
    SELECT * FROM temp_results ORDER BY frequency DESC;
END //
DELIMITER ;

-- 调用存储过程
CALL count_json_array_elements();

注意事项

1. MySQL 5.7+ 支持 JSON 数据类型，但 JSON_TABLE 函数需要 MySQL 8.0+
2. 对于大型 JSON 数组，方法二和方法三可能性能较差
3. 如果 JSON 数组包含复杂对象而非简单值，需要调整提取逻辑
4. 考虑在应用层处理复杂 JSON 统计可能更高效

选择哪种方法取决于您的 MySQL 版本、数据量大小和具体需求。