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适合比较老的版本。这几种方案并不冲突可以按实际场景灵活选。下面我会把每一条都讲透。方案MySQL 版本要求是否支持重叠计数性能使用难度LENGTH REPLACE所有版本不支持高低存储过程/自定义函数所有版本可以支持中中递归 CTE 逐位扫描8.0支持低数据大时不推荐中数字辅助表所有版本支持中中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(中国中国)返回 4CHAR_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();分母直接变成 0MySQL 会报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等于目标计数 1SUBSTRING(aaaa, 2, 2)取到aa计数 2SUBSTRING(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 BYSELECT 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但要注意控制数据规模。最后再多说一句在真正写进生产报表前一定要用几条手工可验算的数据测一遍确认统计口径没问题再放开跑。这个习惯帮我省过不少返工的时间。