MySQL内置函数全解析：从字符串清洗到索引优化的避坑指南

写SQL写了快十年，MySQL的内置函数依然是我最常用的“工具箱”。前阵子接手一个历史数据迁移的活，源库导出的手机号有带86的、有带+86的、有中间漏了空格、还有干脆把座机号写进去的，扒了一个下午的字符串函数，才把数据洗干净。说实话，MySQL内置函数这东西，平时不起眼，但一旦遇到报表、清洗、迁移、统计这类脏活累活，它是真的能救命。这篇文章我把MySQL内置函数按类别系统过一遍——字符串、数值、日期时间、流程控制和聚合函数，顺带讲讲日常使用中最容易踩的坑，以及一个很多人没意识到的点：函数虽好用，但用在索引列上会让查询变慢。无论是刚入门的新手，还是写了一段时间SQL想查漏补缺的同学，这份梳理应该都能帮到你。

1. 内置函数为什么值得系统过一遍：三个真实场景

很多人觉得内置函数就是语法糖，用到再查手册就行。但我的观点不一样：函数熟练度直接决定你写SQL的效率，也决定你处理脏数据、做统计报表时是“十分钟搞定”还是“加班到深夜”。下面三个场景是我这几年反复遇到的，每一个背后都对应一批内置函数。

1.1 场景一：Excel报表逻辑迁移到SQL

前几年团队一直用Excel做周报，几十个sheet套来套去，月末卡到怀疑人生。后来想把统计逻辑全部迁到MySQL，才发现Excel里天天用的LEFT、MID、TEXT、IF，在SQL里全都有对应实现——SUBSTRING、DATE_FORMAT、CASE WHEN。如果对这些函数不熟，就只能把数据拉到应用层，用Java或Python一条条算，不仅代码啰嗦，性能也差。数据量一旦上了百万，应用层内存直接告急，最后兜兜转转还是得回到SQL函数这条路上。

1.2 场景二：字符串清洗与数据迁移

老系统导出来的数据，什么妖魔鬼怪都有：备注字段里混着换行符、制表符、全角空格，手机号加区号不加区号各占一半，用户姓名前后带着看不见的空白字符。这种场景下，TRIM、REPLACE、REGEXP_REPLACE就是你的清洁工。我习惯先写一条SELECT把清洗后的结果量出来看看，确认无误后再套UPDATE刷回库。整个过程如果不会字符串函数，基本无从下手。

1.3 场景三：日期时间加工与周期跑批

每天凌晨跑批，要从订单表里按月、按周、按小时聚合数据。这时候DATE_FORMAT、DATE_ADD、DATEDIFF、LAST_DAY就是核心工具。不会日期函数的人，只能把时间戳传到应用层，在内存里自己切月份、算周数，数据一多，慢不说，代码还特别容易错。我见过太多线上Bug，不是业务逻辑想错了，而是把日期换算逻辑写在了应用代码里，不同时区一搅和，结果就飘了。

所以内置函数根本不是要不要学的问题，而是你能不能把数据加工逻辑下沉到数据库层，让SQL自己把活干完的问题。下面我按类别逐个拆解，每个函数都带例子，方便你直接抄。

2. 字符串函数的正确打开方式：从CONCAT到REGEXP_REPLACE

字符串函数是日常用得最频繁的一类，也是坑最多的一类。很多新手栽跟头，都是栽在“看起来很简单，实际上有约定”的地方。

2.1 拼接、截取、替换三件套

先说拼接。MySQL里拼接字符串有几个选择：直接加号不行，那是数值运算；CONCAT函数本身也有一个很容易踩的坑——只要有一个参数为NULL，整个结果就是NULL。很多线上数据查出来莫名其妙为空，查了半小时，结果发现某个字段是NULL。

SELECT CONCAT('a', 'b', 'c'); -- abc SELECT CONCAT('a', NULL, 'c'); -- NULL，容易踩坑 SELECT CONCAT_WS('-', 'a', NULL, 'c'); -- a-c，自动跳过NULL

CONCAT_WS是带分隔符的拼接，它有个隐藏优势：会自动忽略NULL参数，不会因为某个字段为空就把整条记录拼没了。拼地址、拼姓名、拼文件路径，我基本都用CONCAT_WS，省心。

再说截取。SUBSTRING的起始位置是从1开始，不是从0开始，和Java、Python里完全不一样，写错的人非常多。

SELECT SUBSTRING('hello world', 7, 5); -- world，位置从1开始 SELECT LEFT('hello', 2); -- he SELECT RIGHT('hello', 2); -- lo

最后说替换。REPLACE函数是全局替换，把所有匹配到的子串全部换掉，不是只换第一个。

SELECT REPLACE('aaa.bbb.ccc', '.', '/'); -- aaa/bbb/ccc SELECT REPLACE('2024-03-15', '-', ''); -- 20240315

实际做数据清洗时，我经常把REPLACE和TRIM配合使用。源数据里既有换行符又有空格，先TRIM去头尾，再用REPLACE把内部的\r\n替换掉。注意REPLACE的匹配默认受排序规则影响，表如果建的是utf8mb4_general_ci，它是不区分大小写的。

2.2 查找定位与正则匹配

判断一个子串在字符串里的位置，用LOCATE或INSTR。LOCATE还可以传第三个参数，指定从第几个字符开始找，这个在解析复杂文本时特别有用。

SELECT LOCATE('bc', 'abcd'); -- 2 SELECT INSTR('abcd', 'bc'); -- 2，参数顺序跟LOCATE相反 SELECT LOCATE('o', 'hello world', 5); -- 7，从第5个字符开始找

LIKE和REGEXP是两类完全不同的匹配方式。LIKE的%和_是通配符，适合简单模糊查询；REGEXP支持完整的正则表达式，适合格式校验。

SELECT 'abc123' REGEXP '^[a-z]+[0-9]+$'; -- 1，表示匹配 SELECT REGEXP_REPLACE('1a2b3c', '[0-9]', ''); -- abc，8.0支持

MySQL 8.0里REGEXP_REPLACE非常实用，做敏感信息脱敏、清洗非数字字符都是一行搞定。比如手机号只保留后四位：REGEXP_REPLACE(phone, '^\d{7}', '*******')。注意写反斜杠的时候，在SQL字符串里要写成两个反斜杠。

2.3 字符集与排序规则的坑

这部分是我的血泪教训。LENGTH和CHAR_LENGTH都表示字符串长度，但前者返回字节数，后者返回字符数。在utf8mb4字符集下，一个中文字符占3个字节，一个emoji占4个字节。检查用户昵称长度、截断文本时，用错函数会出现“明明只有40个字符，程序却报长度超限”的诡异问题。

SELECT LENGTH('abc'); -- 3 SELECT LENGTH('你好'); -- 6，utf8mb4下一个中文3字节 SELECT CHAR_LENGTH('你好'); -- 2，按字符数算

另一个隐藏问题是排序规则。utf8mb4_general_ci这个分类中ci代表case-insensitive，查询时LIKE和=默认不区分大小写。如果你在某个字段上做区分大小写的匹配，发现结果不对，先别怀疑函数，去查一下表的COLLATE是什么。

3. 数值函数与日期时间函数：业务计算的高频组合

数值和日期这两类函数在业务系统里几乎是绑在一起出现的：算金额、算折扣、算时长、算周期、按月聚合成报表。这里面的坑比想象中多，尤其是精度和边界值。

3.1 数值处理：ROUND、TRUNCATE与精度陷阱

ROUND是四舍五入，TRUNCATE是直接截断，看起来差不多，实际用起来差别很大。ROUND支持负数位数，比如ROUND(1234.567, -2)会把十位四舍五入到百位，结果是1200；TRUNCATE同样支持，但它只做截断。

SELECT ROUND(3.14159, 2); -- 3.14 SELECT TRUNCATE(3.14159, 2); -- 3.14 SELECT ROUND(1234.567, -1); -- 1230 SELECT TRUNCATE(1234.567, -1); -- 1230

金额计算我的建议是：不要用FLOAT或DOUBLE，直接用DECIMAL。浮点数在计算机内部是二进制存储，0.1在浮点里是个无限循环小数，累加多了误差就会显现。MySQL的ROUND函数在不同版本对浮点数的处理也有过历史差异，所以涉及钱、涉及百分比，优先考虑DECIMAL类型，计算和舍入都更可控。

MOD取模也经常被忽略。业务上分库分表、按ID取余数路由，靠的就是MOD。

SELECT MOD(10, 3); -- 1 SELECT MOD(-7, 2); -- -1，注意负数取模结果因数据库而异

在设计分表策略时，MOD(id, 10)可以把数据均匀分散到10张表，配合一个稳定的哈希算法，效果很好。

3.2 日期时间类型的本质

在讲日期函数之前，得先弄清DATE、DATETIME、TIMESTAMP三者的区别。DATE只存日期，DATETIME存日期和时间，TIMESTAMP也存日期和时间，但它跟时区有关，而且存储范围只有1970年到2038年。TIMESTAMP实际存储的是UTC整数，在展示时按会话时区换算。如果你的业务是全球化、跨时区的，选TIMESTAMP要注意时区问题；如果只关心本地时间，DATETIME通常更省心。

还有一个经典问题：NOW()和SYSDATE()的区别。NOW()是语句开始执行的时间，一条SQL无论跑多久，NOW()都返回同一个值；SYSDATE()是函数实际执行那一刻的时间。这个差异在长事务里会造成“同一批数据时间戳不一致”的错觉，我建议绝大多数场景统一用NOW()。

3.3 日期格式化、加减和间隔计算

DATE_FORMAT是报表统计的万能工具，把日期转成“年月日”“年月”“周几”都靠它。

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2024-03-15 14:30:00 SELECT DATE_FORMAT(NOW(), '%Y-%m'); -- 2024-03

反过来，字符串转日期用STR_TO_DATE。

SELECT STR_TO_DATE('2024-03-15', '%Y-%m-%d'); -- 2024-03-15

日期加减用DATE_ADD和DATE_SUB，配合INTERVAL关键字，单位可以是DAY、MONTH、YEAR、HOUR、MINUTE等。

SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH); -- 2024-02-29，注意跨月逻辑 SELECT DATE_SUB(NOW(), INTERVAL 7 DAY);

这里有个特别容易错的点：DATE_ADD('2024-01-31', INTERVAL 1 MONTH)在MySQL里返回2024-02-29，它不会“溢出”到3月2日。如果业务需要“月底加一个月仍落月底”，可以直接用LAST_DAY再取最大值，或者干脆加个月份字段再处理，别依赖DATE_ADD的默认行为。

间隔计算有两个函数：DATEDIFF和TIMESTAMPDIFF。DATEDIFF只按日期部分算，返回天数差值；TIMESTAMPDIFF可以指定单位，精确到秒、小时、分钟，还支持负数，非常灵活。

SELECT DATEDIFF('2024-03-01', '2024-02-01'); -- 29 SELECT TIMESTAMPDIFF(DAY, '2024-02-01', '2024-03-01'); -- 29 SELECT TIMESTAMPDIFF(HOUR, NOW(), '2024-03-16 00:00:00'); -- 按小时差

我统计复合时长时，习惯用TIMESTAMPDIFF(SECOND, start_time, end_time)取秒，再在应用层格式化，既精确又不依赖日期格式的字符串比较。LAST_DAY也是月底统计的好帮手，比如“查每月最后一天的数据”，直接LAST_DAY(create_date)然后再范围匹配。

4. 流程控制与聚合函数：让SQL拥有业务判断力

流程控制函数和聚合函数组合在一起，SQL就不只是查数据，而是能“算业务”了。常见的场景是统计通过率、达标率、各种分组汇总。

4.1 IF、IFNULL、NULLIF与CASE WHEN

IF函数和Excel里的IF几乎一样，IF(expr, v1, v2)。IFNULL(x, 0)专门处理NULL，是统计时最常见的写法——汇总时把NULL当成0，避免计算结果变成NULL。

SELECT IF(1 > 2, 'yes', 'no'); -- no SELECT IFNULL(NULL, 'default'); -- default SELECT COALESCE(NULL, NULL, 'third'); -- third

IFNULL和COALESCE的区别是：IFNULL只能给两个参数，COALESCE可以给多个参数，依次取第一个非NULL值。写多字段兜底时，COALESCE更合适。NULLIF(a, b)的作用是：如果a等于b，返回NULL，否则返回a。这个函数在做除法的防零保护时特别有用。

SELECT NULLIF('a', 'a'); -- NULL SELECT NULLIF('a', 'b'); -- a

CASE WHEN是SQL里的switch，也是条件统计的基础。多分支判断、等级划分，都用它。

SELECT CASE WHEN score >= 90 THEN 'A' WHEN score >= 60 THEN 'B' ELSE 'C' END AS grade FROM exam;

要注意：CASE WHEN的求值顺序是从上往下，第一个满足的条件生效，所以条件顺序是有意义的。把>=90写在>=60前面，才能正确划分等级。

4.2 聚合函数：COUNT、SUM、AVG与NULL

COUNT族里最大的坑是COUNT()、COUNT(1)、COUNT(col)的区别。COUNT()统计行数，COUNT(1)和COUNT(*)几乎没有区别，统计的都是“行数”而不是字段值，哪怕这一行所有字段都是NULL也会计入。COUNT(col)只统计该字段非NULL的行数，这是统计“有值人数”的关键。

SELECT COUNT(*) FROM users; -- 总行数 SELECT COUNT(nickname) FROM users; -- nickname非NULL的行数 SELECT COUNT(DISTINCT dept_id) FROM users; -- 去重后的部门数

SUM遇到NULL时的行为也要留意。SUM(col)在col全为NULL时返回NULL，而不是0。这导致报表里经常出现“合计为空”的异常，解决办法就是SUM(IFNULL(col, 0))。

AVG会忽略NULL行，它等于SUM(非NULL值)/COUNT(非NULL值)。如果你希望NULL当作0参与平均，也要先IFNULL处理。另外GROUP_CONCAT可以把一组的多个值拼成一列，在“查某个用户的所有角色名”这类场景太好用了。

SELECT dept_id, GROUP_CONCAT(name ORDER BY name SEPARATOR '、') AS names FROM employee GROUP BY dept_id;

GROUP_CONCAT默认长度限制是1024字节，超过会被截断，而且结果会静默截断不报错。遇到拼接结果莫名其妙少了后半段，先检查group_concat_max_len参数，必要时在会话里调大。

4.3 聚合加条件判断：一行SQL出多列统计

这是我最常用的技巧之一。想统计每个部门的成功单量和总数，不需要写多个子查询，一个CASE WHEN套SUM就搞定。

SELECT dept_id, SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS success_cnt, COUNT(*) AS total_cnt, SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) / COUNT(*) AS success_rate FROM orders GROUP BY dept_id HAVING success_cnt > 100;

HAVING专门用来过滤聚合结果，在GROUP BY之后生效。很多人分不清WHERE和HAVING，记住一句话：WHERE是分组前过滤原始行，HAVING是分组后过滤聚合结果。对聚合函数做条件，比如COUNT(*) > 10，只能放HAVING里。这条SQL跑出来，每个部门的整体情况一目了然，不需要写三层嵌套子查询。

5. 函数与索引失效：为什么“函数帮你省事，DBA帮你收尸”

这是内置函数里最容易被忽略，但后果最严重的一个话题。函数用得好是提效，用在索引列上就是给查询埋雷。

5.1 索引列上使用函数的后果

B+树索引是按原始值排序和查找的。一旦你在WHERE条件的索引列上包了一层函数，优化器就无法利用索引的有序结构去定位数据，只能把整列的值全部取出来，算完函数再逐行比较，也就是全表扫描。我见过太多类似的慢查询：

-- 反例：create_time上有索引，但DATE_FORMAT让它失效 SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-03-15';

这条SQL想查某一天的单子，逻辑没问题，但EXPLAIN一看，type=ALL，rows是整张表。正确的写法是把它改造成范围查询，让优化器可以直接在索引上定位这个时间区间：范围查询只需要判断大小，索引天然擅长。

-- 正例：改成范围查询，利用索引 SELECT * FROM orders WHERE create_time >= '2024-03-15 00:00:00' AND create_time < '2024-03-16 00:00:00';

同样的问题也出现在YEAR(create_time)=2024、MONTH(create_time)=3这类写法上。除非索引建的就是函数索引，否则一律改写为范围。

5.2 什么时候可以放心用：函数索引与生成列

MySQL 8.0.13之后支持直接给表达式建索引，这个特性在业务里很实用。如果你确实经常按DATE_FORMAT后的日期去查，与其每次全表扫，不如给这个表达式建一个索引：

ALTER TABLE orders ADD INDEX idx_create_date ((DATE_FORMAT(create_time, '%Y-%m-%d')));

如果用的是MySQL 5.7，没有函数索引，可以用生成列方案：新加一列，值由表达式自动生成，再在生成列上建索引。查询时直接按新列过滤。这相当于把“函数计算结果”物化成一列，既有业务便利性，又能走索引。

5.3 一个真实慢查询的排查过程

上个月压测环境有个接口突然超时，我拉出慢日志，看到这样一条SQL：

SELECT * FROM order_record WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-03-15' ORDER BY id DESC LIMIT 20;

EXPLAIN一看，type=ALL，key为空，rows显示约180万行。这就是典型的索引列上套函数导致全表扫描。我改成范围查询后，EXPLAIN显示type=range，key命中了idx_create_time，rows降到2000左右，接口响应从1.2秒掉到30毫秒。

提示：判断一条SQL能不能用上索引，别靠猜，直接EXPLAIN。看type和key这两列，type是ALL或者rows特别大，基本就是索引没走对。

6. 一套自查清单：内置函数使用前先过一遍

踩过这么多次坑之后，我给自己定了一套固定的写作流程，每次写复杂SQL前都照着过一遍，分享给你。

6.1 我写SQL前的固定流程

• 第一步，WHERE条件里的索引列，有没有被函数包住？有就改成范围条件，或者考虑函数索引。
• 第二步，字符串拼接前先想清楚NULL会不会让对方结果消失，该用CONCAT_WS还是COALESCE兜底。
• 第三步，统计汇总时，聚合列里出现NULL要不要参与计算，参与就套IFNULL，不参与要保持默认行为并写清楚。
• 第四步，日期运算优先用DATE_ADD、DATE_SUB、TIMESTAMPDIFF这类逻辑明确的函数，少用字符串格式化之后的比较。
• 第五步，任何带GROUP_CONCAT的SQL，先评估拼接结果会不会超过group_concat_max_len。

6.2 高频函数速查表

6.3 关于“模板SQL”的积累习惯

这几年带过不少新人，我发现一个现象：SQL写得好的人，电脑里都存着一份自己的“模板SQL”。比如移动平均、同比环比、去重统计、行转列、分组TopN，这些复杂场景的写法不是每次都现场想，而是平时积累好固定写法，遇到类似需求直接改表名和字段就行。内置函数是这些模板的原材料，函数用得熟，模板积累得就快。

我现在的习惯是把Excel里常用的函数挨个翻译成SQL版本，遇到新的处理需求就顺手记成一小段备注SQL。时间长了，你会发现大部分数据加工需求，几行内置函数组合就能解决，根本不需要把数据捞到应用层折腾。

最后说点体外话。内置函数练到什么程度算熟？我的标准是，看到需求能在一分钟内想到用哪几个函数组合，而不是掏出手机现搜。平时可以拿自己的业务表练手，把Excel里常用的函数逐个翻译成SQL，写多了自然就顺手。函数是死的，场景是活的，多积累几个模板SQL，后面写报表和跑批会轻松很多。