你有没有遇到过这种需求在一张表里存了一堆文本比如文章的正文、用户的备注、日志里的错误信息然后你想统计某个关键词在每条记录里到底出现了几次。这个需求做搜索系统的人经常碰见做内容审核、做热词分析的人也会用到。先给结论MySQL没有直接内置一个“数子串出现次数”的函数但通过几个基础函数组合完全可以实现。这篇文章就把各种可行方案、背后的原理、还有我踩过的坑一次讲清楚。1. 需求拆解与核心实现思路1.1 先搞清楚这个任务到底难在哪在MySQL里做“查询字符串中某个字符串出现的次数”很多人第一反应是搜“有没有现成函数”结果发现真的没有。像INSTR只能返回第一次出现的位置SUBSTRING_INDEX能按分隔符截取但也只能帮我们间接算REGEXP能做匹配却默认只告诉你有没有匹配不直接给次数。既然没有现成API就得靠“函数组合”来拼。这类问题的核心技巧就一个用长度差来反推次数。你把原字符串和“把目标串删掉后”的字符串分别量一下长度相减后除以目标串的长度得到的就是出现次数。这个思路听起来很绕但实物例子一摆就通了。比如“abcabcabc”这个字符串目标串是“abc”。原长度是9把里面所有“abc”都替换成空字符串后剩下长度是0。9减0等于9再除以3刚好得到3次。这个逻辑经得起推敲因为它本质上是“总长度少了多少个目标串的长度就有多少个目标串”。1.2 为什么偏要用REPLACE做“删除”有人会问要删掉字符串里的某个子串为什么不写循环为什么不用正则偏要用REPLACE因为REPLACE天然支持全局替换能在一条SQL里把所有匹配的子串全部换掉。用正则要开启多段匹配、还得处理分组写起来复杂很多用循环要造存储过程或者写一大段逻辑。REPLACE是这条思路里最轻量的实现工具一条SQL能搞定的问题没必要引入更重的东西。但要注意这个思路背后有个隐藏前提REPLACE替换是“非重叠”的。什么意思呢如果目标串是“aaa”原字符串是“aaaa”按肉眼数里面其实有2个“aaa”第一个是下标1到3第二个是下标2到4它们是重叠的。但REPLACE处理时不会做重叠匹配它会从左到右找到第一个“aaa”就替换掉原字符串变成“a”最后算出来的次数是1而不是2。这个问题在目标串有重复字符时特别容易踩一会儿我会专门讲。2. 三种主流实现方案对比拆解2.1 方案一REPLACE长度差法最经典也最常用先看最通用的写法SELECT (LENGTH(abcabcabc) - LENGTH(REPLACE(abcabcabc, abc, ))) / LENGTH(abc) AS cnt;这个方案的优势是简洁、直观不依赖存储过程适合在查询中直接使用。如果你只是临时统计几个关键词或者在业务SQL里加一列出现次数这条语句几秒钟就能写出来。但有一个比较容易出错的点如果用LENGTH处理包含中文的字符串结果往往会让你怀疑人生。因为LENGTH在MySQL里返回的是“字节数”不是“字符数”。在utf8mb4字符集下一个汉字占3个字节字符串‘我爱我家的我’如果用LENGTH测量它不是5个字符而是15个字节。你拿它去数“我”的出现次数最后的计算结果会变成3倍。所以涉及中文一定要把LENGTH换成CHAR_LENGTHSELECT (CHAR_LENGTH(我爱我家的我) - CHAR_LENGTH(REPLACE(我爱我家的我, 我, ))) / CHAR_LENGTH(我) AS cnt;两条SQL看着差不多但结果天差地别。2.2 方案二自定义函数配合LOCATE逐条统计最灵活如果你要在多个地方反复使用或者需要支持“从指定位置开始查找”的复杂规则直接写一长串长度差法会让SQL变得特别难读。这个时候我建议写一个自定义函数把逻辑封装起来用起来就像SUBSTRING_COUNT(str, sub_str)一样方便。DELIMITER $$ CREATE FUNCTION STR_COUNT(s VARCHAR(1000), sub VARCHAR(255)) RETURNS INT DETERMINISTIC BEGIN DECLARE cnt INT DEFAULT 0; DECLARE pos INT DEFAULT 1; IF sub IS NULL OR sub THEN RETURN 0; END IF; WHILE pos CHAR_LENGTH(s) DO SET pos LOCATE(sub, s, pos); IF pos 0 THEN LEAVE; END IF; SET cnt cnt 1; SET pos pos CHAR_LENGTH(sub); END WHILE; RETURN cnt; END$$ DELIMITER ;这个函数的核心逻辑是从第1个字符开始用LOCATE(sub, s, pos)找目标串的位置找到了计数加1然后把起点指针挪到“这个目标串之后”继续往后找直到LOCATE返回0为止。跟REPLACE长度差法相比这个方式天然不会把重叠匹配搞乱也不是。你看代码里SET pos pos CHAR_LENGTH(sub)它跳过了整个目标串再继续所以仍然是非重叠统计。如果你就是要数重叠次数可以把这句改成SET pos pos 1从下一个字符开始继续找这样就能数出重叠匹配的次数。这是长度差法做不到的灵活调整。2.3 方案三REGEXP_COUNT函数最省事但要看版本MySQL 8.0.30及以上版本直接提供了REGEXP_COUNT函数原生支持统计子串出现次数。没这个函数之前大家只能靠方案一和方案二有了它之后SQL可以写成这样SELECT REGEXP_COUNT(a cat and a dog, another cat, cat) AS cnt;REGEXP_COUNT的第二个参数是正则表达式所以它不光能数固定的字符串还能数“所有符合某类模式”的数量。比如统计一段话里有多少个连续数字SELECT REGEXP_COUNT(订单号 1024金额 99数量 3, [0-9]) AS cnt;这条语句会返回3正好对应1024、99、3这三段数字。但用之前一定要确认版本5.7和8.0.30之前的版本没有这个函数。顺便说一句REGEXP_COUNT的参数顺序是REGEXP_COUNT(expr, pat[, pos[, match_type]])第三个参数可以指定从第几个字符开始匹配不写默认从1开始。2.4 三种方案的横向对比与选择建议方案写法复杂度是否支持中文是否支持重叠匹配适用版本适合场景REPLACE长度差低需用CHAR_LENGTH不支持所有版本临时查询、简单统计自定义函数中支持可调整所有版本多次复用、复杂逻辑REGEXP_COUNT最低支持按正则语义8.0.30新版本简单统计实际开发中怎么选我个人的经验是如果是临时统计一下直接用方案一如果这个统计逻辑会在多个查询里反复出现别犹豫写个自定义函数如果项目用的是MySQL 8.0.30以上版本并且你还需要同时做正则匹配类的统计优先用REGEXP_COUNT因为它能少写很多代码。3. 完整实操演练从建表到业务查询3.1 模拟一个真实的业务表结构光讲理论没用我们拿一个实际场景做全流程演示。假设你在做一个内容管理系统有一张文章表里面有个content字段存文章正文。现在运营提了个需求统计每篇文章里“优惠券”这个词出现了几次用来判断这篇文章是不是在重点推某个活动。先建表插数据CREATE TABLE article ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(200), content TEXT ); INSERT INTO article (title, content) VALUES (年末大促开启, 全场商品优惠券发放中领取优惠券后下单立减优惠券数量有限先到先得。), (新功能上线说明, 本次版本优化了首页加载速度新增收藏功能修复已知问题。), (会员日专属福利, 会员日当天可领三张优惠券优惠券仅限会员使用另外还有专属折扣。), (系统维护公告, 本周六凌晨系统维护期间暂停访问请提前保存数据。), (优惠券使用技巧, 一张优惠券只能用于一个订单优惠券过期后自动作废请及时使用优惠券。);现在要统计每行content里“优惠券”出现的次数。用方案一的写法SELECT id, title, (CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, 优惠券, ))) / CHAR_LENGTH(优惠券) AS coupon_count FROM article;执行结果里第1篇文章“优惠券”出现3次第3篇出现2次第5篇出现3次第2篇和第4篇为0。这跟上面的数据是一致的。3.2 进阶实操统计一个字符串在查询结果中的总次数上面的查询是按行统计如果你想要的是全表一共出现了多少次可以在外层套个SUMSELECT SUM( (CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, 优惠券, ))) / CHAR_LENGTH(优惠券) ) AS total_coupon_count FROM article;这种统计在运营看板上很实用比如计算某个关键词在全站内容里的总曝光量然后做趋势对比。如果你想加上过滤条件比如只统计标题里含“会员”的文章也很简单SELECT id, title, (CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, 优惠券, ))) / CHAR_LENGTH(优惠券) AS coupon_count FROM article WHERE title LIKE %会员%;记住这条逻辑统计条件放在WHERE里统计计算放在SELECT的表达式里两者互不干扰。3.3 多关键词同时统计一条SQL实现多个计数列实际业务里往往不只需要统计一个关键词而是需要同时统计好几个词。比如运营想比较“优惠券”“折扣”“会员”三个词的热度一条SQL就能搞定SELECT id, title, (CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, 优惠券, ))) / CHAR_LENGTH(优惠券) AS coupon_count, (CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, 折扣, ))) / CHAR_LENGTH(折扣) AS discount_count, (CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, 会员, ))) / CHAR_LENGTH(会员) AS member_count FROM article ORDER BY coupon_count DESC;这样一次把三个关键词的次数都算出来还支持按某个词频排序。这里有个性能提醒在ORDER BY里使用表达式排序如果表数据量很大并且content字段又很长这个查询会非常吃CPU。因为MySQL需要对每一行都做多次全字段替换和长度计算索引在这类场景基本帮不上忙。后面我专门讲怎么优化。3.4 中文、大小写、空字符串等边界情况的处理中文处理刚才讲过了核心就是CHAR_LENGTH。下面重点说一下大小写和空字符串的坑。MySQL的REPLACE在做字符串替换时默认情况下会区分大小写。所以统计“abc”出现的次数时字符串“ABC abc Abc”只会统计到1次就是完全小写的那一次。如果业务上要求不区分大小写需要先把两边都转成小写再计算SELECT (CHAR_LENGTH(LOWER(ABC abc Abc)) - CHAR_LENGTH(REPLACE(LOWER(ABC abc Abc), abc, ))) / CHAR_LENGTH(abc) AS cnt;再看空字符串的情况。如果你不小心把目标串传成了空字符串CHAR_LENGTH()会返回0整个表达式就会出现“除数为0”的错误。所以不要天真地以为传空串只会返回0。无论是直接拼SQL还是写函数都要对空字符串做拦截。我习惯在自定义函数里加这一段IF sub IS NULL OR sub THEN RETURN 0; END IF;这也算是一种防御式编程宁可多写两行也别让线上SQL崩。4. 常见问题与性能优化经验4.1 常见报错和逻辑错误的排查速查表现象原因解决方案中文统计结果变成实际次数的多倍用了LENGTH按字节数统计中文在utf8mb4下占3字节换成CHAR_LENGTH按字符数统计除数为0的错误DIVISION BY 0目标串为空字符串或为NULL在函数里拦截空字符串或SQL中用IFNULL/CASE判断统计结果比你以为的少REPLACE从左到右非重叠匹配存在重叠的目标串统计不到改用自定义函数并调整pos递增步长为1统计结果为0但肉眼明显有匹配大小写不一致用LOWER统一转小写后再统计想统计数字却一直不准确某些数字是int类型拼进SQL变成数字类型参与计算先显式CAST转换为字符串8.0.30以下版本用REGEXP_COUNT报错版本不支持该函数改用REPLACE方案或升级版本4.2 大表场景别让子串统计拖垮你的数据库我见过有人拿这个统计逻辑直接扫全表几百万行结果就是把数据库CPU直接打满。问题出在哪里因为REPLACE和CHAR_LENGTH都是逐行、逐字符处理的内容越长计算成本越高而且这类表达式无法使用普通索引加速。针对这类场景我自己的几个处理思路如下。第一把统计结果缓存到字段里。如果关键词是相对固定的在建表时专门加一个keyword_count字段在写入或更新文章时直接算好存进去查询时只读字段。这种空间换时间的思路最直接也最有效。第二缩小统计范围。如果只需要统计前N个字符的词频先截取再统计避免对整篇长文做全量替换。比如统计正文前500字里的关键词次数SELECT (CHAR_LENGTH(LEFT(content, 500)) - CHAR_LENGTH(REPLACE(LEFT(content, 500), 优惠券, ))) / CHAR_LENGTH(优惠券) AS coupon_count FROM article;这样能省不少计算量当然前提是业务上“只看开头”也能接受。第三把全表统计放到从库或者改到离线任务里。全表扫描型的子串统计本质上是一个批处理任务并不适合放在核心交易链路中高频执行。可以定时把数据同步到报表库或数据仓库再在那里做统计。4.3 我实际踩过的一些坑和心得这个需求我前前后后写了几十次印象最深的是第一次给一个内容平台做热词统计当时直接用LENGTH数中文关键词结果返回的词频全部乘了3看结果的时候差点以为平台文章都在“疯狂堆词”。后来才意识到LENGTH是字节数CHAR_LENGTH才是字符数。还有一个经验是如果统计的目的是“判断某个关键词是否出现至少N次”与其算出准确次数不如用更轻量的方式做过滤。比如判断某个词是否出现至少2次可以先算出第一次出现的位置LOCATE(keyword, content)再从第一次位置之后继续找第二次如果第二次位置不是0就说明至少出现了2次。这种写法在这类场景下比完整计算所有次数要快一些。最后再分享一个做报表时的小技巧如果你要把统计结果按“低频、中频、高频”分桶可以直接把统计表达式包在CASE WHEN里减少一次重复计算SELECT id, cnt, CASE WHEN cnt 0 THEN 无 WHEN cnt 2 THEN 低频 WHEN cnt 5 THEN 中频 ELSE 高频 END AS freq_level FROM ( SELECT id, title, (CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, 优惠券, ))) / CHAR_LENGTH(优惠券) AS cnt FROM article ) t;子查询先把词频算出来外层再分桶条理清晰也不会因为重复写长表达式导致SQL又臭又长。这个习惯保持下来维护成本会低很多。
