一、问题场景与痛点
在数据库设计中，经常会遇到统计某一些数据的最大数量最小数量等，特别是**逗号分隔字段 **的统计会显得非常困难
下面以我生产上遇到的一个问题讲解：
有个需求是在o_work_order表中统计sn字段中哪个工单号的数量最多，sn的存储结构如下：“CF208RC1,CF208L11,CF208L11,CF208L11,…”：
- 传统方案：需拆分字段为临时表或使用JSON解析，代码复杂且性能低下。
- 高效需求：直接计算分隔符数量，避免中间表生成。
二、核心公式解析：LENGTH() - LENGTH(REPLACE()) + 1
通过字符串长度差值计算元素数量，是最高效的纯SQL方案：
SELECT order_id,LENGTH(sn) - LENGTH(REPLACE(sn, ',', '')) + 1 AS sn_count
FROM o_work_order
原理解析（以 sn='A,B,C' 为例）：
| 步骤 | 表达式 | 示例值 | 说明 |
|---|---|---|---|
| 1 | LENGTH(sn) | 5 | 原始字符串长度（含逗号） |
| 2 | REPLACE(sn, ',', '') | 'ABC' | 删除所有逗号 |
| 3 | LENGTH(REPLACE(...)) | 3 | 无逗号字符串长度 |
| 4 | 差值 = 步骤1 - 步骤3 | 5 - 3 = 2 | 逗号个数 |
| 5 | sn_count = 差值 + 1 | 2 + 1 = 3 | 最终元素数量 |
✅ 优势：
无需递归或子查询，
性能提升10倍以上；
兼容MySQL 5.x至8.x所有版本。
三、优化技巧
-
过滤空值避免干扰
添加条件排除无效数据：WHERE sn IS NOT NULL AND sn != '' -- 忽略空字段 -
索引加速查询
对高频过滤字段创建联合索引：CREATE INDEX idx_category_sn ON o_work_order(order_category, sn); -
处理特殊格式
连续逗号
（如A,C）：
先标准化格式：
REPLACE(REPLACE(sn, ',,', ','), ',,', ',') -- 递归替换连续逗号结尾逗号（如A,B,）：公式仍正确计数（结果为3），无需额外处理。
四、总结
最后的结果也是达到预期如下图所示
核心公式本质：
元素数量 = 分隔符数量 + 1
通过字符串函数直接计算，避免复杂解析过程，是处理分隔字段的性能最优解。