MySQL 统计字符串出现次数的几种实用方法

做数据统计或者写报表的时候，我经常碰到一类需求：在一个 MySQL 的字段里，统计某个子串到底出现了几次。比如统计用户评论里“差评”这个词出现多少回，检查一篇文章里“MySQL”这个关键词密度够不够，或者从一串标签里数一数某个标签被引用了几次。这类需求听起来简单，真上手的时候会发现有很多细节容易踩坑，尤其是遇到中文、大小写、重叠字符的时候。这篇博文就把我平时用到的几种方法都整理一遍，从最简单的一行 SQL 到稍微复杂的存储过程、递归 CTE，最后再聊聊实战里的性能问题，希望对你有用。

我会尽量把每条语句的来龙去脉说清楚，不只是贴代码。你能知道它是怎么算的，遇到边界情况为什么出问题，以及怎么改才稳妥。无论你是面试准备、临时跑数，还是要把逻辑固化到正式报表里，都能从这里找到可用的方案。

1. 为什么需要统计字符串出现次数：从需求到方案选型

1.1 典型业务场景

字符串出现次数这个操作，远不是课堂练习题那么简单，实际业务里到处都是。举几个我真实遇到过的例子。

第一类是文本质量分析。内容平台上经常要检查一篇文章的关键词密度，也就是某个词在正文中出现的次数除以总字数。我们需要拿着标题里的词去正文里数次数，这个次数直接影响搜索排名和内容质量评估。如果用 MySQL 直接统计，就比把内容导出到程序里算要快得多，尤其是在数据库已经存储了正文的情况下。

第二类是用户评论和反馈分析。电商后台的客服团队想快速找出“物流太慢”这个短语在近一周评价里被提到了多少次。注意，这里不是统计有多少条评论包含它，而是所有评论里这个短语累计出现的总次数。一条评论可能重复抱怨三次，统计方式完全不同。

第三类是日志和错误码分析。有些系统把错误码拼接在一个字段里，比如1001,2001,1001,3001，要统计某个错误码出现的次数，用来判断故障的发生频率。这里和普通的文本关键词统计逻辑一致，但往往会碰到值为 NULL 的异常记录，需要格外小心。

第四类则是标签或名单解析。某个业务表里用逗号分隔存了一串用户标签，比如"老客,高价值,高价值,高价值"，产品同学想看看高价值标签到底出现了几次。这时候如果只数分隔符数量，会得到错误结果，必须精确匹配子串。

这些场景的共同点是：输入的是“一个字段值 + 一个目标子串”，输出的是“出现次数”。有的要求不重叠计数，有的可能想统计重叠出现的位置数量。不同的统计口径，会直接影响用哪种 SQL 写法。

1.2 方案选型对比

我早期的第一反应是先把数据拉到程序里用正则循环去数。后来发现，如果数据量不大，直接在 SQL 里用字符串函数就能解决，省掉一段 Java 或 Python 代码，维护起来也简单。方案大概可以分成这几类。

最简单的是LENGTH + REPLACE的算术方法。它的思路是：把目标子串全部删掉，用被删掉的总长度除以子串长度，得到的就是次数。这种方式没有任何循环，一条 SELECT 就能出结果，适合大多数“不重叠计数”的场景。

然后是自定义函数或存储过程。当统计规则变得复杂，比如要区分大小写、要按重叠位置计数、要跳过标签符号，一条 SQL 表达式就会写得非常拗口。这时候可以封装一个函数CountStr()，以后每次调用SELECT CountStr(content, 'MySQL')就完事，维护起来更清爽。缺点是很多开发库不允许后台账号创建函数，得看权限。

第三种是 MySQL 8.0 的递归 CTE。它能枚举出字符串的每个位置，然后逐个判断从该位置开始的子串是否等于目标子串。如果要求统计“重叠出现”的次数，比如aaaa里aa出现了几次，用 REPLACE 方法只能得到 2，因为它是按从头到尾不重叠替换的；但如果我们想数出位置 1-2、2-3、3-4 三个重叠片段，就必须用这种逐位扫描的方法。

最后一种是借助数字辅助表，通过SUBSTRING截取和JOIN关联，在一条 SQL 里实现逐位扫描，不需要递归 CTE，适合比较老的版本。

这几种方案并不冲突，可以按实际场景灵活选。下面我会把每一条都讲透。

2. 基础解法：用 LENGTH、REPLACE 一行 SQL 搞定

2.1 核心公式与原理

先上最常见的写法：

SELECT (LENGTH('abcabcabc') - LENGTH(REPLACE('abcabcabc', 'abc', ''))) / LENGTH('abc') AS cnt;

这条 SQL 的结果是 3。原理不复杂：原始字符串abcabcabc的长度是 9；把里面所有的abc替换成空字符串后，剩下空串，长度是 0；长度差是 9；再除以目标子串abc的长度 3，得到 3。

换成更通用的公式就是：

( LENGTH(原始字符串) - LENGTH(REPLACE(原始字符串, 目标子串, '')) ) / LENGTH(目标子串)

用文字说就是：看看把目标子串全部移除后，整个字符串缩短了多少个字符，这个缩短量除以子串的长度，自然就是子串的数量。

这个公式有两个大前提。第一，REPLACE是同时替换所有匹配项的，所以算出来的是“不重叠”次数；第二，目标子串不能为空字符串，否则第二步会出问题，后面我会单独说。

如果统计的对象是字段，而不是直接写死的字符串，写法也是一样。假设有一张文章表article，我们要统计content字段里MySQL出现的次数：

SELECT id, (LENGTH(content) - LENGTH(REPLACE(content, 'MySQL', ''))) / LENGTH('MySQL') AS cnt FROM article;

这样每条记录都会返回一个cnt，表示该文章里MySQL出现了几次。整个 SQL 不需要额外存储过程，也不需要在应用层循环，看完就能用。

2.2 中文等多字节字符的坑

很多初学者在使用这个方法时，会在中文字符串上栽跟头。比如：

SELECT LENGTH('中国中国') AS len_total, LENGTH(REPLACE('中国中国', '中国', '')) AS len_left;

在 UTF-8 编码下，中国中国的LENGTH结果是 12，因为每个汉字在 UTF-8 中占 3 个字节，两个“中国”共 4 个汉字，就是 12 字节。REPLACE掉所有“中国”后，剩下的字符串长度是 0。于是分子是 12，分母LENGTH('中国')是 6，结果 12 / 6 = 2。单看结果，居然也是对的。

但这里只是个巧合，遇到混合字符就不太直观了。更重要的问题是可读性，读 SQL 的人会很难理解为什么一个汉字数是 3 上下浮动。因此我推荐统一使用CHAR_LENGTH，它的语义是“字符数”，不是“字节数”：

SELECT id, (CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, '中国', ''))) / CHAR_LENGTH('中国') AS cnt FROM article;

在 UTF-8 下，CHAR_LENGTH('中国中国')返回 4，CHAR_LENGTH('中国')返回 2，一眼就能看懂：4 个字符减去 0 个字符，再除以 2 个字符，得到 2。这和编码无关，无论数据库用 utf8 还是 utf8mb4，结果都是一致的。

另外，如果目标字符串里真的有 emoji 这类四字节字符，LENGTH会出现更大的偏差，用CHAR_LENGTH可以规避掉这类问题。所以我个人建议，只要没有特殊原因，统计字符串出现次数一律用CHAR_LENGTH，不要用LENGTH。即使结果可能一样，从可维护性角度考虑也更友好。

2.3 边界情况：子串为空、目标为空、大小写敏感性

这个一行 SQL 看着简单，边界情况却非常多，我至少见过三次线上问题由这些边角引发。

第一个坑是目标子串为空字符串。如果写：

SELECT (CHAR_LENGTH('abc') - CHAR_LENGTH(REPLACE('abc', '', ''))) / CHAR_LENGTH('');

分母直接变成 0，MySQL 会报DIVISION BY 0错误，或者返回 NULL。更麻烦的是，即使分母不为零，REPLACE在子串为空时的行为也比较特殊，会向原字符串每个字符之间插入内容，导致长度变化没有意义。所以在实际查询中，必须在外面包一层判断，比如：

SELECT IF( CHAR_LENGTH('目标子串') = 0, 0, (CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, '目标子串', ''))) / CHAR_LENGTH('目标子串') ) AS cnt;

第二个坑是字段值为 NULL。MySQL 中任何值和 NULL 做运算，结果都是 NULL。如果某一行content是 NULL，这条记录计算出来的cnt就是 NULL，而不是 0。这在聚合统计时特别容易造成“总数失踪”。处理方式是用COALESCE或IFNULL先兜底：

SELECT COALESCE( (CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, '目标子串', ''))) / CHAR_LENGTH('目标子串'), 0 ) AS cnt FROM article;

第三个坑是大小写敏感问题。MySQL 的默认排序规则，比如utf8mb4_general_ci或utf8mb4_unicode_ci，在字符串比较时是不区分大小写的。REPLACE函数同样受到排序规则的影响。举个例子：

SELECT (CHAR_LENGTH('MySQL MySQL') - CHAR_LENGTH(REPLACE('MySQL MySQL', 'mysql', ''))) / CHAR_LENGTH('mysql') AS cnt;

内心的期望可能是统计小写mysql出现次数，结果是 0，因为两个MySQL的首字母是大写。但在默认排序规则下，REPLACE会认为它们是同一个字符串，把两个都替换掉，最终结果是 2。

如果业务上确实要区分大小写，可以用BINARY关键字强制按二进制比较：

SELECT (CHAR_LENGTH('MySQL MySQL') - CHAR_LENGTH(REPLACE(BINARY 'MySQL MySQL', BINARY 'mysql', ''))) / CHAR_LENGTH('mysql') AS cnt;

这次结果就是 0。所以写统计 SQL 之前，先想清楚产品要求的是大小写敏感还是不敏感，然后把对应的写法固化进代码，不要等结果对不上再排查。

3. 进阶解法：处理重叠计数和复杂逻辑

3.1 用存储过程实现逐个定位

上面的REPLACE方法无法处理重叠计数。比如字符串aaaa，目标子串aa。如果按不重叠方式数，从头开始找到一次 1-2，剩下aa还可以找到一次 3-4，所以是 2。但如果我们想统计的是所有“连续片段”的出现次数，那么位置 1-2、2-3、3-4 应该算三次，这时候REPLACE方法就无能为力了。

这种情况我一般会写一个存储过程，逐个用LOCATE查找子串的位置。LOCATE的语法是：

LOCATE(substr, target) -- 返回第一次出现的位置，找不到返回 0 LOCATE(substr, target, pos) -- 从 pos 位置开始查找

循环思路很简单：从位置 1 开始找，找到之后计数加 1。如果希望不重叠，就把下一次查找起点移动到当前找到的位置 + 子串长度；如果希望重叠，就移动到当前找到的位置 + 1。

下面是一个完整的自定义函数，函数名就叫CountStr，输入目标字符串和子串，还有一个overlap参数：

DELIMITER // CREATE FUNCTION CountStr( target VARCHAR(1000), substr VARCHAR(255), overlap TINYINT ) RETURNS INT DETERMINISTIC READS SQL DATA BEGIN DECLARE cnt INT DEFAULT 0; DECLARE pos INT DEFAULT 1; DECLARE sub_len INT DEFAULT CHAR_LENGTH(substr); IF substr IS NULL OR sub_len = 0 THEN RETURN 0; END IF; SET pos = LOCATE(substr, target); WHILE pos > 0 DO SET cnt = cnt + 1; IF overlap = 1 THEN -- 重叠计数：只把起点往后挪 1 个字符 SET pos = LOCATE(substr, target, pos + 1); ELSE -- 不重叠计数：跳过整个子串长度 SET pos = LOCATE(substr, target, pos + sub_len); END IF; END WHILE; RETURN cnt; END // DELIMITER ;

创建好函数后，使用方式和普通函数完全一样：

SELECT CountStr('aaaa', 'aa', 0) AS not_overlap_cnt, -- 返回 2 CountStr('aaaa', 'aa', 1) AS overlap_cnt; -- 返回 3

我在实际项目里会把overlap参数默认成 0，避免业务上误启用重叠计数。同时要注意函数参数和变量名不能和保留字冲突，比如target在不同的 MySQL 版本里可能有问题，我会起target_str更稳妥。

这种方案的优点是逻辑透明，所有规则都写得很清楚，后面人接手也能看懂。缺点是需要创建函数权限，而且对超长字符串的性能会比较差，因为每个命中位置都要执行一次LOCATE查询。

3.2 用 MySQL 8.0 的递归 CTE 实现重叠计数

如果你不想创建函数，或者数据库版本恰好是 MySQL 8.0，还可以用递归 CTE 枚举字符串的每个位置，然后直接比较子串。这种方法尤其适合“临时跑一次”的重叠计数需求。

思路是这样的：生成一个数字序列，从 1 一直到目标字符串的字符数。然后逐个用SUBSTRING(target, pos, sub_len)取出以 pos 开头的子串，看看是否等于目标子串。等式成立则计数。

WITH RECURSIVE seq(pos) AS ( SELECT 1 UNION ALL SELECT pos + 1 FROM seq WHERE pos < CHAR_LENGTH('aaaa') ) SELECT COUNT(*) AS overlap_cnt FROM seq WHERE SUBSTRING('aaaa', pos, CHAR_LENGTH('aa')) = 'aa';

这里seq会产出 1, 2, 3, 4 四个位置。SUBSTRING('aaaa', 1, 2)取到aa，等于目标，计数 1；SUBSTRING('aaaa', 2, 2)取到aa，计数 2；SUBSTRING('aaaa', 3, 2)取到aa，计数 3；第四个位置从 4 开始，只能截取出a，长度不足，不等于aa，不计数。所以结果就是 3，实现了重叠计数。

如果实际查询的字段来自表，可以这样写：

WITH RECURSIVE seq(pos) AS ( SELECT 1 UNION ALL SELECT pos + 1 FROM seq -- 这里要取一个最大的长度作为终止条件，避免递归过早结束 WHERE pos < (SELECT MAX(CHAR_LENGTH(comment)) FROM review) ) SELECT id, COUNT(*) AS cnt FROM review JOIN seq ON seq.pos <= CHAR_LENGTH(comment) WHERE SUBSTRING(comment, seq.pos, CHAR_LENGTH('不错')) = '不错' GROUP BY id;

这种方案有一个明显的性能问题：它会为每条记录生成大量临时行，如果字段特别长，递归层数会非常多，线上轻易不要用，建议在临时分析库或数据量小的场景用。

3.3 封装成自定义函数方便复用

如果你所在的公司有规范要求，不希望在 SQL 里写复杂的递归，另一个思路是把常见场景封装成自定义函数，然后在业务 SQL 里直接调用。这样不仅代码干净，还能把边界处理统一收口。

封装函数时除了处理重叠计数，还可以把大小写敏感、空字符串、NULL 等问题一并处理掉。比如我们可以在函数内部先判断目标子串是否为空，再决定直接返回 0。这样的函数在报表和统计任务中可以长期复用，避免每个人各写一套判断逻辑。

举一个我在内容系统里实际用过的函数，它统计content中某个词不区分大小写出现的次数：

DELIMITER // CREATE FUNCTION CountKeyword( target_str TEXT, keyword VARCHAR(255) ) RETURNS INT DETERMINISTIC NO SQL BEGIN DECLARE result INT DEFAULT 0; SET result = ( (CHAR_LENGTH(target_str) - CHAR_LENGTH(REPLACE(LOWER(target_str), LOWER(keyword), ''))) / CHAR_LENGTH(keyword) ); RETURN COALESCE(result, 0); END // DELIMITER ;

注意这里用了LOWER先把两个参数统一转成小写，然后执行REPLACE。这样做的好处是不会受默认排序规则对大小写的不同处理影响，逻辑直观。缺点是多了一次字符串转换的开销，但对多数场景可以接受。

如果觉得函数不够灵活，还可以用“数字辅助表”的方法来实现重叠计数，不需要递归。提前准备一张nums表，里面存从 1 到 N 的整数，然后 JOIN 到业务表。这个方法在 MySQL 5.7 也能用，不依赖 CTE。示例如下：

SELECT r.id, COUNT(*) AS cnt FROM review r JOIN nums n ON n.pos <= CHAR_LENGTH(r.content) WHERE SUBSTRING(r.content, n.pos, CHAR_LENGTH('不错')) = '不错' GROUP BY r.id;

数字辅助表的方式本质上和递归 CTE 一样，都是枚举位置。好处是执行计划更可控，坏处是需要额外准备一张数字表。如果项目里已经有维表，这种方法非常稳。

4. 实战踩坑记录与性能优化建议

4.1 我踩过的几个坑

先说一个让我印象特别深刻的线上问题。当时我需要统计一批用户标签里“VIP”出现的次数，直接用了一段类似LENGTH的 SQL，结果某些行的返回值明显比预期大许多倍。查了半天才发现，目标字符串里存了VIP会员这种带中文的词，而VIP是英文字符。虽然最终“次数”可能仍然正确，但中间过程的字节长度差非常大，一旦有人把它当作字符数去展示，就会产生幻觉般的数字。后来我全面改掉了LENGTH，统一用CHAR_LENGTH，这类问题才彻底消失。

第二个坑是 NULL 导致的聚合结果丢失。有一次我跑日报任务，统计评论表中“差评”出现的总次数，结果发现数据经常少统计。排查到最后，问题出在一条评论的content字段是 NULL，表达式返回 NULL，最终SUM函数会把这一行当 NULL 处理，直接跳过。解决方案就是我之前提到的COALESCE，把每行的计数结果先转成 0。

第三个坑就是大小写。默认排序规则不区分大小写，导致统计“mysql”时把“MySQL”也算进去了。产品当时想统计的是用户是否用全小写的方式来写“mysql”，结果数据翻了好几倍。从那次以后，我在所有字符串统计需求里，都会先跟业务方确认大小写敏感要求，然后在 SQL 中用BINARY或LOWER把口径写死。

4.2 大批量数据下的性能优化思路

如果在几十万行的表上，每条记录都跑一次CHAR_LENGTH + REPLACE，性能可能不会太差，但也不会很好。MySQL 需要对每一行执行字符串替换和长度计算，这在 CPU 密集型的分析任务里特别明显。更麻烦的是，这类表达式没法直接用普通索引。

针对这种场景，我有几条优化建议。

第一是先用条件粗筛。因为我们需要统计出现次数，所以可以先通过LIKE把完全没有包含目标子串的记录过滤掉，只对包含它的记录做精确计数。这样虽然LIKE本身也需要扫描，但至少减少了后面表达式计算的次数。对于没有索引的大表，效果仍然有限，但能明显降低无效计算。

SELECT id, (CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, 'MySQL', ''))) / CHAR_LENGTH('MySQL') AS cnt FROM article WHERE content LIKE '%MySQL%';

第二是使用生成列。如果 MySQL 版本支持生成列，可以把“目标词出现次数”的结果提前计算并存储到一列中，然后对这一列建索引。注意，生成列里不能直接用自定义函数，但可以直接用内置函数表达式。比如：

ALTER TABLE article ADD COLUMN mysql_cnt INT GENERATED ALWAYS AS ( (CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, 'MySQL', ''))) / CHAR_LENGTH('MySQL') ) STORED; CREATE INDEX idx_mysql_cnt ON article(mysql_cnt);

这样当查询条件是mysql_cnt > 3时，MySQL 可以直接走索引定位，性能会好很多。但生成列只能在建表时或者通过ALTER TABLE添加，且表达式必须确定性，不能依赖外部变量。创建之后，每次插入或更新内容，MySQL 会自动维护这个列的值。

第三是在应用层维护计数。如果统计需求非常频繁，而且写入频率不高，最简单的是在业务代码里每次写入或更新content时，算出目标词出现次数并额外存到一个字段里。这种反规范化手段非常实用，也是我最后通常会推荐给团队的方案。它牺牲了一点写入复杂度，换来的是所有报表查询的极速响应。

4.3 一个综合示例：统计评论中某个词的出现次数

最后用一个完整的例子来串一遍。假设我们有一张评论表review，字段有id、content、created_at。内容是用户填写的原始文本。产品同学要求统计每一条评论里“不错”这个词出现了几次，并且要按出现次数从高到低排序，只看出现过这个词的记录。

最清晰的写法是这样：

SELECT id, content, COALESCE( (CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, '不错', ''))) / CHAR_LENGTH('不错'), 0 ) AS cnt FROM review WHERE content LIKE '%不错%' ORDER BY cnt DESC;

这里先通过LIKE '%不错%'把没有这个词的评论全部过滤掉，避免全表无谓计算。每行命中后，用REPLACE + CHAR_LENGTH算出“不重叠”的计数，再用COALESCE兜底 NULL。结果按次数倒序排列。

如果产品还需要按天汇总所有评论里“不错”出现的总次数，可以再加上GROUP BY：

SELECT DATE(created_at) AS day, SUM( COALESCE( (CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, '不错', ''))) / CHAR_LENGTH('不错'), 0 ) ) AS total_cnt FROM review WHERE content LIKE '%不错%' GROUP BY DATE(created_at) ORDER BY day DESC;

这个 SQL 在几十万行数据上跑，效果还不错。如果哪天数据量到了千万级，或者报表要求秒级返回，我就会考虑前面说的生成列方案，或者在写入评论时维护一个keyword_cnt字段，让统计直接读取预计算结果。

另外，如果你在查询时使用的是存储过程或自定义函数，一定要记得把DETERMINISTIC、NO SQL或READS SQL DATA这些属性写清楚。很多初用者不写这些，导致无法在生成列或某些复制场景下使用函数。MySQL 对函数创建时的属性要求比较严格，少了关键字可能直接报错。

我自己的体会是，统计字符串出现次数这个需求，绝大多数场景用一行REPLACE + CHAR_LENGTH已经足够。只要把大小写、NULL、空子串这几个关键点想清楚，就能稳得住。如果你需要处理重叠计数，再考虑用存储过程或递归 CTE，但要注意控制数据规模。最后再多说一句，在真正写进生产报表前，一定要用几条手工可验算的数据测一遍，确认统计口径没问题，再放开跑。这个习惯帮我省过不少返工的时间。