如何统计MySQL数据表中重复字符串的出现次数？

要实现统计数据表中不同记录里重复单词的需求，可以通过拆分字符串后分组统计的方式实现，以下分两种MySQL版本给出具体方案：

方案一：适用于MySQL 8.0及以上（支持递归CTE） #

利用递归公共表表达式（CTE）拆分每条记录中的单词，再统计每个单词的出现次数：

WITH RECURSIVE word_split AS (
    -- 初始步骤：提取每条记录的第一个单词，并保留剩余未拆分的内容
    SELECT
        id,
        SUBSTRING_INDEX(REGEXP_REPLACE(data, '\\s+', ' '), ' ', 1) AS word,
        SUBSTRING(REGEXP_REPLACE(data, '\\s+', ' '), LENGTH(SUBSTRING_INDEX(REGEXP_REPLACE(data, '\\s+', ' '), ' ', 1)) + 2) AS remaining_data
    FROM test_table
    WHERE TRIM(data) != ''
    UNION ALL
    -- 递归步骤：继续拆分剩余内容中的单词，直到剩余内容为空
    SELECT
        id,
        SUBSTRING_INDEX(remaining_data, ' ', 1) AS word,
        SUBSTRING(remaining_data, LENGTH(SUBSTRING_INDEX(remaining_data, ' ', 1)) + 2) AS remaining_data
    FROM word_split
    WHERE TRIM(remaining_data) != ''
)
-- 统计单词出现次数并拼接成目标格式
SELECT CONCAT('Count: ', GROUP_CONCAT(CONCAT(word, '(', COUNT(*), ')') ORDER BY COUNT(*) DESC SEPARATOR ', ')) AS result
FROM word_split
GROUP BY word
HAVING COUNT(*) > 1; -- 可选：仅保留出现多次的单词

说明 #

• REGEXP_REPLACE(data, '\\s+', ' ')用于将字段中多个连续空格替换为单个空格，避免拆分出空单词
• 递归CTE会逐行拆分每个data字段的所有单词，最终通过GROUP BY统计次数，GROUP_CONCAT拼接成你需要的输出格式

方案二：适用于MySQL 5.x版本（不支持CTE） #

需要先创建一个数字辅助表，用来辅助拆分字符串：

-- 1. 创建数字辅助表（数字数量需大于数据表中最长字符串的单词数）
CREATE TABLE IF NOT EXISTS numbers (n INT);
INSERT INTO numbers VALUES (1),(2),(3),(4),(5); -- 根据实际需求添加更多数字

-- 2. 拆分单词并统计
SELECT CONCAT('Count: ', GROUP_CONCAT(CONCAT(word, '(', COUNT(*), ')') ORDER BY COUNT(*) DESC SEPARATOR ', ')) AS result
FROM (
    SELECT
        SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_REPLACE(t.data, '\\s+', ' '), ' ', n.n), ' ', -1) AS word
    FROM test_table t
    JOIN numbers n ON n.n <= LENGTH(REGEXP_REPLACE(t.data, '\\s+', ' ')) - LENGTH(REPLACE(REGEXP_REPLACE(t.data, '\\s+', ' '), ' ', '')) + 1
) AS words
GROUP BY word
HAVING COUNT(*) > 1;

说明 #

• 数字表中的每个数字对应字符串中的第N个单词
• LENGTH(data) - LENGTH(REPLACE(data, ' ', '')) + 1用于计算每条记录的单词总数，确保只拆分出有效单词

针对你给出的测试数据，执行上述任一方案后，都会得到结果：

Count: this(4), here(2)

内容的提问来源于stack exchange，提问作者Oswaldo Goite