做后端开发尤其是每天跟数据打交道的几乎离不开SQL。写SQL的时候最烦什么不是join查不出来而是数据处理那点破事——日期要格式化、字符串要拼接、数值要取整你要是全靠应用层代码去搞逻辑绕一大圈不说性能还浪费。MySQL内置函数就是干这个用的把常见的日期计算、字符串处理、数学运算、流程判断直接下沉到SQL层面一条语句解决问题。这篇文章不聊高深理论就把MySQL内置函数系统地过一遍日期函数、字符串函数、数学函数以及其他常用函数。我会结合真实开发场景来聊每个函数都给出可复现的案例包括参数踩坑和版本注意事项。适合刚接触MySQL的初学者也适合想系统梳理一遍函数体系的开发同学。看完了你至少能知道遇到时间区间统计怎么写、字符串截取怎么不踩下标坑、金额计算怎么避免精度翻车。1. 内置函数概览为什么你该系统掌握这些函数MySQL内置函数是一组预定义好的功能模块你只需要调用函数名、传对参数就能拿到处理后的结果。它覆盖了数据操作的大部分高频场景分类清晰语法统一用好了能省掉大量应用层代码。1.1 MySQL内置函数分类一览函数按用途大致可以分成以下几类我先把最常用的列出来函数分类代表函数高频应用场景日期函数NOW、DATE_FORMAT、DATEDIFF、DATE_ADD时间格式化、时间区间统计、年龄计算字符串函数CONCAT、SUBSTRING、REPLACE、TRIM字段拼接、文本截取、脏数据清洗数学函数ROUND、CEIL、FLOOR、ABS金额取整、分页随机、数值精度处理流程控制函数IF、IFNULL、CASE WHEN业务逻辑分支、空值兜底类型转换函数CAST、CONVERT、COALESCE字符串转数字、字符集转换聚合函数COUNT、SUM、AVG、GROUP_CONCAT统计报表、分组汇总加密函数MD5、SHA2、AES_ENCRYPT数据脱敏、校验和计算实际开发中日期、字符串、数学这三类用得最频繁后两类属于关键时刻能救命的那种。就像你工具箱里的螺丝刀可能不是每天用但真需要的时候没有它你就得拿牙咬。1.2 为什么优先用内置函数而不是程序处理很多人有个习惯SQL只负责查询所有处理逻辑都丢给Java、Python、Go。这种思路在数据量小的时候没啥问题但一旦数据量上来问题就明显了。第一是性能差距。你写一条SQL从数据库拉出上万行原始数据再在应用层循环处理跟直接在SQL里用函数处理完只返回结果数据库网络传输的数据量差了几个量级。数据库本身的函数是编译好的执行效率远高于你应用层的循环。第二是逻辑统一。如果多个服务都依赖同一套处理逻辑比如时间格式化方式、字符串脱敏规则放在SQL层写死所有调用方拿到的结果是一致的。要是各端自己实现格式对不上就是线上事故。第三是开发效率。一条SQL能完成的事没必要写十几行代码。举个最典型的例子统计最近7天每天订单量应用层方案是先查出所有订单再在内存里写循环按天分组SQL方案就是一行GROUP BY加一个日期函数的事语句短、逻辑直观还不用写完代码再写单元测试。当然也要提醒一句内置函数不是越多越好。过于复杂的逻辑塞进SQL里可读性会急剧下降而且后续不好维护。我的习惯是单表内的数据处理优先用函数跨表汇总的复杂逻辑还是放应用层更合适。2. 日期函数时间处理的核心日期函数是我每天都会碰到的函数类别。业务系统里几乎每张表都有create_time、update_time报表统计、数据筛选、定时任务全都离不开日期计算。MySQL的日期函数设计得相当全面关键是你得知道什么场景用哪个。2.1 最常用的日期获取与格式化函数先过一遍最基础的NOW()返回当前日期和时间格式是YYYY-MM-DD HH:MM:SS。它取的是语句执行时刻的时间同一个事务内多次调用结果一致。CURDATE()只返回当前日期结果是YYYY-MM-DD。CURTIME()只返回当前时间结果是HH:MM:SS。DATE_FORMAT(date, format)把日期按指定格式输出这是用得最多的一个格式化函数。STR_TO_DATE(str, format)反过来把字符串解析成日期常用于导入外部数据时的字段转换。看几个实际执行结果SELECT NOW(); -- 2025-01-15 14:23:45 SELECT CURDATE(); -- 2025-01-15 SELECT CURTIME(); -- 14:23:45 SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); -- 2025-01-15 14:23:45 SELECT DATE_FORMAT(NOW(), %Y年%m月%d日); -- 2025年01月15日DATE_FORMAT的格式符是新手最容易记混的地方我把常用的整理一下格式符含义示例%Y四位年份2025%y两位年份25%m两位月份01%d两位日期15%H24小时制小时14%i分钟23%s秒45%W星期名Wednesday%M月份名JanuarySTR_TO_DATE的格式串必须跟输入字符串严格匹配它对不上就直接返回NULL不会报错。这个特性经常坑人数据查着查着发现少了实际上是转化失败被过滤了SELECT STR_TO_DATE(2025-01-15 14:30:00, %Y-%m-%d %H:%i:%s); -- 2025-01-15 14:30:00 SELECT STR_TO_DATE(2025/01/15, %Y-%m-%d); -- NULL因为字符串是斜杠分隔格式串却写了横杠2.2 日期运算与区间计算时间格式化只是基础真正常用的是日期运算加几天、减几个月、算两个日期间隔。这几个函数必须熟练DATE_ADD(date, INTERVAL expr unit)给日期加上指定时间间隔。DATE_SUB(date, INTERVAL expr unit)给日期减去指定时间间隔。DATEDIFF(date1, date2)返回两个日期相差的天数是date1减date2。TIMESTAMPDIFF(unit, datetime1, datetime2)返回两个时间在指定单位上的差值这里是datetime2减datetime1。INTERVAL支持的单位相当丰富DAY、WEEK、MONTH、QUARTER、YEAR、HOUR、MINUTE、SECOND都可以用。比如SELECT DATE_ADD(2025-01-15, INTERVAL 3 DAY); -- 2025-01-18 SELECT DATE_SUB(2025-01-15, INTERVAL 1 MONTH); -- 2024-12-15 SELECT DATEDIFF(2025-03-01, 2025-01-15); -- 45 SELECT TIMESTAMPDIFF(MONTH, 2024-10-01, 2025-01-15); -- 3这里要特别注意TIMESTAMPDIFF和DATEDIFF的单位差异。DATEDIFF只算天数TIMESTAMPDIFF可以按MONTH、YEAR算但两者的参数顺序相反一个是前减后一个是后减前。我写的时候经常搞混后来干脆记住TIMESTAMPDIFF后面那个减前面那个刚好和DATEDIFF相反。日期运算最常见的场景之一是计算用户年龄SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM users;这样算出来的年龄是按整年计的比你自己在程序里处理闰年、月份差要省心得多。2.3 日期函数实战按月分组统计订单说了这么多看一个完整的实战场景。假设orders表里有create_time和amount字段要统计最近6个月每月的订单数和销售额SQL怎么写SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders WHERE create_time DATE_SUB(CURDATE(), INTERVAL 6 MONTH) GROUP BY month ORDER BY month DESC;这里有两个细节值得展开。第一为什么用DATE_FORMAT而不是直接GROUP BY create_time。create_time是精确到秒的时间戳按它分组一个月会拆成几十万组显然不对。DATE_FORMAT把时间统一成YYYY-MM格式才能按月份归组。第二GROUP BY后面直接用了别名month。这是MySQL的一个扩展特性允许GROUP BY和ORDER BY引用SELECT子句里的别名。但要注意在ONLY_FULL_GROUP_BY模式下SELECT里的非聚合列必须出现在GROUP BY中写成GROUP BY month而month本身是表达式的结果在MySQL 5.7及以后版本默认开启的严格模式下是可以的但在某些其他数据库里不行。我早期写过一条老SQL用的是MySQL 5.5的宽松模式GROUP BY写得很随意。后来升级到MySQL 5.7默认SQL_MODE带上了ONLY_FULL_GROUP_BY那条SQL直接报错排查了半天才想起来是这个模式的问题。遇到这种情况要么把列全部加进GROUP BY要么改SQL_MODE但一般不建议改全局配置调SQL更靠谱。3. 字符串函数文本处理工具箱字符串函数是我在数据清洗场景里最依赖的东西。导入的Excel数据格式乱七八糟手机号带空格、姓名有全角字符、地址字段里混着换行符全靠字符串函数一条条清理。这一节把常用的挨个过一遍每个都配上实际用法。3.1 拼接、截取与替换先看最常用的几个CONCAT(str1, str2, ...)拼接字符串有任何一个参数是NULL结果就是NULL。CONCAT_WS(sep, str1, str2, ...)带分隔符的拼接第一个参数是分隔符会跳过NULL值。SUBSTRING(str, pos, len)从指定位置截取字符串注意下标从1开始不是从0开始。SUBSTRING_INDEX(str, delimiter, count)按分隔符截取count为正数取左边第几个负数取右边第几个。REPLACE(str, from_str, to_str)替换字符串中的子串。LEFT(str, len)和RIGHT(str, len)从左边或右边截取固定长度。代码示例SELECT CONCAT(Hello, , World); -- Hello World SELECT CONCAT_WS(-, 2025, 01, 15); -- 2025-01-15 SELECT SUBSTRING(hello world, 7, 5); -- world SELECT LEFT(hello world, 5); -- hello SELECT RIGHT(hello world, 5); -- world SELECT SUBSTRING_INDEX(技术,生活,随笔, ,, 1); -- 技术 SELECT SUBSTRING_INDEX(技术,生活,随笔, ,, -1); -- 随笔SUBSTRING从1开始计数这一条对从Python、JavaScript转来的同学是个典型的坑。Python里字符串从0开始写习惯了直接套过来算出来的结果永远差一位。我建议在本地跑个测试确认结果再往正式SQL里写。CONCAT和CONCAT_WS的区别值得多说一句。CONCAT碰到NULL直接返回NULL比如用户表里last_name有值、first_name是NULLCONCAT(last_name, first_name)结果整个变成NULL。而CONCAT_WS会跳过NULL在拼接用户姓名时用CONCAT_WS更安全SELECT CONCAT_WS( , last_name, first_name) AS full_name FROM users;REPLACE最经典的场景是手机号脱敏。把中间四位替换成星号SELECT REPLACE(13812345678, SUBSTRING(13812345678, 4, 4), ****); -- 138****56783.2 长度、大小写与去空格这类函数看着简单但每次用都能遇到边界情况LENGTH(str)返回字符串的字节数UTF-8下中文占3个字节。CHAR_LENGTH(str)返回字符串的字符数中文算1个字符。UPPER(str)和LOWER(str)转大写和小写。TRIM(str)去掉首尾空格默认只去空格。LTRIM(str)、RTRIM(str)只去左侧或只去右侧空格。LPAD(str, len, padstr)、RPAD(str, len, padstr)用指定字符串填充到指定长度。LENGTH和CHAR_LENGTH的区别必须搞清楚。用UTF-8编码的MySQL里LENGTH(中文)返回6CHAR_LENGTH(中文)返回2。如果你要判断用户输入的密码长度是否达到8位用CHAR_LENGTH才是符合直觉的判断。用LENGTH的话8个中文字符会被误判为24个字符。LPAD也特别实用。比如订单号是自增数字要生成固定6位的编号SELECT LPAD(123, 6, 0); -- 000123再比如带计数值的排行榜名次拼接SELECT RPAD(CONCAT(rank, . , user_name), 20, ) FROM ranking;TRIM有个扩展用法很多人不知道它可以去掉指定字符不只是空格SELECT TRIM(BOTH x FROM xxxhelloxxx); -- hello SELECT TRIM(LEADING 0 FROM 00123); -- 123LEADING、TRAILING、BOTH三个关键字分别控制去掉开头、结尾、两边。这在清洗格式化编号时很常用有些导入数据里编号前补了零要去掉补零就用TRIM(LEADING 0 FROM code)。3.3 字符串函数实战清洗手机号和地址数据实际业务里字符串函数经常组合使用。举个例子用户表里的phone字段混入了空格、横线、86前缀要把它们统一清洗成纯11位数字UPDATE users SET phone REPLACE(REPLACE(REPLACE( TRIM(phone), , ), -, ), 86, ) WHERE phone LIKE % % OR phone LIKE %-% OR phone LIKE 86%;从里到外逐层剥离先去首尾空格再去中间空格再去横线最后去掉86前缀。这里注意REPLACE嵌套顺序必须先处理空格再处理横线如果顺序反了横线处理完又冒出来空格的可能性不大但按依赖关系排总是稳妥的。再举一个地址拆分的场景。tags字段存的是逗号分隔的标签比如技术,生活,随笔要取第一个标签分组统计SELECT SUBSTRING_INDEX(tags, ,, 1) AS main_tag, COUNT(*) FROM articles GROUP BY main_tag;SUBSTRING_INDEX还有一层巧妙用法取最后一个分隔符之后的内容。写-1就是从右往左取第一段比如拿到一个文件路径要取文件名SELECT SUBSTRING_INDEX(/var/log/mysql/error.log, /, -1); -- error.log这个用法比先算长度再截取要干净得多。4. 数学函数数值计算的基石数学函数在业务代码里用得没有日期和字符串频繁但一旦用上就是关键节点。尤其是金额计算、百分比统计、随机抽样这几个场景用错函数会让数据直接出错而且很难一眼看出来。4.1 取整与四舍五入取整相关的函数是重灾区因为不同函数的行为差异很大ROUND(x, d)四舍五入保留d位小数。d不写默认取整到个位。CEIL(x)/CEILING(x)向上取整返回大于等于x的最小整数。FLOOR(x)向下取整返回小于等于x的最大整数。TRUNCATE(x, d)直接截断保留d位小数不四舍五入。标准示例SELECT ROUND(3.456, 2); -- 3.46 SELECT ROUND(3.5); -- 4 SELECT CEIL(3.01); -- 4 SELECT FLOOR(3.99); -- 3 SELECT TRUNCATE(3.456, 2); -- 3.45CEIL和FLOOR的差异在分页计算里表现得很明显。假设每页显示10条数据总共27条你要算页数用CEIL(27/10)得到3页用FLOOR(27/10)得到2页少了最后一页。TRUNCATE和ROUND容易混淆记住一点TRUNCATE是直接砍掉后面的位数不做任何进位判断。如果做账务对账必须用ROUND保持四舍五入的一致性。这里必须提一个ROUND的经典坑在MySQL中ROUND(2.5)的结果是3ROUND(3.5)的结果是4没毛病。但ROUND(2.675, 2)的结果很可能是2.67而不是2.68。原因是2.675在计算机二进制浮点表示里其实是一个略小于2.675的数四舍五入后掉到了2.67。这不是MySQL的bug是所有浮点数的通病。做金额计算尽量用DECIMAL类型传进ROUND不要用FLOAT/DOUBLE。4.2 绝对值、符号与随机数这类函数不多但个个都有典型使用场景ABS(x)绝对值计算误差范围时常用。SIGN(x)返回-1、0、1对应负数、零、正数。MOD(x, y)取模运算等价于x % y。RAND()返回0到1之间的随机数每次调用结果不同。RAND(seed)传入固定种子返回固定的随机数序列用于测试复现。示例SELECT ABS(-10); -- 10 SELECT SIGN(-5); -- -1 SELECT MOD(10, 3); -- 1 SELECT RAND(); -- 0.134567890123456每次都不一样 SELECT RAND(42); -- 固定种子下每次返回同一个数SIGN在业务里用得很少但做数据对账时很有用。比如对比两个库的金额差SIGN(a - b)可以直接判断谁大谁小省得写一堆CASE WHEN。RAND最常见的用法是随机抽样但这里有个大坑ORDER BY RAND()在表数据量大时性能极差。它需要对全表每一行生成随机数然后做排序文件数据量一旦过百万查询能卡到让你怀疑服务器是不是挂了。更好的随机取一条记录方案是利用主键范围SELECT * FROM users WHERE id (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users))) LIMIT 1;这个方案走的是主键索引不用全表排序速度提升明显。当然它有个小缺陷如果id有空洞抽到的概率会略有不均但对非严格的随机场景完全够用。4.3 数值运算的精度陷阱精度问题值得单独拿出来说。MySQL里的数值类型分几档整数类型的INT/BIGINT近似小数的FLOAT/DOUBLE精确小数的DECIMAL。FLOAT和DOUBLE是二进制浮点内部用近似方式存储某些十进制小数没法精确表达。经典的例子是SELECT 0.1 0.2; -- 0.30000000000000004这在MySQL里可能显示0.3但做等值比较时WHERE price 0.3可能匹配不到预期的行因为浮点数底层不是精确的0.3。DECIMAL则是以字符串方式存储的精确小数运算结果不会丢精度。业务里涉及金额、税率、单价一律用DECIMAL。比如订单金额字段定义成DECIMAL(10, 2)运算不管怎么算结果都精确到分。再比如百分比计算SUM(amount) / SUM(total) * 100结果可能是一长串小数用ROUND包一层并指定小数位数SELECT ROUND(SUM(paid_amount) / SUM(total_amount) * 100, 2) AS paid_rate FROM orders;注意运算顺序先乘再除还是先除再乘会带来精度差异。一般先算比例再乘100能保留更多有效位数。5. 其他常用函数流程控制与类型转换日期、字符串、数学是三个大类但日常SQL里还有几类函数出镜率也很高流程控制、类型转换、加密和聚合。这些函数单独看都不难但组合起来能写出相当灵活的统计SQL。5.1 流程控制函数SQL里也能写if-else靠的是这三个函数IF(expr, if_true, if_false)简单二分支判断。IFNULL(expr1, expr2)expr1为NULL时返回expr2否则返回expr1。CASE WHEN...THEN...ELSE...END多分支判断可以替代IF的嵌套写法。IF的用法SELECT name, score, IF(score 60, 及格, 不及格) AS result FROM exam_scores;CASE WHEN适合多分支场景SELECT name, score, CASE WHEN score 90 THEN 优秀 WHEN score 60 THEN 及格 ELSE 不及格 END AS level FROM exam_scores;这两个写法改造成统计逻辑也很方便。比如把用户按年龄分桶统计SELECT CASE WHEN age 18 THEN 未成年 WHEN age 30 THEN 青年 WHEN age 60 THEN 中年 ELSE 老年 END AS age_group, COUNT(*) FROM users GROUP BY age_group;IFNULL是最常用的空值兜底函数报表里尤其多SELECT id, IFNULL(user_name, 未知用户) AS user_name FROM user_info;注意IFNULL只接受两个参数如果你有多个字段依次取第一个非NULL的需求用COALESCE更合适。5.2 类型转换与比较函数类型转换在数据集成场景里天天见。导入的数据往往是字符串要参与计算必须转成数字CAST(expr AS type)标准类型转换语法。CONVERT(expr, type)功能和CAST类似。CONVERT(expr USING charset)用于字符集转换这是CAST做不到的。COALESCE(val1, val2, ...)返回参数列表中第一个非NULL的值。NULLIF(expr1, expr2)两个参数相等时返回NULL否则返回expr1。示例SELECT CAST(123 AS UNSIGNED) 1; -- 124 SELECT CAST(2025-01-15 AS DATE); -- 2025-01-15 SELECT CONVERT(中文 USING utf8mb4); -- 中文转成utf8mb4编码CAST的坑在于把非数字字符串转成数字时在严格SQL模式下会直接报错。比如CAST(abc AS UNSIGNED)非严格模式下返回0严格模式下直接抛异常。所以ETL场景里转之前最好先做一层清洗或者用REGEXP判断一下是不是纯数字。NULLIF的典型使用场景是防除零SELECT SUM(amount) / NULLIF(COUNT(DISTINCT user_id), 0) FROM orders;如果去重用户数为0NULLIF返回NULL整个除法结果是NULL而不是报错。你可以在应用层判断空值并给出友好提示。5.3 加密与聚合函数加密函数在数据脱敏和密码存储场景会用到。MD5(str)生成32位十六进制校验和。SHA1(str)生成40位十六进制校验和。SHA2(str, hash_len)更安全的SHA2系列hash_len支持224、256、384、512。SELECT MD5(password123); -- 482c811da5d5b4bc6d497ffa98491e38这里要说一句重要的MD5和SHA1都已经不是安全加密算法了主要用于文件校验、请求签名、数据去重。真正的用户密码存储不建议在数据库层做应该用应用层加盐哈希方案比如bcrypt、scrypt。数据库层做密码加密一旦泄露没加盐的哈希表很容易被彩虹表打穿。聚合函数是报表统计的主力每个都很常用COUNT(*)统计所有行数。COUNT(column)统计某列非NULL的行数。SUM(column)求和。AVG(column)求平均值。MAX(column)、MIN(column)最大最小值。GROUP_CONCAT(column)把分组内的值拼成一个字符串。COUNT()和COUNT(column)的差异要拎清楚。COUNT()统计行数不管列里有没有NULLCOUNT(column)只统计该列非NULL的行。统计用户数量用COUNT(*)统计填写了手机号的用户数量用COUNT(phone)。GROUP_CONCAT的灵活度很高可以自定义分隔符、排序方式SELECT department_id, GROUP_CONCAT(user_name ORDER BY user_id SEPARATOR ,) AS user_list FROM employees GROUP BY department_id;这个函数在做标签聚合、关联列表展示时特别好用一条SQL就把分组的明细拼成一个字符串省得应用层再循环拼接。6. 常见问题与排查技巧函数用多了踩坑是必然的。这一节我把实际开发里遇到的问题整理成速查内容既有报错排查也有性能避坑还有几条独家经验。6.1 函数使用高频报错整理报错信息或异常现象常见原因解决方案SQL语法错误 near xxx函数名拼错、括号不匹配、引号不成对逐个检查参数括号本地先跑一遍查询结果莫名变少STR_TO_DATE、CAST转换失败返回NULL先单独跑转换函数用IS NULL筛选排查ONLY_FULL_GROUP_BY相关报错GROUP BY没包含SELECT里的非聚合列补齐GROUP BY字段或用ANY_VALUE包一层中文字符统计长度不对误用LENGTH按字节数统计了换成CHAR_LENGTH金额对账差几分钱用了FLOAT/DOUBLE存金额统一改DECIMAL计算时用ROUND包裹随机抽取超时用了ORDER BY RAND()改成基于主键的随机区间方案举一个我真实排查过的案例。某次报表按月统计订单量发现某月数据明显偏少。我一开始以为是订单确实少后来对比订单明细才发现当月有一部分订单的create_time字段格式异常进入了脏数据区。因为SQL里用了STR_TO_DATE预处理时间异常数据被转成NULL后WHERE条件直接过滤掉了。排查方法是拆解SQL先查原始数据SELECT create_time FROM orders WHERE create_time LIKE %/%看到有斜杠格式的日期再单独跑STR_TO_DATE验证结果确实是NULL。最终在写入端做了格式统一报表恢复正常。这种事最好的避免方式就是写函数前先看看原始数据长什么样别想当然。6.2 函数导致索引失效的问题这是性能层面最大的坑。MySQL的B树索引存储的是原始列值一旦你在WHERE条件里对索引列应用了函数优化器就无法利用索引做范围扫描只能退化成全表扫描。最典型的错误写法-- 这条SQL无法使用create_time上的索引 SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2025-01-15;正确的写法是转成范围条件SELECT * FROM orders WHERE create_time 2025-01-15 00:00:00 AND create_time 2025-01-16 00:00:00;范围写法能让优化器直接走索引尤其是在create_time数据量特别大的时候性能差距可能是几十倍。同理对字符串列做LENGTH判断、对数值列做ABS运算只要在索引列上套了函数索引都会失效。我有个习惯性检查写完WHERE条件后扫一眼索引列上有没有函数包裹有就重构写法。如果你确实需要按日期格式化结果来过滤又希望走索引MySQL 5.7以上的版本可以用生成列Generated Column解决。先给表加一个虚拟列存格式化结果再在这个虚拟列上建索引ALTER TABLE orders ADD COLUMN create_month VARCHAR(7) GENERATED ALWAYS AS (DATE_FORMAT(create_time, %Y-%m)) STORED; CREATE INDEX idx_create_month ON orders(create_month);之后直接按create_month过滤索引就能用上。这个方案适合固定格式的查询灵活度不高但性能确实稳。6.3 版本差异与兼容性注意MySQL升级带来的函数行为变化是老项目最容易踩的雷。MySQL 5.7到8.0的升级有几个点值得注意。首先是默认SQL_MODE变了ONLY_FULL_GROUP_BY默认开启老的宽松GROUP BY SQL直接报错。其次是字符集默认从utf8升级为utf8mb4LENGTH()这类函数按字节统计的结果会变。再就是8.0移除了一些老函数和别名比如PASSWORD()函数被去掉了如果你还在用老语法升级前就要排查。还有一个容易被忽略的点SQL_MODE里的严格模式会影响函数对非法输入的处理。非严格模式下CAST(abc AS UNSIGNED)返回0严格模式下直接报错。同一个SQL在不同模式的库上跑出不同结果这是很多环境差异问题的根源。务实的建议是你维护一套函数测试用例。把常用的日期函数、字符串函数、数学函数各写一条查询跑一遍记录结果移数据库或升级版本时先跑测试用例比上线后查数据错误要省事得多。写在最后我在实际项目里的体会是日期函数和字符串函数几乎每天都在用报表统计、数据清洗、接口联调都离不开数学函数频率稍低但金额计算和随机抽样的场景一次用错就能造成大麻烦。把函数体系学清楚之后写复杂SQL的思路会开阔很多有些统计需求你第一反应会变成这能不能用一条SQL搞定而不是闷头写代码。最后分享一个小技巧遇到不熟的函数先在本地MySQL跑一跑看看返回结果别急着往生产环境写。尤其是STR_TO_DATE、CAST、ROUND这些边界情况多的函数提前验证一遍能省很多事。MySQL也提供了SELECT version;可以查看版本不同版本之间函数行为有差异本地验证时留意版本号。
MySQL内置函数详解：日期、字符串、数学函数实战指南
做后端开发尤其是每天跟数据打交道的几乎离不开SQL。写SQL的时候最烦什么不是join查不出来而是数据处理那点破事——日期要格式化、字符串要拼接、数值要取整你要是全靠应用层代码去搞逻辑绕一大圈不说性能还浪费。MySQL内置函数就是干这个用的把常见的日期计算、字符串处理、数学运算、流程判断直接下沉到SQL层面一条语句解决问题。这篇文章不聊高深理论就把MySQL内置函数系统地过一遍日期函数、字符串函数、数学函数以及其他常用函数。我会结合真实开发场景来聊每个函数都给出可复现的案例包括参数踩坑和版本注意事项。适合刚接触MySQL的初学者也适合想系统梳理一遍函数体系的开发同学。看完了你至少能知道遇到时间区间统计怎么写、字符串截取怎么不踩下标坑、金额计算怎么避免精度翻车。1. 内置函数概览为什么你该系统掌握这些函数MySQL内置函数是一组预定义好的功能模块你只需要调用函数名、传对参数就能拿到处理后的结果。它覆盖了数据操作的大部分高频场景分类清晰语法统一用好了能省掉大量应用层代码。1.1 MySQL内置函数分类一览函数按用途大致可以分成以下几类我先把最常用的列出来函数分类代表函数高频应用场景日期函数NOW、DATE_FORMAT、DATEDIFF、DATE_ADD时间格式化、时间区间统计、年龄计算字符串函数CONCAT、SUBSTRING、REPLACE、TRIM字段拼接、文本截取、脏数据清洗数学函数ROUND、CEIL、FLOOR、ABS金额取整、分页随机、数值精度处理流程控制函数IF、IFNULL、CASE WHEN业务逻辑分支、空值兜底类型转换函数CAST、CONVERT、COALESCE字符串转数字、字符集转换聚合函数COUNT、SUM、AVG、GROUP_CONCAT统计报表、分组汇总加密函数MD5、SHA2、AES_ENCRYPT数据脱敏、校验和计算实际开发中日期、字符串、数学这三类用得最频繁后两类属于关键时刻能救命的那种。就像你工具箱里的螺丝刀可能不是每天用但真需要的时候没有它你就得拿牙咬。1.2 为什么优先用内置函数而不是程序处理很多人有个习惯SQL只负责查询所有处理逻辑都丢给Java、Python、Go。这种思路在数据量小的时候没啥问题但一旦数据量上来问题就明显了。第一是性能差距。你写一条SQL从数据库拉出上万行原始数据再在应用层循环处理跟直接在SQL里用函数处理完只返回结果数据库网络传输的数据量差了几个量级。数据库本身的函数是编译好的执行效率远高于你应用层的循环。第二是逻辑统一。如果多个服务都依赖同一套处理逻辑比如时间格式化方式、字符串脱敏规则放在SQL层写死所有调用方拿到的结果是一致的。要是各端自己实现格式对不上就是线上事故。第三是开发效率。一条SQL能完成的事没必要写十几行代码。举个最典型的例子统计最近7天每天订单量应用层方案是先查出所有订单再在内存里写循环按天分组SQL方案就是一行GROUP BY加一个日期函数的事语句短、逻辑直观还不用写完代码再写单元测试。当然也要提醒一句内置函数不是越多越好。过于复杂的逻辑塞进SQL里可读性会急剧下降而且后续不好维护。我的习惯是单表内的数据处理优先用函数跨表汇总的复杂逻辑还是放应用层更合适。2. 日期函数时间处理的核心日期函数是我每天都会碰到的函数类别。业务系统里几乎每张表都有create_time、update_time报表统计、数据筛选、定时任务全都离不开日期计算。MySQL的日期函数设计得相当全面关键是你得知道什么场景用哪个。2.1 最常用的日期获取与格式化函数先过一遍最基础的NOW()返回当前日期和时间格式是YYYY-MM-DD HH:MM:SS。它取的是语句执行时刻的时间同一个事务内多次调用结果一致。CURDATE()只返回当前日期结果是YYYY-MM-DD。CURTIME()只返回当前时间结果是HH:MM:SS。DATE_FORMAT(date, format)把日期按指定格式输出这是用得最多的一个格式化函数。STR_TO_DATE(str, format)反过来把字符串解析成日期常用于导入外部数据时的字段转换。看几个实际执行结果SELECT NOW(); -- 2025-01-15 14:23:45 SELECT CURDATE(); -- 2025-01-15 SELECT CURTIME(); -- 14:23:45 SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); -- 2025-01-15 14:23:45 SELECT DATE_FORMAT(NOW(), %Y年%m月%d日); -- 2025年01月15日DATE_FORMAT的格式符是新手最容易记混的地方我把常用的整理一下格式符含义示例%Y四位年份2025%y两位年份25%m两位月份01%d两位日期15%H24小时制小时14%i分钟23%s秒45%W星期名Wednesday%M月份名JanuarySTR_TO_DATE的格式串必须跟输入字符串严格匹配它对不上就直接返回NULL不会报错。这个特性经常坑人数据查着查着发现少了实际上是转化失败被过滤了SELECT STR_TO_DATE(2025-01-15 14:30:00, %Y-%m-%d %H:%i:%s); -- 2025-01-15 14:30:00 SELECT STR_TO_DATE(2025/01/15, %Y-%m-%d); -- NULL因为字符串是斜杠分隔格式串却写了横杠2.2 日期运算与区间计算时间格式化只是基础真正常用的是日期运算加几天、减几个月、算两个日期间隔。这几个函数必须熟练DATE_ADD(date, INTERVAL expr unit)给日期加上指定时间间隔。DATE_SUB(date, INTERVAL expr unit)给日期减去指定时间间隔。DATEDIFF(date1, date2)返回两个日期相差的天数是date1减date2。TIMESTAMPDIFF(unit, datetime1, datetime2)返回两个时间在指定单位上的差值这里是datetime2减datetime1。INTERVAL支持的单位相当丰富DAY、WEEK、MONTH、QUARTER、YEAR、HOUR、MINUTE、SECOND都可以用。比如SELECT DATE_ADD(2025-01-15, INTERVAL 3 DAY); -- 2025-01-18 SELECT DATE_SUB(2025-01-15, INTERVAL 1 MONTH); -- 2024-12-15 SELECT DATEDIFF(2025-03-01, 2025-01-15); -- 45 SELECT TIMESTAMPDIFF(MONTH, 2024-10-01, 2025-01-15); -- 3这里要特别注意TIMESTAMPDIFF和DATEDIFF的单位差异。DATEDIFF只算天数TIMESTAMPDIFF可以按MONTH、YEAR算但两者的参数顺序相反一个是前减后一个是后减前。我写的时候经常搞混后来干脆记住TIMESTAMPDIFF后面那个减前面那个刚好和DATEDIFF相反。日期运算最常见的场景之一是计算用户年龄SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM users;这样算出来的年龄是按整年计的比你自己在程序里处理闰年、月份差要省心得多。2.3 日期函数实战按月分组统计订单说了这么多看一个完整的实战场景。假设orders表里有create_time和amount字段要统计最近6个月每月的订单数和销售额SQL怎么写SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders WHERE create_time DATE_SUB(CURDATE(), INTERVAL 6 MONTH) GROUP BY month ORDER BY month DESC;这里有两个细节值得展开。第一为什么用DATE_FORMAT而不是直接GROUP BY create_time。create_time是精确到秒的时间戳按它分组一个月会拆成几十万组显然不对。DATE_FORMAT把时间统一成YYYY-MM格式才能按月份归组。第二GROUP BY后面直接用了别名month。这是MySQL的一个扩展特性允许GROUP BY和ORDER BY引用SELECT子句里的别名。但要注意在ONLY_FULL_GROUP_BY模式下SELECT里的非聚合列必须出现在GROUP BY中写成GROUP BY month而month本身是表达式的结果在MySQL 5.7及以后版本默认开启的严格模式下是可以的但在某些其他数据库里不行。我早期写过一条老SQL用的是MySQL 5.5的宽松模式GROUP BY写得很随意。后来升级到MySQL 5.7默认SQL_MODE带上了ONLY_FULL_GROUP_BY那条SQL直接报错排查了半天才想起来是这个模式的问题。遇到这种情况要么把列全部加进GROUP BY要么改SQL_MODE但一般不建议改全局配置调SQL更靠谱。3. 字符串函数文本处理工具箱字符串函数是我在数据清洗场景里最依赖的东西。导入的Excel数据格式乱七八糟手机号带空格、姓名有全角字符、地址字段里混着换行符全靠字符串函数一条条清理。这一节把常用的挨个过一遍每个都配上实际用法。3.1 拼接、截取与替换先看最常用的几个CONCAT(str1, str2, ...)拼接字符串有任何一个参数是NULL结果就是NULL。CONCAT_WS(sep, str1, str2, ...)带分隔符的拼接第一个参数是分隔符会跳过NULL值。SUBSTRING(str, pos, len)从指定位置截取字符串注意下标从1开始不是从0开始。SUBSTRING_INDEX(str, delimiter, count)按分隔符截取count为正数取左边第几个负数取右边第几个。REPLACE(str, from_str, to_str)替换字符串中的子串。LEFT(str, len)和RIGHT(str, len)从左边或右边截取固定长度。代码示例SELECT CONCAT(Hello, , World); -- Hello World SELECT CONCAT_WS(-, 2025, 01, 15); -- 2025-01-15 SELECT SUBSTRING(hello world, 7, 5); -- world SELECT LEFT(hello world, 5); -- hello SELECT RIGHT(hello world, 5); -- world SELECT SUBSTRING_INDEX(技术,生活,随笔, ,, 1); -- 技术 SELECT SUBSTRING_INDEX(技术,生活,随笔, ,, -1); -- 随笔SUBSTRING从1开始计数这一条对从Python、JavaScript转来的同学是个典型的坑。Python里字符串从0开始写习惯了直接套过来算出来的结果永远差一位。我建议在本地跑个测试确认结果再往正式SQL里写。CONCAT和CONCAT_WS的区别值得多说一句。CONCAT碰到NULL直接返回NULL比如用户表里last_name有值、first_name是NULLCONCAT(last_name, first_name)结果整个变成NULL。而CONCAT_WS会跳过NULL在拼接用户姓名时用CONCAT_WS更安全SELECT CONCAT_WS( , last_name, first_name) AS full_name FROM users;REPLACE最经典的场景是手机号脱敏。把中间四位替换成星号SELECT REPLACE(13812345678, SUBSTRING(13812345678, 4, 4), ****); -- 138****56783.2 长度、大小写与去空格这类函数看着简单但每次用都能遇到边界情况LENGTH(str)返回字符串的字节数UTF-8下中文占3个字节。CHAR_LENGTH(str)返回字符串的字符数中文算1个字符。UPPER(str)和LOWER(str)转大写和小写。TRIM(str)去掉首尾空格默认只去空格。LTRIM(str)、RTRIM(str)只去左侧或只去右侧空格。LPAD(str, len, padstr)、RPAD(str, len, padstr)用指定字符串填充到指定长度。LENGTH和CHAR_LENGTH的区别必须搞清楚。用UTF-8编码的MySQL里LENGTH(中文)返回6CHAR_LENGTH(中文)返回2。如果你要判断用户输入的密码长度是否达到8位用CHAR_LENGTH才是符合直觉的判断。用LENGTH的话8个中文字符会被误判为24个字符。LPAD也特别实用。比如订单号是自增数字要生成固定6位的编号SELECT LPAD(123, 6, 0); -- 000123再比如带计数值的排行榜名次拼接SELECT RPAD(CONCAT(rank, . , user_name), 20, ) FROM ranking;TRIM有个扩展用法很多人不知道它可以去掉指定字符不只是空格SELECT TRIM(BOTH x FROM xxxhelloxxx); -- hello SELECT TRIM(LEADING 0 FROM 00123); -- 123LEADING、TRAILING、BOTH三个关键字分别控制去掉开头、结尾、两边。这在清洗格式化编号时很常用有些导入数据里编号前补了零要去掉补零就用TRIM(LEADING 0 FROM code)。3.3 字符串函数实战清洗手机号和地址数据实际业务里字符串函数经常组合使用。举个例子用户表里的phone字段混入了空格、横线、86前缀要把它们统一清洗成纯11位数字UPDATE users SET phone REPLACE(REPLACE(REPLACE( TRIM(phone), , ), -, ), 86, ) WHERE phone LIKE % % OR phone LIKE %-% OR phone LIKE 86%;从里到外逐层剥离先去首尾空格再去中间空格再去横线最后去掉86前缀。这里注意REPLACE嵌套顺序必须先处理空格再处理横线如果顺序反了横线处理完又冒出来空格的可能性不大但按依赖关系排总是稳妥的。再举一个地址拆分的场景。tags字段存的是逗号分隔的标签比如技术,生活,随笔要取第一个标签分组统计SELECT SUBSTRING_INDEX(tags, ,, 1) AS main_tag, COUNT(*) FROM articles GROUP BY main_tag;SUBSTRING_INDEX还有一层巧妙用法取最后一个分隔符之后的内容。写-1就是从右往左取第一段比如拿到一个文件路径要取文件名SELECT SUBSTRING_INDEX(/var/log/mysql/error.log, /, -1); -- error.log这个用法比先算长度再截取要干净得多。4. 数学函数数值计算的基石数学函数在业务代码里用得没有日期和字符串频繁但一旦用上就是关键节点。尤其是金额计算、百分比统计、随机抽样这几个场景用错函数会让数据直接出错而且很难一眼看出来。4.1 取整与四舍五入取整相关的函数是重灾区因为不同函数的行为差异很大ROUND(x, d)四舍五入保留d位小数。d不写默认取整到个位。CEIL(x)/CEILING(x)向上取整返回大于等于x的最小整数。FLOOR(x)向下取整返回小于等于x的最大整数。TRUNCATE(x, d)直接截断保留d位小数不四舍五入。标准示例SELECT ROUND(3.456, 2); -- 3.46 SELECT ROUND(3.5); -- 4 SELECT CEIL(3.01); -- 4 SELECT FLOOR(3.99); -- 3 SELECT TRUNCATE(3.456, 2); -- 3.45CEIL和FLOOR的差异在分页计算里表现得很明显。假设每页显示10条数据总共27条你要算页数用CEIL(27/10)得到3页用FLOOR(27/10)得到2页少了最后一页。TRUNCATE和ROUND容易混淆记住一点TRUNCATE是直接砍掉后面的位数不做任何进位判断。如果做账务对账必须用ROUND保持四舍五入的一致性。这里必须提一个ROUND的经典坑在MySQL中ROUND(2.5)的结果是3ROUND(3.5)的结果是4没毛病。但ROUND(2.675, 2)的结果很可能是2.67而不是2.68。原因是2.675在计算机二进制浮点表示里其实是一个略小于2.675的数四舍五入后掉到了2.67。这不是MySQL的bug是所有浮点数的通病。做金额计算尽量用DECIMAL类型传进ROUND不要用FLOAT/DOUBLE。4.2 绝对值、符号与随机数这类函数不多但个个都有典型使用场景ABS(x)绝对值计算误差范围时常用。SIGN(x)返回-1、0、1对应负数、零、正数。MOD(x, y)取模运算等价于x % y。RAND()返回0到1之间的随机数每次调用结果不同。RAND(seed)传入固定种子返回固定的随机数序列用于测试复现。示例SELECT ABS(-10); -- 10 SELECT SIGN(-5); -- -1 SELECT MOD(10, 3); -- 1 SELECT RAND(); -- 0.134567890123456每次都不一样 SELECT RAND(42); -- 固定种子下每次返回同一个数SIGN在业务里用得很少但做数据对账时很有用。比如对比两个库的金额差SIGN(a - b)可以直接判断谁大谁小省得写一堆CASE WHEN。RAND最常见的用法是随机抽样但这里有个大坑ORDER BY RAND()在表数据量大时性能极差。它需要对全表每一行生成随机数然后做排序文件数据量一旦过百万查询能卡到让你怀疑服务器是不是挂了。更好的随机取一条记录方案是利用主键范围SELECT * FROM users WHERE id (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users))) LIMIT 1;这个方案走的是主键索引不用全表排序速度提升明显。当然它有个小缺陷如果id有空洞抽到的概率会略有不均但对非严格的随机场景完全够用。4.3 数值运算的精度陷阱精度问题值得单独拿出来说。MySQL里的数值类型分几档整数类型的INT/BIGINT近似小数的FLOAT/DOUBLE精确小数的DECIMAL。FLOAT和DOUBLE是二进制浮点内部用近似方式存储某些十进制小数没法精确表达。经典的例子是SELECT 0.1 0.2; -- 0.30000000000000004这在MySQL里可能显示0.3但做等值比较时WHERE price 0.3可能匹配不到预期的行因为浮点数底层不是精确的0.3。DECIMAL则是以字符串方式存储的精确小数运算结果不会丢精度。业务里涉及金额、税率、单价一律用DECIMAL。比如订单金额字段定义成DECIMAL(10, 2)运算不管怎么算结果都精确到分。再比如百分比计算SUM(amount) / SUM(total) * 100结果可能是一长串小数用ROUND包一层并指定小数位数SELECT ROUND(SUM(paid_amount) / SUM(total_amount) * 100, 2) AS paid_rate FROM orders;注意运算顺序先乘再除还是先除再乘会带来精度差异。一般先算比例再乘100能保留更多有效位数。5. 其他常用函数流程控制与类型转换日期、字符串、数学是三个大类但日常SQL里还有几类函数出镜率也很高流程控制、类型转换、加密和聚合。这些函数单独看都不难但组合起来能写出相当灵活的统计SQL。5.1 流程控制函数SQL里也能写if-else靠的是这三个函数IF(expr, if_true, if_false)简单二分支判断。IFNULL(expr1, expr2)expr1为NULL时返回expr2否则返回expr1。CASE WHEN...THEN...ELSE...END多分支判断可以替代IF的嵌套写法。IF的用法SELECT name, score, IF(score 60, 及格, 不及格) AS result FROM exam_scores;CASE WHEN适合多分支场景SELECT name, score, CASE WHEN score 90 THEN 优秀 WHEN score 60 THEN 及格 ELSE 不及格 END AS level FROM exam_scores;这两个写法改造成统计逻辑也很方便。比如把用户按年龄分桶统计SELECT CASE WHEN age 18 THEN 未成年 WHEN age 30 THEN 青年 WHEN age 60 THEN 中年 ELSE 老年 END AS age_group, COUNT(*) FROM users GROUP BY age_group;IFNULL是最常用的空值兜底函数报表里尤其多SELECT id, IFNULL(user_name, 未知用户) AS user_name FROM user_info;注意IFNULL只接受两个参数如果你有多个字段依次取第一个非NULL的需求用COALESCE更合适。5.2 类型转换与比较函数类型转换在数据集成场景里天天见。导入的数据往往是字符串要参与计算必须转成数字CAST(expr AS type)标准类型转换语法。CONVERT(expr, type)功能和CAST类似。CONVERT(expr USING charset)用于字符集转换这是CAST做不到的。COALESCE(val1, val2, ...)返回参数列表中第一个非NULL的值。NULLIF(expr1, expr2)两个参数相等时返回NULL否则返回expr1。示例SELECT CAST(123 AS UNSIGNED) 1; -- 124 SELECT CAST(2025-01-15 AS DATE); -- 2025-01-15 SELECT CONVERT(中文 USING utf8mb4); -- 中文转成utf8mb4编码CAST的坑在于把非数字字符串转成数字时在严格SQL模式下会直接报错。比如CAST(abc AS UNSIGNED)非严格模式下返回0严格模式下直接抛异常。所以ETL场景里转之前最好先做一层清洗或者用REGEXP判断一下是不是纯数字。NULLIF的典型使用场景是防除零SELECT SUM(amount) / NULLIF(COUNT(DISTINCT user_id), 0) FROM orders;如果去重用户数为0NULLIF返回NULL整个除法结果是NULL而不是报错。你可以在应用层判断空值并给出友好提示。5.3 加密与聚合函数加密函数在数据脱敏和密码存储场景会用到。MD5(str)生成32位十六进制校验和。SHA1(str)生成40位十六进制校验和。SHA2(str, hash_len)更安全的SHA2系列hash_len支持224、256、384、512。SELECT MD5(password123); -- 482c811da5d5b4bc6d497ffa98491e38这里要说一句重要的MD5和SHA1都已经不是安全加密算法了主要用于文件校验、请求签名、数据去重。真正的用户密码存储不建议在数据库层做应该用应用层加盐哈希方案比如bcrypt、scrypt。数据库层做密码加密一旦泄露没加盐的哈希表很容易被彩虹表打穿。聚合函数是报表统计的主力每个都很常用COUNT(*)统计所有行数。COUNT(column)统计某列非NULL的行数。SUM(column)求和。AVG(column)求平均值。MAX(column)、MIN(column)最大最小值。GROUP_CONCAT(column)把分组内的值拼成一个字符串。COUNT()和COUNT(column)的差异要拎清楚。COUNT()统计行数不管列里有没有NULLCOUNT(column)只统计该列非NULL的行。统计用户数量用COUNT(*)统计填写了手机号的用户数量用COUNT(phone)。GROUP_CONCAT的灵活度很高可以自定义分隔符、排序方式SELECT department_id, GROUP_CONCAT(user_name ORDER BY user_id SEPARATOR ,) AS user_list FROM employees GROUP BY department_id;这个函数在做标签聚合、关联列表展示时特别好用一条SQL就把分组的明细拼成一个字符串省得应用层再循环拼接。6. 常见问题与排查技巧函数用多了踩坑是必然的。这一节我把实际开发里遇到的问题整理成速查内容既有报错排查也有性能避坑还有几条独家经验。6.1 函数使用高频报错整理报错信息或异常现象常见原因解决方案SQL语法错误 near xxx函数名拼错、括号不匹配、引号不成对逐个检查参数括号本地先跑一遍查询结果莫名变少STR_TO_DATE、CAST转换失败返回NULL先单独跑转换函数用IS NULL筛选排查ONLY_FULL_GROUP_BY相关报错GROUP BY没包含SELECT里的非聚合列补齐GROUP BY字段或用ANY_VALUE包一层中文字符统计长度不对误用LENGTH按字节数统计了换成CHAR_LENGTH金额对账差几分钱用了FLOAT/DOUBLE存金额统一改DECIMAL计算时用ROUND包裹随机抽取超时用了ORDER BY RAND()改成基于主键的随机区间方案举一个我真实排查过的案例。某次报表按月统计订单量发现某月数据明显偏少。我一开始以为是订单确实少后来对比订单明细才发现当月有一部分订单的create_time字段格式异常进入了脏数据区。因为SQL里用了STR_TO_DATE预处理时间异常数据被转成NULL后WHERE条件直接过滤掉了。排查方法是拆解SQL先查原始数据SELECT create_time FROM orders WHERE create_time LIKE %/%看到有斜杠格式的日期再单独跑STR_TO_DATE验证结果确实是NULL。最终在写入端做了格式统一报表恢复正常。这种事最好的避免方式就是写函数前先看看原始数据长什么样别想当然。6.2 函数导致索引失效的问题这是性能层面最大的坑。MySQL的B树索引存储的是原始列值一旦你在WHERE条件里对索引列应用了函数优化器就无法利用索引做范围扫描只能退化成全表扫描。最典型的错误写法-- 这条SQL无法使用create_time上的索引 SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2025-01-15;正确的写法是转成范围条件SELECT * FROM orders WHERE create_time 2025-01-15 00:00:00 AND create_time 2025-01-16 00:00:00;范围写法能让优化器直接走索引尤其是在create_time数据量特别大的时候性能差距可能是几十倍。同理对字符串列做LENGTH判断、对数值列做ABS运算只要在索引列上套了函数索引都会失效。我有个习惯性检查写完WHERE条件后扫一眼索引列上有没有函数包裹有就重构写法。如果你确实需要按日期格式化结果来过滤又希望走索引MySQL 5.7以上的版本可以用生成列Generated Column解决。先给表加一个虚拟列存格式化结果再在这个虚拟列上建索引ALTER TABLE orders ADD COLUMN create_month VARCHAR(7) GENERATED ALWAYS AS (DATE_FORMAT(create_time, %Y-%m)) STORED; CREATE INDEX idx_create_month ON orders(create_month);之后直接按create_month过滤索引就能用上。这个方案适合固定格式的查询灵活度不高但性能确实稳。6.3 版本差异与兼容性注意MySQL升级带来的函数行为变化是老项目最容易踩的雷。MySQL 5.7到8.0的升级有几个点值得注意。首先是默认SQL_MODE变了ONLY_FULL_GROUP_BY默认开启老的宽松GROUP BY SQL直接报错。其次是字符集默认从utf8升级为utf8mb4LENGTH()这类函数按字节统计的结果会变。再就是8.0移除了一些老函数和别名比如PASSWORD()函数被去掉了如果你还在用老语法升级前就要排查。还有一个容易被忽略的点SQL_MODE里的严格模式会影响函数对非法输入的处理。非严格模式下CAST(abc AS UNSIGNED)返回0严格模式下直接报错。同一个SQL在不同模式的库上跑出不同结果这是很多环境差异问题的根源。务实的建议是你维护一套函数测试用例。把常用的日期函数、字符串函数、数学函数各写一条查询跑一遍记录结果移数据库或升级版本时先跑测试用例比上线后查数据错误要省事得多。写在最后我在实际项目里的体会是日期函数和字符串函数几乎每天都在用报表统计、数据清洗、接口联调都离不开数学函数频率稍低但金额计算和随机抽样的场景一次用错就能造成大麻烦。把函数体系学清楚之后写复杂SQL的思路会开阔很多有些统计需求你第一反应会变成这能不能用一条SQL搞定而不是闷头写代码。最后分享一个小技巧遇到不熟的函数先在本地MySQL跑一跑看看返回结果别急着往生产环境写。尤其是STR_TO_DATE、CAST、ROUND这些边界情况多的函数提前验证一遍能省很多事。MySQL也提供了SELECT version;可以查看版本不同版本之间函数行为有差异本地验证时留意版本号。
