写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自动跳过NULLCONCAT_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], ); -- abc8.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(你好); -- 6utf8mb4下一个中文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)会把十位四舍五入到百位结果是1200TRUNCATE同样支持但它只做截断。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 WHENIF函数和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); -- thirdIFNULL和COALESCE的区别是IFNULL只能给两个参数COALESCE可以给多个参数依次取第一个非NULL值。写多字段兜底时COALESCE更合适。NULLIF(a, b)的作用是如果a等于b返回NULL否则返回a。这个函数在做除法的防零保护时特别有用。SELECT NULLIF(a, a); -- NULL SELECT NULLIF(a, b); -- aCASE 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与NULLCOUNT族里最大的坑是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一看typeALLrows是整张表。正确的写法是把它改造成范围查询让优化器可以直接在索引上定位这个时间区间范围查询只需要判断大小索引天然擅长。-- 正例改成范围查询利用索引 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 一个真实慢查询的排查过程上个月压测环境有个接口突然超时我拉出慢日志看到这样一条SQLSELECT * FROM order_record WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-03-15 ORDER BY id DESC LIMIT 20;EXPLAIN一看typeALLkey为空rows显示约180万行。这就是典型的索引列上套函数导致全表扫描。我改成范围查询后EXPLAIN显示typerangekey命中了idx_create_timerows降到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 高频函数速查表分类函数用途注意事项字符串CONCAT / CONCAT_WS拼接CONCAT遇NULL整体为NULLCONCAT_WS会跳过NULL字符串SUBSTRING / LEFT / RIGHT截取起始位置从1开始字符串REPLACE替换全局替换受排序规则影响字符串LOCATE / INSTR定位LOCATE支持指定起始位置字符串REGEXP_REPLACE正则替换8.0以上可用注意反斜杠转义字符串CHAR_LENGTH / LENGTH字符数/字节数utf8mb4下一个中文占3字节数值ROUND / TRUNCATE四舍五入/截断负位数为整数部分舍入数值CEIL / FLOOR向上/向下取整负数方向容易搞反数值MOD取模负数结果因数据库而异日期DATE_FORMAT日期格式化格式符区分大小写日期STR_TO_DATE字符串转日期格式必须匹配日期DATE_ADD / DATE_SUB日期加减月末溢出逻辑要注意日期DATEDIFF天数差只按日期部分计算日期TIMESTAMPDIFF精确间隔支持秒、分钟、小时等日期LAST_DAY当月最后一天月底统计常用流程IF / IFNULL / NULLIF条件取值注意参数个数差异流程CASE WHEN多分支条件按顺序求值聚合COUNT / SUM / AVG统计汇总COUNT(col)不计NULLSUM全NULL返回NULL聚合GROUP_CONCAT行转列拼接默认长度1024字节6.3 关于“模板SQL”的积累习惯这几年带过不少新人我发现一个现象SQL写得好的人电脑里都存着一份自己的“模板SQL”。比如移动平均、同比环比、去重统计、行转列、分组TopN这些复杂场景的写法不是每次都现场想而是平时积累好固定写法遇到类似需求直接改表名和字段就行。内置函数是这些模板的原材料函数用得熟模板积累得就快。我现在的习惯是把Excel里常用的函数挨个翻译成SQL版本遇到新的处理需求就顺手记成一小段备注SQL。时间长了你会发现大部分数据加工需求几行内置函数组合就能解决根本不需要把数据捞到应用层折腾。最后说点体外话。内置函数练到什么程度算熟我的标准是看到需求能在一分钟内想到用哪几个函数组合而不是掏出手机现搜。平时可以拿自己的业务表练手把Excel里常用的函数逐个翻译成SQL写多了自然就顺手。函数是死的场景是活的多积累几个模板SQL后面写报表和跑批会轻松很多。