尧图网页设计 
上个月排查一个活动报表的慢查询表只有三十来万行索引建得也齐全可一条统计SQL跑了快六秒。拉开执行计划一看问题不在表结构而在一句WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-12-01。索引列被函数包了一圈优化器直接放弃走索引老老实实全表扫描。这种MySQL内置函数用得不当引发的性能问题我见过太多次。日期函数、字符串函数、数学函数这些看起来不起眼的小工具平时写CRUD用不上几个可真遇到统计报表、数据清洗、排序分页的场景用得好和用不好差别往往是数量级的。这篇把我在实际项目里常用的MySQL内置函数系统过一遍不按官方手册罗列而是按日常到底怎么用、有哪些坑来讲。每个函数都会给到典型用法和踩过的坑。适合刚入门想系统补基础的人也适合写了两三年SQL、但只会用几个常用函数的老手对照查漏。1. 先从一个慢查询说起函数用不好的代价1.1 一个DATE_FORMAT引发的全表扫描先说开头那个案例。原始需求很简单统计2024年12月1日这一天的订单量和销售额。第一版SQL是这么写的SELECT COUNT(*), SUM(amount) FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-12-01;逻辑上完全正确create_time 上也有普通索引但执行计划显示type: ALL扫了整张表。原因是MySQL对索引列做了函数运算之后索引失效了。优化器无法用B树直接按日期定位只能先把每行的 create_time 都格式化一遍再比对。同样的需求改成范围查询就能稳稳走索引SELECT COUNT(*), SUM(amount) FROM orders WHERE create_time 2024-12-01 00:00:00 AND create_time 2024-12-02 00:00:00;这个改写不是MySQL独有的技巧而是所有数据库通用的原则别让函数站在索引列那一边。后面讲字符串函数时还有类似的坑。1.2 内置函数家族全景MySQL内置函数按用途大概分这几类类别代表函数典型用途日期时间NOW、DATE_FORMAT、DATE_ADD、DATEDIFF时间格式化、日期运算、报表分组字符串CONCAT、SUBSTRING、REPLACE、TRIM拼接、截取、清洗、脱敏数学ROUND、CEIL、FLOOR、RAND取整、随机抽样、数值精度控制流程控制IF、IFNULL、NULLIF、CASE WHEN条件分支、空值兜底聚合COUNT、SUM、AVG、GROUP_CONCAT统计汇总、行转列加密散列MD5、SHA2、AES_ENCRYPT密码散列、数据签名信息类VERSION、DATABASE、LAST_INSERT_ID环境检查、自增主键回取类型转换CAST、CONVERT显式类型转换实际项目里80%的SQL也就用到前四类但聚合函数和数据清洗场景对字符串、日期函数的要求很高。接下来按类别拆开讲重点放在为什么这么用和边界情况返回什么。2. 日期时间函数格式转换、日期运算与时区细节日期函数是报表需求里躲不开的一类。按月分组、计算库存账龄、统计活跃天数全都依赖它。2.1 获取当前时间的四兄弟NOW、CURDATE、CURTIME、SYSDATESELECT NOW(), CURDATE(), CURTIME(), SYSDATE();返回结果大概这样NOW()2024-12-18 14:30:25日期时间都有CURDATE()2024-12-18只有日期CURTIME()14:30:25只有时间SYSDATE()看起来和 NOW() 一样实际有微妙区别NOW()取的是当前语句开始执行那一刻的时间不管这条SQL跑多久在同一批数据里 NOW() 都保持不变SYSDATE()是函数实际执行到那一刻的时间如果一条SQL里多次调用或者SQL执行时间很长SYSDATE()可能返回不同的值。在普通查询里这个差异可以忽略但在长事务、存储过程或者批量更新里这个差异会导致判断基准不一致。我写过一版库存脚本用了 SYSDATE() 判断超时时间结果批处理跑了两分钟后同一批货的最后操作时间居然不一样。排查半天才定位到是这个函数的问题。批量处理的场景统一用 NOW()别用 SYSDATE()。CURRENT_TIMESTAMP是NOW()的同义词CURRENT_DATE、CURRENT_TIME分别对应 CURDATE 和 CURTIME习惯写哪个都行。2.2 格式化与反向解析DATE_FORMAT、STR_TO_DATE这是报表分组最常用的组合。DATE_FORMAT(date, format)把日期时间按指定格式转成字符串SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); -- 2024-12-18 14:30:25 SELECT DATE_FORMAT(NOW(), %Y-%m); -- 2024-12 SELECT DATE_FORMAT(NOW(), %W); -- Wednesday常用格式符先列出来避免每次都翻手册格式符含义示例%Y四位年份2024%y两位年份24%m月份两位数12%c月份一位或两位12%d日两位数18%e日一位或两位18%H小时24小时制14%h小时12小时制02%i分钟30%s / %S秒25%W星期全名Wednesday%a星期缩写Wed%M月份全名December%b月份缩写Dec%j一年中的第几天353%pAM 或 PMPM按月份分组报表惯用写法是GROUP BY DATE_FORMAT(create_time, %Y-%m)。注意这里的 GROUP BY 直接用别名或者重复表达式都行。后面给完整例子。STR_TO_DATE(str, format)是反向操作把字符串解析成日期。ETL导入外部数据时特别有用比如收到的源文件里写的是2024/12/18 14:30标准格式存不进去SELECT STR_TO_DATE(2024/12/18 14:30, %Y/%m/%d %H:%i); -- 2024-12-18 14:30:00解析失败返回 NULL不会抛异常。所以大批量导入时先用一个 SELECT 验证格式是否匹配再执行 INSERT否则容易导入一批 NULL 进去还查不出来。2.3 日期运算DATE_ADD、DATEDIFF、TIMESTAMPDIFF日期加减用DATE_ADD和DATE_SUBSELECT DATE_ADD(2024-12-18, INTERVAL 1 MONTH); -- 2025-01-18 SELECT DATE_SUB(2024-12-18, INTERVAL 7 DAY); -- 2024-12-11INTERVAL后面可以跟 YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND也能拼写组合形式比如INTERVAL 1:30 HOUR_MINUTE但实际项目里用到复合单位的场景很少不用强行记。日期差计算有两个容易混淆的函数DATEDIFF和TIMESTAMPDIFF。SELECT DATEDIFF(2024-12-18, 2024-12-01); -- 17单位固定为天 SELECT TIMESTAMPDIFF(MONTH, 2024-12-01, 2025-02-01); -- 2单位可以指定DATEDIFF(expr1, expr2)返回 expr1 减 expr2 的天数参数只要日期部分时间部分忽略。TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)返回的是expr2 减 expr1方向跟 DATEDIFF 相反这个太容易搞反了。我习惯记法TIMESTAMPDIFF 是后面的减前面的。TIMESTAMPDIFF单位支持 MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR算账龄、算时长都靠它。比如统计用户注册到首单的天数SELECT user_id, TIMESTAMPDIFF(DAY, register_time, first_order_time) AS days_to_first_order FROM user_stat;还有一个PERIOD_DIFF(p1, p2)专门算两个年月字符串之间差几个月参数格式是 YYYYMM 或 YYMM 的数字注意不是日期SELECT PERIOD_DIFF(202502, 202412); -- 22.4 提取年月日周YEAR、MONTH、DAY、WEEK、QUARTER、LAST_DAY对于已经存在的日期列需要单独取年、月、日做统计时这些函数最直接SELECT YEAR(2024-12-18), MONTH(2024-12-18), DAY(2024-12-18); -- 2024, 12, 18 SELECT QUARTER(2024-12-18), WEEK(2024-12-18), DAYOFWEEK(2024-12-18); -- 4, 51, 4DAYOFWEEK返回的索引是 1Sunday 到 7Saturday跟国内习惯不一样。要按周一作为一周第一天统计用WEEKDAY它返回 0Monday 到 6Sunday。LAST_DAY(date)返回当月最后一天算月末截止时点很常用SELECT LAST_DAY(2024-02-15); -- 2024-02-29拿它配合日期加减可以快速构造上月末、本月初、下月初这些报表边界日期。2.5 Unix时间戳与不可忽略的时区问题UNIX_TIMESTAMP()把日期转成秒级时间戳FROM_UNIXTIME()反向转换SELECT UNIX_TIMESTAMP(2024-12-18 14:30:00); -- 具体的秒数 SELECT FROM_UNIXTIME(1734500000); -- 2024-12-18 14:13:20这里有个藏得很深的坑这两个函数的转换结果取决于当前会话的 time_zone。如果你在配置文件里把数据库连接time_zone设置成了00:00而 Java 应用跑在Asia/Shanghai那么存进去的时间戳在 MySQL 里给 FROM_UNIXTIME 一转换就会差8个小时。我处理过一个线上告警时间错乱的问题最后排查到是连接串上少配了serverTimezoneAsia/Shanghai应用层和数据库层各按各的时区理解时间戳。涉及时间戳转换先确认三层时区一致JVM时区、JDBC连接时区、MySQL会话时区。MySQL 8.0 下查看当前时区和会话时区SELECT global.time_zone, session.time_zone;2.6 日期函数使用时的性能红线再强调一遍开头那个教训用日期函数时优先保证索引列保持原样。下面这几种写法都会让索引失效WHERE YEAR(create_time) 2024 WHERE MONTH(create_time) 12 WHERE DATE(create_time) 2024-12-01改写方向是把条件变成范围WHERE create_time 2024-12-01 AND create_time 2024-12-02 WHERE create_time 2024-12-01 00:00:00 AND create_time 2025-01-01 00:00:00年份统计就拼一个年初到明年初的范围。别嫌啰嗦对千万级表来说这决定了查询是毫秒级还是秒级。3. 字符串函数从拼接拆分到清洗脱敏的实用技巧字符串函数在日常需求里出现频率最高。写接口、做报表、清理脏数据到处都能碰上。3.1 拼接与分隔CONCAT、CONCAT_WSCONCAT(str1, str2, ...)是最基础的拼接函数SELECT CONCAT(订单, 编号, 1001); -- 订单编号1001注意一个小陷阱CONCAT里任何一个参数为 NULL整个结果就是 NULL。数据清洗时经常会遇到这个情况——某个字段为空拼接结果整个消失。SELECT CONCAT(用户ID:, NULL, 结束); -- NULL所以常用CONCAT_WSWith Separator来处理。它在参数之间加分隔符同时会自动跳过 NULLSELECT CONCAT_WS(-, 2024, 12, 18); -- 2024-12-18 SELECT CONCAT_WS(-, 2024, NULL, 18); -- 2024-18注意 CONCAT_WS 跳过的是 NULL不是空字符串。空字符串仍然会拼进去。3.2 GROUP_CONCAT分组内字符串聚合严格来说 GROUP_CONCAT 是聚合函数但它的返回值是字符串归到字符串这一节更好理解。它把同一分组内的多行内容拼成一行SELECT category, GROUP_CONCAT(product_name SEPARATOR 、) FROM products GROUP BY category;执行结果类似这样category | GROUP_CONCAT(product_name) --------------------------------------- 电子产品 | 手机、电脑、耳机 图书 | 小说、历史、科普有几个实用细节SEPARATOR不指定时默认用逗号。可以在拼接时排序GROUP_CONCAT(product_name ORDER BY price DESC SEPARATOR 、)。可以去重GROUP_CONCAT(DISTINCT tag ORDER BY tag SEPARATOR |)。最大长度默认 1024 字节拼接内容多时会被静默截断。需要加大时SET SESSION group_concat_max_len 1048576;遇到过导出商品标签功能的同事拼出来总少一截怎么查都查不到原因最后就是栽在这个默认长度上。动态 SQL 里如果拼的内容可能很长每条连接会话都要设置这个变量可以在连接池初始化时统一执行。3.3 截取函数SUBSTRING、LEFT、RIGHT、SUBSTRING_INDEXSUBSTRING(str, pos, len)从指定位置截取SELECT SUBSTRING(hello world, 3, 5); -- llo wMySQL 的字符串位置从 1 开始。pos可以传负数表示从尾部倒数第几个位置开始截取SELECT SUBSTRING(hello world, -5, 3); -- worLEFT(str, n)和RIGHT(str, n)分别从左侧和右侧截取 n 个字符SELECT LEFT(订单号20241218, 3); -- 订单号 SELECT RIGHT(订单号20241218, 4); -- 1218SUBSTRING_INDEX(str, delim, count)是处理分隔字符串的王牌函数。它在字符串中查找分隔符返回第 count 次出现分隔符之前或之后的子串SELECT SUBSTRING_INDEX(a,b,c,d, ,, 2); -- a,b SELECT SUBSTRING_INDEX(a,b,c,d, ,, -2); -- c,dcount 为正数返回从左往右数到第 count 个分隔符的左侧内容count 为负数返回从右往左数到第 |count| 个分隔符的右侧内容。用它可以实现简单的拆分。比如有一个包含多个标签的字段存的是教育,科技,生活要取第一个标签SELECT SUBSTRING_INDEX(tags, ,, 1) FROM article;还能配合嵌套取中间段。比如要取第二个标签SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ,, 2), ,, -1) FROM article;这个嵌套写法在数据清洗里常见值得记下来。3.4 查找定位LOCATE、INSTR、POSITION、FIELD判断一个字符串里是否包含某段内容用LOCATE或者INSTRSELECT LOCATE(world, hello world); -- 7找不到返回0 SELECT INSTR(hello world, world); -- 7 SELECT POSITION(world IN hello world); -- 7LOCATE(substr, str, pos)还可以指定从第几位开始找。三个函数语义基本一致写哪个都行习惯用 LOCATE 的多一些。用法上最常见的场景是包含条件。要查所有包含旗舰店的店铺名SELECT * FROM shop WHERE LOCATE(旗舰店, shop_name) 0;注意这个写法同样会放弃索引和LIKE %旗舰店%一样。需要加速时考虑全文本索引或者其他方案。FIELD(value, val1, val2, ...)返回 value 在参数列表里的位置从 1 开始不在列表里返回 0。它最实用的场景是自定义排序优先级SELECT order_status, COUNT(*) FROM orders GROUP BY order_status ORDER BY FIELD(order_status, 已完成, 处理中, 待支付);这样可以把已完成排到最前面而不是默认的字母序。3.5 长度、大小写与空白CHAR_LENGTH 和 LENGTH 的字节陷阱CHAR_LENGTH(str)返回字符数LENGTH(str)返回字节数。在 utf8mb4 字符集下一个中文占3个字节一个 emoji 占4个字节SELECT CHAR_LENGTH(你好), LENGTH(你好); -- 2, 6 SELECT CHAR_LENGTH(hello), LENGTH(hello); -- 5, 5之前有个同事做字段长度校验页面提示最多10个字符他写了个WHERE LENGTH(name) 10来判断超长结果用户输入了4个汉字就报错了因为 LENGTH 算出来是 12。校验用户输入的字符个数统一用CHAR_LENGTH。大小写转换函数是UPPER(str)/LOWER(str)别名UCASE/LCASESELECT UPPER(mysql), LOWER(MySQL); -- MYSQL, mysql注意它在非英文字符上有边界行为比如德语的 ß 转大写可能是 SS。不过国内项目基本碰不到这类问题。去空格有三件套TRIM(str)去掉首尾空格LTRIM(str)去左侧RTRIM(str)去右侧。TRIM还能指定去除字符SELECT TRIM( abc ); -- abc SELECT TRIM(LEADING 0 FROM 007123); -- 7123实际项目里RTRIM很常用因为 CHAR 类型补全空格、人工录入多打空格这类脏数据都要靠它洗一遍。清洗时建议用TRIM后再更新回字段否则排序和去重都会出问题。3.6 替换与脱敏REPLACE、LPAD、RPADREPLACE(str, from, to)做全量替换SELECT REPLACE(13812345678, 138, 139); -- 13912345678注意 REPLACE 会把所有匹配都替换掉不像 Java 里的 replaceFirst。需求要只换第一个出现时可以配合 SUBSTRING_INDEX 自己组装。LPAD(str, len, padstr)和RPAD(str, len, padstr)按指定长度填充SELECT LPAD(7, 4, 0); -- 0007 SELECT RPAD(LEFT(13812345678, 3), 11, *); -- 138********第二个例子是手机号中间脱敏的常见写法取前3位再右边补8个星号效果就是138********。更标准的脱敏是保留前3后4SELECT CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) FROM user;这类脱敏 SQL 在测试环境刷数据时特别实用后面实战部分再给完整例子。3.7 字符串函数导致的索引失效和日期函数一样在索引列上套字符串函数同样会废掉索引。最典型的两个WHERE LEFT(phone, 3) 138 WHERE SUBSTRING(name, 1, 2) 张能改写的话尽量改写成范围或前缀匹配。LIKE 138%在 phone 列有索引时是可以走索引的只有通配符在中间或末尾的情况才会失效WHERE phone LIKE 138% -- 可以走索引 WHERE phone LIKE %138 -- 无法走索引 WHERE phone LIKE %138% -- 无法走索引4. 数学函数取整、随机数与计算结果精度控制数学函数在业务SQL里相对配角但用的地方都很关键尤其是取整和随机抽样踩坑概率非常高。4.1 取整四兄弟ROUND、CEIL、FLOOR、TRUNCATE四个函数的差异SELECT ROUND(2.5), CEIL(2.1), FLOOR(2.9), TRUNCATE(2.999, 2); -- 3, 3, 2, 2.99函数行为示例ROUND(x, d)四舍五入d 为小数位数ROUND(2.5) 3CEIL(x)向上取整CEIL(2.1) 3FLOOR(x)向下取整FLOOR(2.9) 2TRUNCATE(x, d)直接截断不做四舍五入TRUNCATE(2.999, 2) 2.99ROUND(2.45, 1)返回 2.5这是常规理解。但要注意浮点数的经典坑由于 IEEE 754 表示误差ROUND(1.005, 2)的结果不是 1.01而是 1.00。这不是MySQL的bug是几乎所有编程语言和数据库用二进制浮点数算小数都会遇到的事。涉及金额、税率、百分比这类对精度有要求的数据不要在SQL里用浮点数做四舍五入用 DECIMAL 类型字段运算或者干脆在应用层计算。TRUNCATE(x, d)的小数位数 d 还可以传负数表示在小数点左侧截断SELECT TRUNCATE(1234.567, -2); -- 12004.2 随机数 RAND 与抽样的正确姿势RAND()返回 [0, 1) 范围内的随机浮点数。RAND(N)接收一个种子同一种子生成的序列完全一致这个特性可以用来复现问题SELECT RAND(), RAND(10), RAND(10); -- 首次执行 RAND() 随机RAND(10) 固定返回相同结果常见用法是随机抽样SELECT * FROM user ORDER BY RAND() LIMIT 5;这个写法在数据量小的时候没问题数据量一大就是灾难。ORDER BY RAND()会对每一行生成随机值再排序百万级表上这个排序开销不容小觑。数据量大时更高效的做法是随机出主键范围再取数。比如 id 大致连续SELECT * FROM user WHERE id (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM user))) ORDER BY id LIMIT 5;遇到 id 有空洞时可能取不到足够条数需要加上补查逻辑。实际抽奖、随机推荐场景建议把随机范围算好在应用层生成几个候选 id再用 IN 查询压力小很多。4.3 取余、整除、幂与开方MOD、DIV、POWER、SQRT取余MOD(x, y)和取模运算符%等价SELECT MOD(10, 3), 10 % 3; -- 1, 1DIV是整除返回结果的整数部分SELECT 10 DIV 3; -- 3POWER(x, y)做幂运算SQRT(x)开平方SELECT POWER(2, 10), SQRT(16); -- 1024, 4实际业务里 MOD 最常见的场景是分表路由、按余数分组采样。比如按用户ID分10张表路由规则基本都会用到user_id % 10或者MOD(user_id, 10)。4.4 千分位格式化FORMATFORMAT(x, d)把数字格式化为带千分位分隔符的字符串保留 d 位小数SELECT FORMAT(1234567.891, 2); -- 1,234,567.89注意返回值是字符串不是数字。前端展示金额时可以直接用它拼出友好的展示文案但后续要做数值运算时得先转回数字否则拿一个带逗号的字符串去加减乘除结果会有问题。另外有个容易忽略的细节FORMAT 的第二个参数 d 代表小数位数它同时会做四舍五入。FORMAT(2.345, 2)返回2.35但同样受浮点数精度影响对精度敏感的仍然建议先转 DECIMAL。4.5 三角函数与常数用的少但不该没听说过日常业务SQL里极少直接用 SIN、COS、TAN。但PI()和角度弧度转换RADIANS()/DEGREES()在地理坐标计算、可视化开发里会碰到。SELECT PI(); -- 3.141593 SELECT DEGREES(PI() / 2); -- 90在涉及地图围栏、坐标距离估算时偶尔会用到球面距离公式其中就会涉及弧度转换。这种场景建议把计算放到应用层SQL里只负责查数据不然查询语句又长又难测试。5. 流程控制与杂项函数查询里的语法糖这部分函数让SQL从取数工具变成带逻辑的处理工具做好分支判断、空值兜底和类型转换。5.1 IF、IFNULL、NULLIF 与 CASE WHENIF(expr, true_value, false_value)是简化的三元表达式SELECT user_name, IF(status 1, 启用, 禁用) AS status_name FROM user;IFNULL(expr1, expr2)是空值兜底函数expr1 为 NULL 时返回 expr2否则返回 expr1SELECT user_name, IFNULL(nickname, user_name) AS display_name FROM user;NULLIF(expr1, expr2)反过来两个参数相等时返回 NULL不相等时返回 expr1。它最经典的用法是防除零SELECT amount / NULLIF(quantity, 0) AS avg_price FROM order_detail;当 quantity 为 0 时NULLIF(quantity, 0)返回 NULL整个除法结果为 NULL不会报错。外面再套一层 IFNULL就能把结果变成 0 或其他兜底值SELECT IFNULL(amount / NULLIF(quantity, 0), 0) AS avg_price FROM order_detail;多条件分支用CASE WHENSELECT product_name, CASE WHEN stock 0 THEN 无货 WHEN stock 10 THEN 库存紧张 ELSE 库存充足 END AS stock_status FROM product;CASE WHEN 还能在聚合里做条件统计。统计订单里支付成功和取消的数量SELECT SUM(CASE WHEN status 1 THEN 1 ELSE 0 END) AS paid_count, SUM(CASE WHEN status 3 THEN 1 ELSE 0 END) AS cancelled_count FROM orders;这种写法比多次查同一张表高效得多。5.2 聚合函数的细节COUNT、SUM、AVG、MAX、MIN聚合函数是报表的基石但每个都有容易被忽略的细节。COUNT(*)统计行数包含 NULL 的行COUNT(column)统计该列非 NULL 的行数。以前流传COUNT(1) 比 COUNT() 快的说法在 MySQL 8.0 的 InnoDB 引擎下两者没有性能差异放心用 COUNT()。COUNT(DISTINCT column)做精确去重统计数据量大时很慢可以借助COUNT(DISTINCT column)配合IF实现按条件去重SELECT COUNT(DISTINCT user_id) AS all_users, COUNT(DISTINCT IF(order_cnt 0, user_id, NULL)) AS active_users FROM user_stat;SUM(column)忽略 NULL不会因为某行是 NULL 就把整个结果变 NULL。AVG(column)也是忽略 NULL这里有一个反直觉的点AVG 不是每个分组的总值除以全组记录数而是除以非 NULL 值的个数。需要按全组行数平均时要手动算SUM(column) / COUNT(*)。MAX和MIN对字符串也能用按字符序比较。实际使用中更要注意的是MySQL 8.0 的优化器支持MAX(column)利用索引跳过大量数据但前提是条件里不要把索引列包上函数。5.3 加密与散列MD5、SHA2、AES_ENCRYPTMySQL 内置了常用散列函数SELECT MD5(abc); -- 900150983cd24fb0d6963f7d28e17f72 SELECT SHA2(abc, 256); -- 64位十六进制字符串 SELECT SHA1(abc); -- 40位十六进制字符串MD5 早已不适合存高安全级别的密码但它在业务里仍然很常用比如生成接口签名、对文本做指纹去重。前两年我处理内容去重需求就是先对正文做 MD5再对指纹建唯一索引几十万条数据秒级去重。SHA2 第二个参数支持 224、256、384、512其中 256 最常用。线上系统如果老接口用的是 MD5新老系统对接时需要明确是统一用哪种散列不然后端对不上签名就抓瞎。AES_ENCRYPT/AES_DECRYPT是对称加密和散列不同可以解密回来。它的用法-- 加密 SELECT AES_ENCRYPT(敏感内容, encrypt_key); -- 解密 SELECT CAST(AES_DECRYPT(encrypted_col, encrypt_key) AS CHAR) FROM secret_table;AES_ENCRYPT 返回二进制串直接存字段会造成乱码一般先 HEX() 再存。密钥管理是另一个大话题这里只提醒一句密钥不要硬编码进SQL脚本更不要出现在日志里。生产环境密钥一般从配置中心拉取到应用层由应用层做加密后入库避免数据库里明文密钥。5.4 信息函数VERSION、DATABASE、USER、LAST_INSERT_ID排查环境问题时常用的SELECT VERSION(); -- 8.0.36 SELECT DATABASE(); -- 当前库名 SELECT USER(), CURRENT_USER(); -- 当前连接用户 SELECT CONNECTION_ID(); -- 当前连接IDLAST_INSERT_ID()返回当前连接上一条 INSERT 产生的自增ID注意三个要点它绑定的是当前会话连接不是全局。别的连接插入的数据它感知不到。没有新插入时再次调用仍然返回最近一次插入的ID可能造成误判。一次插入多行时只返回第一行的自增ID。组合插入主从表数据时会用到。插入订单主表拿到 order_id再插入明细表INSERT INTO orders(user_id, amount) VALUES (1001, 99.90); -- 假设这是第一条新插入得到 order_id 10086 INSERT INTO order_detail(order_id, product_name, price) VALUES (LAST_INSERT_ID(), 商品A, 99.90);注意多行插入时LAST_INSERT_ID()返回的是第一条数据的ID如果需要每一行的ID得在应用层解析或者改写循环插入。5.5 类型转换CAST、CONVERT显式类型转换用CAST(expr AS type)SELECT CAST(123 AS SIGNED); -- 123 SELECT CAST(2024-12-18 AS DATE); -- 2024-12-18 SELECT CAST(123 AS CHAR); -- 123CONVERT(expr, type)作用相同CONVERT还可以转字符集SELECT CONVERT(name USING utf8mb4) FROM old_table;转换时的失败行为要看情况字符串转数字时遇到非数字字符只取前缀可识别部分。CAST(123abc AS SIGNED)返回 123CAST(abc123 AS SIGNED)返回 0不会报错。这正是数据清洗时需要警惕的静默转换不会告诉你数据有问题导入前最好先用 REGEXP 校验格式。5.6 其他实用函数COALESCE、GREATEST、LEAST、UUIDCOALESCE(expr1, expr2, ...)返回参数列表中第一个非 NULL 值相当于多个字段间取首个有值SELECT COALESCE(mobile, phone, 无联系方式) FROM contact;GREATEST(a, b, c)返回最大值LEAST(a, b, c)返回最小值多个列横向比较时好用SELECT GREATEST(price1, price2, price3) AS max_price FROM product;UUID()生成一个标准 UUID 字符串SELECT UUID(); -- 0a3dd514-1a34-11ef-9ed2-0242ac110002可以作为不依赖自增ID的业务主键。但 UUID 作为主键在 InnoDB 里会引发随机插入造成页分裂和碎片性能敏感的大表不建议直接用字符串 UUID 做主键可以考虑 UUID 转成二进制或者改用雪花ID。6. 三个业务场景的函数组合实战单独记函数不如看组合。最后用三个实际需求把常用函数串一遍。6.1 月度订单报表日期函数加聚合函数需求统计最近6个月每个月的订单量、销售额并计算环比增长率。WITH monthly_stats AS ( SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE create_time DATE_SUB(DATE_FORMAT(CURDATE(), %Y-%m-01), INTERVAL 5 MONTH) GROUP BY DATE_FORMAT(create_time, %Y-%m) ) SELECT month, order_cnt, total_amount, LAG(order_cnt, 1) OVER (ORDER BY month) AS prev_cnt, ROUND( (order_cnt - LAG(order_cnt, 1) OVER (ORDER BY month)) / NULLIF(LAG(order_cnt, 1) OVER (ORDER BY month), 0) * 100, 2 ) AS growth_rate FROM monthly_stats ORDER BY month;这里用到了多个前面讲过的点DATE_FORMAT做月份分组DATE_SUB和DATE_FORMAT(CURDATE(), %Y-%m-01)构造6个月前的月初日期LAG窗口函数取上一行数据NULLIF防止上个月订单量为0时除零报错ROUND控制百分比小数位如果数据库是 MySQL 5.7没有窗口函数LAG这段可以改成本月数据 LEFT JOIN 上月数据或者用标量子查询实现。6.2 用户手机号脱敏字符串函数组合需求把测试环境里所有 user 表的手机号处理成脱敏形式保留前3后4。UPDATE user SET phone CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) WHERE phone REGEXP ^1[3-9][0-9]{9}$;先判断手机号格式是否合法再脱敏避免把脏数据也原样处理进去。执行前先 SELECT 预览SELECT phone AS original_phone, CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) AS masked_phone FROM user LIMIT 10;这种做法在刷测试库、脱敏导出场景里很常见。如果还需要生成不重复的手机号可以在脱敏后再用CONCAT拼上随机后缀但要注意保证不违反手机号格式校验逻辑。6.3 分类商品聚合行转列GROUP_CONCAT需求把每个分类下的商品名拼成一行按价格升序排列。SELECT category, COUNT(*) AS product_count, GROUP_CONCAT(product_name ORDER BY price ASC SEPARATOR 、) AS product_list FROM products GROUP BY category;再配合 SUBSTRING_INDEX 取列表里的第一个商品SELECT category, SUBSTRING_INDEX(GROUP_CONCAT(product_name ORDER BY price ASC SEPARATOR 、), 、, 1) AS cheapest_product FROM products GROUP BY category;在这个需求里GROUP_CONCAT 的排序、去重、分隔符设置、长度限制就全部用上了。如果拼接结果超过 1024 字节记得先加大 group_concat_max_len。6.4 字符串数字混排问题ORDER BY 的小坑需求对编号字段排序但编号是P-1、P-2、P-10这种带前缀的字符串。直接ORDER BY code会得到字典序P-1 P-10 P-2因为10和1比较时先比第一位结果10 2。要按数字部分排可以SELECT * FROM product ORDER BY CAST(SUBSTRING(code, 3) AS SIGNED);更粗暴但常见的写法是加0隐式转数字SELECT * FROM product ORDER BY SUBSTRING(code, 3) 0;这类写法能应付大多数简单场景但前提是截出来的部分确实都是数字否则转换结果会是0排序就不准了。整理完这一圈函数我最大的体会是函数本身不难记难的是知道每个函数在真实数据下的行为差异。建议你把常用函数写进一个测试SQL文件建一张临时表把 NULL、空字符串、特别大的整数、浮点数精度这些边界情况都跑一遍亲眼看看返回什么。我自己就是这么积累的比翻文档印象深得多。真到排查线上慢查询或者数据错乱的时候这些看起来基础的东西往往是破局的钥匙。 MySQL内置函数实战指南：从慢查询优化到常用函数避坑
上个月排查一个活动报表的慢查询表只有三十来万行索引建得也齐全可一条统计SQL跑了快六秒。拉开执行计划一看问题不在表结构而在一句WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-12-01。索引列被函数包了一圈优化器直接放弃走索引老老实实全表扫描。这种MySQL内置函数用得不当引发的性能问题我见过太多次。日期函数、字符串函数、数学函数这些看起来不起眼的小工具平时写CRUD用不上几个可真遇到统计报表、数据清洗、排序分页的场景用得好和用不好差别往往是数量级的。这篇把我在实际项目里常用的MySQL内置函数系统过一遍不按官方手册罗列而是按日常到底怎么用、有哪些坑来讲。每个函数都会给到典型用法和踩过的坑。适合刚入门想系统补基础的人也适合写了两三年SQL、但只会用几个常用函数的老手对照查漏。1. 先从一个慢查询说起函数用不好的代价1.1 一个DATE_FORMAT引发的全表扫描先说开头那个案例。原始需求很简单统计2024年12月1日这一天的订单量和销售额。第一版SQL是这么写的SELECT COUNT(*), SUM(amount) FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-12-01;逻辑上完全正确create_time 上也有普通索引但执行计划显示type: ALL扫了整张表。原因是MySQL对索引列做了函数运算之后索引失效了。优化器无法用B树直接按日期定位只能先把每行的 create_time 都格式化一遍再比对。同样的需求改成范围查询就能稳稳走索引SELECT COUNT(*), SUM(amount) FROM orders WHERE create_time 2024-12-01 00:00:00 AND create_time 2024-12-02 00:00:00;这个改写不是MySQL独有的技巧而是所有数据库通用的原则别让函数站在索引列那一边。后面讲字符串函数时还有类似的坑。1.2 内置函数家族全景MySQL内置函数按用途大概分这几类类别代表函数典型用途日期时间NOW、DATE_FORMAT、DATE_ADD、DATEDIFF时间格式化、日期运算、报表分组字符串CONCAT、SUBSTRING、REPLACE、TRIM拼接、截取、清洗、脱敏数学ROUND、CEIL、FLOOR、RAND取整、随机抽样、数值精度控制流程控制IF、IFNULL、NULLIF、CASE WHEN条件分支、空值兜底聚合COUNT、SUM、AVG、GROUP_CONCAT统计汇总、行转列加密散列MD5、SHA2、AES_ENCRYPT密码散列、数据签名信息类VERSION、DATABASE、LAST_INSERT_ID环境检查、自增主键回取类型转换CAST、CONVERT显式类型转换实际项目里80%的SQL也就用到前四类但聚合函数和数据清洗场景对字符串、日期函数的要求很高。接下来按类别拆开讲重点放在为什么这么用和边界情况返回什么。2. 日期时间函数格式转换、日期运算与时区细节日期函数是报表需求里躲不开的一类。按月分组、计算库存账龄、统计活跃天数全都依赖它。2.1 获取当前时间的四兄弟NOW、CURDATE、CURTIME、SYSDATESELECT NOW(), CURDATE(), CURTIME(), SYSDATE();返回结果大概这样NOW()2024-12-18 14:30:25日期时间都有CURDATE()2024-12-18只有日期CURTIME()14:30:25只有时间SYSDATE()看起来和 NOW() 一样实际有微妙区别NOW()取的是当前语句开始执行那一刻的时间不管这条SQL跑多久在同一批数据里 NOW() 都保持不变SYSDATE()是函数实际执行到那一刻的时间如果一条SQL里多次调用或者SQL执行时间很长SYSDATE()可能返回不同的值。在普通查询里这个差异可以忽略但在长事务、存储过程或者批量更新里这个差异会导致判断基准不一致。我写过一版库存脚本用了 SYSDATE() 判断超时时间结果批处理跑了两分钟后同一批货的最后操作时间居然不一样。排查半天才定位到是这个函数的问题。批量处理的场景统一用 NOW()别用 SYSDATE()。CURRENT_TIMESTAMP是NOW()的同义词CURRENT_DATE、CURRENT_TIME分别对应 CURDATE 和 CURTIME习惯写哪个都行。2.2 格式化与反向解析DATE_FORMAT、STR_TO_DATE这是报表分组最常用的组合。DATE_FORMAT(date, format)把日期时间按指定格式转成字符串SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); -- 2024-12-18 14:30:25 SELECT DATE_FORMAT(NOW(), %Y-%m); -- 2024-12 SELECT DATE_FORMAT(NOW(), %W); -- Wednesday常用格式符先列出来避免每次都翻手册格式符含义示例%Y四位年份2024%y两位年份24%m月份两位数12%c月份一位或两位12%d日两位数18%e日一位或两位18%H小时24小时制14%h小时12小时制02%i分钟30%s / %S秒25%W星期全名Wednesday%a星期缩写Wed%M月份全名December%b月份缩写Dec%j一年中的第几天353%pAM 或 PMPM按月份分组报表惯用写法是GROUP BY DATE_FORMAT(create_time, %Y-%m)。注意这里的 GROUP BY 直接用别名或者重复表达式都行。后面给完整例子。STR_TO_DATE(str, format)是反向操作把字符串解析成日期。ETL导入外部数据时特别有用比如收到的源文件里写的是2024/12/18 14:30标准格式存不进去SELECT STR_TO_DATE(2024/12/18 14:30, %Y/%m/%d %H:%i); -- 2024-12-18 14:30:00解析失败返回 NULL不会抛异常。所以大批量导入时先用一个 SELECT 验证格式是否匹配再执行 INSERT否则容易导入一批 NULL 进去还查不出来。2.3 日期运算DATE_ADD、DATEDIFF、TIMESTAMPDIFF日期加减用DATE_ADD和DATE_SUBSELECT DATE_ADD(2024-12-18, INTERVAL 1 MONTH); -- 2025-01-18 SELECT DATE_SUB(2024-12-18, INTERVAL 7 DAY); -- 2024-12-11INTERVAL后面可以跟 YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND也能拼写组合形式比如INTERVAL 1:30 HOUR_MINUTE但实际项目里用到复合单位的场景很少不用强行记。日期差计算有两个容易混淆的函数DATEDIFF和TIMESTAMPDIFF。SELECT DATEDIFF(2024-12-18, 2024-12-01); -- 17单位固定为天 SELECT TIMESTAMPDIFF(MONTH, 2024-12-01, 2025-02-01); -- 2单位可以指定DATEDIFF(expr1, expr2)返回 expr1 减 expr2 的天数参数只要日期部分时间部分忽略。TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)返回的是expr2 减 expr1方向跟 DATEDIFF 相反这个太容易搞反了。我习惯记法TIMESTAMPDIFF 是后面的减前面的。TIMESTAMPDIFF单位支持 MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR算账龄、算时长都靠它。比如统计用户注册到首单的天数SELECT user_id, TIMESTAMPDIFF(DAY, register_time, first_order_time) AS days_to_first_order FROM user_stat;还有一个PERIOD_DIFF(p1, p2)专门算两个年月字符串之间差几个月参数格式是 YYYYMM 或 YYMM 的数字注意不是日期SELECT PERIOD_DIFF(202502, 202412); -- 22.4 提取年月日周YEAR、MONTH、DAY、WEEK、QUARTER、LAST_DAY对于已经存在的日期列需要单独取年、月、日做统计时这些函数最直接SELECT YEAR(2024-12-18), MONTH(2024-12-18), DAY(2024-12-18); -- 2024, 12, 18 SELECT QUARTER(2024-12-18), WEEK(2024-12-18), DAYOFWEEK(2024-12-18); -- 4, 51, 4DAYOFWEEK返回的索引是 1Sunday 到 7Saturday跟国内习惯不一样。要按周一作为一周第一天统计用WEEKDAY它返回 0Monday 到 6Sunday。LAST_DAY(date)返回当月最后一天算月末截止时点很常用SELECT LAST_DAY(2024-02-15); -- 2024-02-29拿它配合日期加减可以快速构造上月末、本月初、下月初这些报表边界日期。2.5 Unix时间戳与不可忽略的时区问题UNIX_TIMESTAMP()把日期转成秒级时间戳FROM_UNIXTIME()反向转换SELECT UNIX_TIMESTAMP(2024-12-18 14:30:00); -- 具体的秒数 SELECT FROM_UNIXTIME(1734500000); -- 2024-12-18 14:13:20这里有个藏得很深的坑这两个函数的转换结果取决于当前会话的 time_zone。如果你在配置文件里把数据库连接time_zone设置成了00:00而 Java 应用跑在Asia/Shanghai那么存进去的时间戳在 MySQL 里给 FROM_UNIXTIME 一转换就会差8个小时。我处理过一个线上告警时间错乱的问题最后排查到是连接串上少配了serverTimezoneAsia/Shanghai应用层和数据库层各按各的时区理解时间戳。涉及时间戳转换先确认三层时区一致JVM时区、JDBC连接时区、MySQL会话时区。MySQL 8.0 下查看当前时区和会话时区SELECT global.time_zone, session.time_zone;2.6 日期函数使用时的性能红线再强调一遍开头那个教训用日期函数时优先保证索引列保持原样。下面这几种写法都会让索引失效WHERE YEAR(create_time) 2024 WHERE MONTH(create_time) 12 WHERE DATE(create_time) 2024-12-01改写方向是把条件变成范围WHERE create_time 2024-12-01 AND create_time 2024-12-02 WHERE create_time 2024-12-01 00:00:00 AND create_time 2025-01-01 00:00:00年份统计就拼一个年初到明年初的范围。别嫌啰嗦对千万级表来说这决定了查询是毫秒级还是秒级。3. 字符串函数从拼接拆分到清洗脱敏的实用技巧字符串函数在日常需求里出现频率最高。写接口、做报表、清理脏数据到处都能碰上。3.1 拼接与分隔CONCAT、CONCAT_WSCONCAT(str1, str2, ...)是最基础的拼接函数SELECT CONCAT(订单, 编号, 1001); -- 订单编号1001注意一个小陷阱CONCAT里任何一个参数为 NULL整个结果就是 NULL。数据清洗时经常会遇到这个情况——某个字段为空拼接结果整个消失。SELECT CONCAT(用户ID:, NULL, 结束); -- NULL所以常用CONCAT_WSWith Separator来处理。它在参数之间加分隔符同时会自动跳过 NULLSELECT CONCAT_WS(-, 2024, 12, 18); -- 2024-12-18 SELECT CONCAT_WS(-, 2024, NULL, 18); -- 2024-18注意 CONCAT_WS 跳过的是 NULL不是空字符串。空字符串仍然会拼进去。3.2 GROUP_CONCAT分组内字符串聚合严格来说 GROUP_CONCAT 是聚合函数但它的返回值是字符串归到字符串这一节更好理解。它把同一分组内的多行内容拼成一行SELECT category, GROUP_CONCAT(product_name SEPARATOR 、) FROM products GROUP BY category;执行结果类似这样category | GROUP_CONCAT(product_name) --------------------------------------- 电子产品 | 手机、电脑、耳机 图书 | 小说、历史、科普有几个实用细节SEPARATOR不指定时默认用逗号。可以在拼接时排序GROUP_CONCAT(product_name ORDER BY price DESC SEPARATOR 、)。可以去重GROUP_CONCAT(DISTINCT tag ORDER BY tag SEPARATOR |)。最大长度默认 1024 字节拼接内容多时会被静默截断。需要加大时SET SESSION group_concat_max_len 1048576;遇到过导出商品标签功能的同事拼出来总少一截怎么查都查不到原因最后就是栽在这个默认长度上。动态 SQL 里如果拼的内容可能很长每条连接会话都要设置这个变量可以在连接池初始化时统一执行。3.3 截取函数SUBSTRING、LEFT、RIGHT、SUBSTRING_INDEXSUBSTRING(str, pos, len)从指定位置截取SELECT SUBSTRING(hello world, 3, 5); -- llo wMySQL 的字符串位置从 1 开始。pos可以传负数表示从尾部倒数第几个位置开始截取SELECT SUBSTRING(hello world, -5, 3); -- worLEFT(str, n)和RIGHT(str, n)分别从左侧和右侧截取 n 个字符SELECT LEFT(订单号20241218, 3); -- 订单号 SELECT RIGHT(订单号20241218, 4); -- 1218SUBSTRING_INDEX(str, delim, count)是处理分隔字符串的王牌函数。它在字符串中查找分隔符返回第 count 次出现分隔符之前或之后的子串SELECT SUBSTRING_INDEX(a,b,c,d, ,, 2); -- a,b SELECT SUBSTRING_INDEX(a,b,c,d, ,, -2); -- c,dcount 为正数返回从左往右数到第 count 个分隔符的左侧内容count 为负数返回从右往左数到第 |count| 个分隔符的右侧内容。用它可以实现简单的拆分。比如有一个包含多个标签的字段存的是教育,科技,生活要取第一个标签SELECT SUBSTRING_INDEX(tags, ,, 1) FROM article;还能配合嵌套取中间段。比如要取第二个标签SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ,, 2), ,, -1) FROM article;这个嵌套写法在数据清洗里常见值得记下来。3.4 查找定位LOCATE、INSTR、POSITION、FIELD判断一个字符串里是否包含某段内容用LOCATE或者INSTRSELECT LOCATE(world, hello world); -- 7找不到返回0 SELECT INSTR(hello world, world); -- 7 SELECT POSITION(world IN hello world); -- 7LOCATE(substr, str, pos)还可以指定从第几位开始找。三个函数语义基本一致写哪个都行习惯用 LOCATE 的多一些。用法上最常见的场景是包含条件。要查所有包含旗舰店的店铺名SELECT * FROM shop WHERE LOCATE(旗舰店, shop_name) 0;注意这个写法同样会放弃索引和LIKE %旗舰店%一样。需要加速时考虑全文本索引或者其他方案。FIELD(value, val1, val2, ...)返回 value 在参数列表里的位置从 1 开始不在列表里返回 0。它最实用的场景是自定义排序优先级SELECT order_status, COUNT(*) FROM orders GROUP BY order_status ORDER BY FIELD(order_status, 已完成, 处理中, 待支付);这样可以把已完成排到最前面而不是默认的字母序。3.5 长度、大小写与空白CHAR_LENGTH 和 LENGTH 的字节陷阱CHAR_LENGTH(str)返回字符数LENGTH(str)返回字节数。在 utf8mb4 字符集下一个中文占3个字节一个 emoji 占4个字节SELECT CHAR_LENGTH(你好), LENGTH(你好); -- 2, 6 SELECT CHAR_LENGTH(hello), LENGTH(hello); -- 5, 5之前有个同事做字段长度校验页面提示最多10个字符他写了个WHERE LENGTH(name) 10来判断超长结果用户输入了4个汉字就报错了因为 LENGTH 算出来是 12。校验用户输入的字符个数统一用CHAR_LENGTH。大小写转换函数是UPPER(str)/LOWER(str)别名UCASE/LCASESELECT UPPER(mysql), LOWER(MySQL); -- MYSQL, mysql注意它在非英文字符上有边界行为比如德语的 ß 转大写可能是 SS。不过国内项目基本碰不到这类问题。去空格有三件套TRIM(str)去掉首尾空格LTRIM(str)去左侧RTRIM(str)去右侧。TRIM还能指定去除字符SELECT TRIM( abc ); -- abc SELECT TRIM(LEADING 0 FROM 007123); -- 7123实际项目里RTRIM很常用因为 CHAR 类型补全空格、人工录入多打空格这类脏数据都要靠它洗一遍。清洗时建议用TRIM后再更新回字段否则排序和去重都会出问题。3.6 替换与脱敏REPLACE、LPAD、RPADREPLACE(str, from, to)做全量替换SELECT REPLACE(13812345678, 138, 139); -- 13912345678注意 REPLACE 会把所有匹配都替换掉不像 Java 里的 replaceFirst。需求要只换第一个出现时可以配合 SUBSTRING_INDEX 自己组装。LPAD(str, len, padstr)和RPAD(str, len, padstr)按指定长度填充SELECT LPAD(7, 4, 0); -- 0007 SELECT RPAD(LEFT(13812345678, 3), 11, *); -- 138********第二个例子是手机号中间脱敏的常见写法取前3位再右边补8个星号效果就是138********。更标准的脱敏是保留前3后4SELECT CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) FROM user;这类脱敏 SQL 在测试环境刷数据时特别实用后面实战部分再给完整例子。3.7 字符串函数导致的索引失效和日期函数一样在索引列上套字符串函数同样会废掉索引。最典型的两个WHERE LEFT(phone, 3) 138 WHERE SUBSTRING(name, 1, 2) 张能改写的话尽量改写成范围或前缀匹配。LIKE 138%在 phone 列有索引时是可以走索引的只有通配符在中间或末尾的情况才会失效WHERE phone LIKE 138% -- 可以走索引 WHERE phone LIKE %138 -- 无法走索引 WHERE phone LIKE %138% -- 无法走索引4. 数学函数取整、随机数与计算结果精度控制数学函数在业务SQL里相对配角但用的地方都很关键尤其是取整和随机抽样踩坑概率非常高。4.1 取整四兄弟ROUND、CEIL、FLOOR、TRUNCATE四个函数的差异SELECT ROUND(2.5), CEIL(2.1), FLOOR(2.9), TRUNCATE(2.999, 2); -- 3, 3, 2, 2.99函数行为示例ROUND(x, d)四舍五入d 为小数位数ROUND(2.5) 3CEIL(x)向上取整CEIL(2.1) 3FLOOR(x)向下取整FLOOR(2.9) 2TRUNCATE(x, d)直接截断不做四舍五入TRUNCATE(2.999, 2) 2.99ROUND(2.45, 1)返回 2.5这是常规理解。但要注意浮点数的经典坑由于 IEEE 754 表示误差ROUND(1.005, 2)的结果不是 1.01而是 1.00。这不是MySQL的bug是几乎所有编程语言和数据库用二进制浮点数算小数都会遇到的事。涉及金额、税率、百分比这类对精度有要求的数据不要在SQL里用浮点数做四舍五入用 DECIMAL 类型字段运算或者干脆在应用层计算。TRUNCATE(x, d)的小数位数 d 还可以传负数表示在小数点左侧截断SELECT TRUNCATE(1234.567, -2); -- 12004.2 随机数 RAND 与抽样的正确姿势RAND()返回 [0, 1) 范围内的随机浮点数。RAND(N)接收一个种子同一种子生成的序列完全一致这个特性可以用来复现问题SELECT RAND(), RAND(10), RAND(10); -- 首次执行 RAND() 随机RAND(10) 固定返回相同结果常见用法是随机抽样SELECT * FROM user ORDER BY RAND() LIMIT 5;这个写法在数据量小的时候没问题数据量一大就是灾难。ORDER BY RAND()会对每一行生成随机值再排序百万级表上这个排序开销不容小觑。数据量大时更高效的做法是随机出主键范围再取数。比如 id 大致连续SELECT * FROM user WHERE id (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM user))) ORDER BY id LIMIT 5;遇到 id 有空洞时可能取不到足够条数需要加上补查逻辑。实际抽奖、随机推荐场景建议把随机范围算好在应用层生成几个候选 id再用 IN 查询压力小很多。4.3 取余、整除、幂与开方MOD、DIV、POWER、SQRT取余MOD(x, y)和取模运算符%等价SELECT MOD(10, 3), 10 % 3; -- 1, 1DIV是整除返回结果的整数部分SELECT 10 DIV 3; -- 3POWER(x, y)做幂运算SQRT(x)开平方SELECT POWER(2, 10), SQRT(16); -- 1024, 4实际业务里 MOD 最常见的场景是分表路由、按余数分组采样。比如按用户ID分10张表路由规则基本都会用到user_id % 10或者MOD(user_id, 10)。4.4 千分位格式化FORMATFORMAT(x, d)把数字格式化为带千分位分隔符的字符串保留 d 位小数SELECT FORMAT(1234567.891, 2); -- 1,234,567.89注意返回值是字符串不是数字。前端展示金额时可以直接用它拼出友好的展示文案但后续要做数值运算时得先转回数字否则拿一个带逗号的字符串去加减乘除结果会有问题。另外有个容易忽略的细节FORMAT 的第二个参数 d 代表小数位数它同时会做四舍五入。FORMAT(2.345, 2)返回2.35但同样受浮点数精度影响对精度敏感的仍然建议先转 DECIMAL。4.5 三角函数与常数用的少但不该没听说过日常业务SQL里极少直接用 SIN、COS、TAN。但PI()和角度弧度转换RADIANS()/DEGREES()在地理坐标计算、可视化开发里会碰到。SELECT PI(); -- 3.141593 SELECT DEGREES(PI() / 2); -- 90在涉及地图围栏、坐标距离估算时偶尔会用到球面距离公式其中就会涉及弧度转换。这种场景建议把计算放到应用层SQL里只负责查数据不然查询语句又长又难测试。5. 流程控制与杂项函数查询里的语法糖这部分函数让SQL从取数工具变成带逻辑的处理工具做好分支判断、空值兜底和类型转换。5.1 IF、IFNULL、NULLIF 与 CASE WHENIF(expr, true_value, false_value)是简化的三元表达式SELECT user_name, IF(status 1, 启用, 禁用) AS status_name FROM user;IFNULL(expr1, expr2)是空值兜底函数expr1 为 NULL 时返回 expr2否则返回 expr1SELECT user_name, IFNULL(nickname, user_name) AS display_name FROM user;NULLIF(expr1, expr2)反过来两个参数相等时返回 NULL不相等时返回 expr1。它最经典的用法是防除零SELECT amount / NULLIF(quantity, 0) AS avg_price FROM order_detail;当 quantity 为 0 时NULLIF(quantity, 0)返回 NULL整个除法结果为 NULL不会报错。外面再套一层 IFNULL就能把结果变成 0 或其他兜底值SELECT IFNULL(amount / NULLIF(quantity, 0), 0) AS avg_price FROM order_detail;多条件分支用CASE WHENSELECT product_name, CASE WHEN stock 0 THEN 无货 WHEN stock 10 THEN 库存紧张 ELSE 库存充足 END AS stock_status FROM product;CASE WHEN 还能在聚合里做条件统计。统计订单里支付成功和取消的数量SELECT SUM(CASE WHEN status 1 THEN 1 ELSE 0 END) AS paid_count, SUM(CASE WHEN status 3 THEN 1 ELSE 0 END) AS cancelled_count FROM orders;这种写法比多次查同一张表高效得多。5.2 聚合函数的细节COUNT、SUM、AVG、MAX、MIN聚合函数是报表的基石但每个都有容易被忽略的细节。COUNT(*)统计行数包含 NULL 的行COUNT(column)统计该列非 NULL 的行数。以前流传COUNT(1) 比 COUNT() 快的说法在 MySQL 8.0 的 InnoDB 引擎下两者没有性能差异放心用 COUNT()。COUNT(DISTINCT column)做精确去重统计数据量大时很慢可以借助COUNT(DISTINCT column)配合IF实现按条件去重SELECT COUNT(DISTINCT user_id) AS all_users, COUNT(DISTINCT IF(order_cnt 0, user_id, NULL)) AS active_users FROM user_stat;SUM(column)忽略 NULL不会因为某行是 NULL 就把整个结果变 NULL。AVG(column)也是忽略 NULL这里有一个反直觉的点AVG 不是每个分组的总值除以全组记录数而是除以非 NULL 值的个数。需要按全组行数平均时要手动算SUM(column) / COUNT(*)。MAX和MIN对字符串也能用按字符序比较。实际使用中更要注意的是MySQL 8.0 的优化器支持MAX(column)利用索引跳过大量数据但前提是条件里不要把索引列包上函数。5.3 加密与散列MD5、SHA2、AES_ENCRYPTMySQL 内置了常用散列函数SELECT MD5(abc); -- 900150983cd24fb0d6963f7d28e17f72 SELECT SHA2(abc, 256); -- 64位十六进制字符串 SELECT SHA1(abc); -- 40位十六进制字符串MD5 早已不适合存高安全级别的密码但它在业务里仍然很常用比如生成接口签名、对文本做指纹去重。前两年我处理内容去重需求就是先对正文做 MD5再对指纹建唯一索引几十万条数据秒级去重。SHA2 第二个参数支持 224、256、384、512其中 256 最常用。线上系统如果老接口用的是 MD5新老系统对接时需要明确是统一用哪种散列不然后端对不上签名就抓瞎。AES_ENCRYPT/AES_DECRYPT是对称加密和散列不同可以解密回来。它的用法-- 加密 SELECT AES_ENCRYPT(敏感内容, encrypt_key); -- 解密 SELECT CAST(AES_DECRYPT(encrypted_col, encrypt_key) AS CHAR) FROM secret_table;AES_ENCRYPT 返回二进制串直接存字段会造成乱码一般先 HEX() 再存。密钥管理是另一个大话题这里只提醒一句密钥不要硬编码进SQL脚本更不要出现在日志里。生产环境密钥一般从配置中心拉取到应用层由应用层做加密后入库避免数据库里明文密钥。5.4 信息函数VERSION、DATABASE、USER、LAST_INSERT_ID排查环境问题时常用的SELECT VERSION(); -- 8.0.36 SELECT DATABASE(); -- 当前库名 SELECT USER(), CURRENT_USER(); -- 当前连接用户 SELECT CONNECTION_ID(); -- 当前连接IDLAST_INSERT_ID()返回当前连接上一条 INSERT 产生的自增ID注意三个要点它绑定的是当前会话连接不是全局。别的连接插入的数据它感知不到。没有新插入时再次调用仍然返回最近一次插入的ID可能造成误判。一次插入多行时只返回第一行的自增ID。组合插入主从表数据时会用到。插入订单主表拿到 order_id再插入明细表INSERT INTO orders(user_id, amount) VALUES (1001, 99.90); -- 假设这是第一条新插入得到 order_id 10086 INSERT INTO order_detail(order_id, product_name, price) VALUES (LAST_INSERT_ID(), 商品A, 99.90);注意多行插入时LAST_INSERT_ID()返回的是第一条数据的ID如果需要每一行的ID得在应用层解析或者改写循环插入。5.5 类型转换CAST、CONVERT显式类型转换用CAST(expr AS type)SELECT CAST(123 AS SIGNED); -- 123 SELECT CAST(2024-12-18 AS DATE); -- 2024-12-18 SELECT CAST(123 AS CHAR); -- 123CONVERT(expr, type)作用相同CONVERT还可以转字符集SELECT CONVERT(name USING utf8mb4) FROM old_table;转换时的失败行为要看情况字符串转数字时遇到非数字字符只取前缀可识别部分。CAST(123abc AS SIGNED)返回 123CAST(abc123 AS SIGNED)返回 0不会报错。这正是数据清洗时需要警惕的静默转换不会告诉你数据有问题导入前最好先用 REGEXP 校验格式。5.6 其他实用函数COALESCE、GREATEST、LEAST、UUIDCOALESCE(expr1, expr2, ...)返回参数列表中第一个非 NULL 值相当于多个字段间取首个有值SELECT COALESCE(mobile, phone, 无联系方式) FROM contact;GREATEST(a, b, c)返回最大值LEAST(a, b, c)返回最小值多个列横向比较时好用SELECT GREATEST(price1, price2, price3) AS max_price FROM product;UUID()生成一个标准 UUID 字符串SELECT UUID(); -- 0a3dd514-1a34-11ef-9ed2-0242ac110002可以作为不依赖自增ID的业务主键。但 UUID 作为主键在 InnoDB 里会引发随机插入造成页分裂和碎片性能敏感的大表不建议直接用字符串 UUID 做主键可以考虑 UUID 转成二进制或者改用雪花ID。6. 三个业务场景的函数组合实战单独记函数不如看组合。最后用三个实际需求把常用函数串一遍。6.1 月度订单报表日期函数加聚合函数需求统计最近6个月每个月的订单量、销售额并计算环比增长率。WITH monthly_stats AS ( SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE create_time DATE_SUB(DATE_FORMAT(CURDATE(), %Y-%m-01), INTERVAL 5 MONTH) GROUP BY DATE_FORMAT(create_time, %Y-%m) ) SELECT month, order_cnt, total_amount, LAG(order_cnt, 1) OVER (ORDER BY month) AS prev_cnt, ROUND( (order_cnt - LAG(order_cnt, 1) OVER (ORDER BY month)) / NULLIF(LAG(order_cnt, 1) OVER (ORDER BY month), 0) * 100, 2 ) AS growth_rate FROM monthly_stats ORDER BY month;这里用到了多个前面讲过的点DATE_FORMAT做月份分组DATE_SUB和DATE_FORMAT(CURDATE(), %Y-%m-01)构造6个月前的月初日期LAG窗口函数取上一行数据NULLIF防止上个月订单量为0时除零报错ROUND控制百分比小数位如果数据库是 MySQL 5.7没有窗口函数LAG这段可以改成本月数据 LEFT JOIN 上月数据或者用标量子查询实现。6.2 用户手机号脱敏字符串函数组合需求把测试环境里所有 user 表的手机号处理成脱敏形式保留前3后4。UPDATE user SET phone CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) WHERE phone REGEXP ^1[3-9][0-9]{9}$;先判断手机号格式是否合法再脱敏避免把脏数据也原样处理进去。执行前先 SELECT 预览SELECT phone AS original_phone, CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) AS masked_phone FROM user LIMIT 10;这种做法在刷测试库、脱敏导出场景里很常见。如果还需要生成不重复的手机号可以在脱敏后再用CONCAT拼上随机后缀但要注意保证不违反手机号格式校验逻辑。6.3 分类商品聚合行转列GROUP_CONCAT需求把每个分类下的商品名拼成一行按价格升序排列。SELECT category, COUNT(*) AS product_count, GROUP_CONCAT(product_name ORDER BY price ASC SEPARATOR 、) AS product_list FROM products GROUP BY category;再配合 SUBSTRING_INDEX 取列表里的第一个商品SELECT category, SUBSTRING_INDEX(GROUP_CONCAT(product_name ORDER BY price ASC SEPARATOR 、), 、, 1) AS cheapest_product FROM products GROUP BY category;在这个需求里GROUP_CONCAT 的排序、去重、分隔符设置、长度限制就全部用上了。如果拼接结果超过 1024 字节记得先加大 group_concat_max_len。6.4 字符串数字混排问题ORDER BY 的小坑需求对编号字段排序但编号是P-1、P-2、P-10这种带前缀的字符串。直接ORDER BY code会得到字典序P-1 P-10 P-2因为10和1比较时先比第一位结果10 2。要按数字部分排可以SELECT * FROM product ORDER BY CAST(SUBSTRING(code, 3) AS SIGNED);更粗暴但常见的写法是加0隐式转数字SELECT * FROM product ORDER BY SUBSTRING(code, 3) 0;这类写法能应付大多数简单场景但前提是截出来的部分确实都是数字否则转换结果会是0排序就不准了。整理完这一圈函数我最大的体会是函数本身不难记难的是知道每个函数在真实数据下的行为差异。建议你把常用函数写进一个测试SQL文件建一张临时表把 NULL、空字符串、特别大的整数、浮点数精度这些边界情况都跑一遍亲眼看看返回什么。我自己就是这么积累的比翻文档印象深得多。真到排查线上慢查询或者数据错乱的时候这些看起来基础的东西往往是破局的钥匙。