你有没有遇到过这种需求：在一张表里存了一堆文本，比如文章的正文、用户的备注、日志里的错误信息，然后你想统计某个关键词在每条记录里到底出现了几次。这个需求做搜索系统的人经常碰见，做内容审核、做热词分析的人也会用到。先给结论：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_LENGTH：
SELECT (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 进阶实操：统计一个字符串在查询结果中的总次数
上面的查询是按行统计，如果你想要的是全表一共出现了多少次，可以在外层套个SUM：
SELECT 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又臭又长。这个习惯保持下来，维护成本会低很多。
