1. 日期函数的正确打开方式别只沉浸在NOW()里做MySQL开发这些年我见过最多的SQL问题一半以上都出在日期处理上。很多人写NOW()取当前时间用得飞起但一遇到上周一的订单量上个月的注册用户数这类需求就开始头疼了。其实MySQL的日期函数远比你想象中强大但前提是你要理解它内部的存储逻辑。1.1 日期存储的底层逻辑为什么建议用DATETIME而不是字符串先说一个最基础也最关键的认知MySQL的日期时间在底层是数字形式存储的所谓2025-01-15 10:30:00只是它展示给你的样子。所以日期函数干的事本质上就是在各种数字格式之间做转换。你应该见过有人用字符串比较日期SELECT * FROM orders WHERE order_time 2025-01-15;这条SQL在大多数情况下能跑通但它有一个隐患如果order_time列是DATETIME类型而2025-01-15只是字符串MySQL需要先把字符串隐式转换成日期再比较。隐式转换一旦发生索引就失效了数据量大时全表扫描就是必然结局。正确做法是SELECT * FROM orders WHERE order_time 2025-01-15 00:00:00 AND order_time 2025-01-16 00:00:00;或者直接使用日期函数把列值规范化SELECT * FROM orders WHERE DATE(order_time) 2025-01-15;注意DATE(order_time)虽然写法干净但同样无法使用索引因为每一行都要先过一遍函数。如果表很大更推荐范围写法这类问题我在后面章节单独展开讲。1.2 高频日期函数逐个拆解NOW、DATE_FORMAT、DATEDIFF在真实场景中的用法我把日常用得最多的日期函数整理成了下面这张速查表每个都附带了使用场景。函数作用典型场景NOW()返回当前日期时间记录操作时间、更新updated_atCURDATE()返回当前日期无时间统计当天下单量DATE_FORMAT(date, fmt)按指定格式输出日期前端展示、报表分组DATEDIFF(d1, d2)返回两个日期相差的天数计算账龄、会员活跃天数DATE_ADD(date, INTERVAL n unit)日期加减计算到期日、回溯统计窗口YEAR()/MONTH()/DAY()提取日期部分按月/年分组统计DAYOFWEEK()返回星期索引1周日判断周末UNIX_TIMESTAMP()转成时间戳与前端/其他系统对接这里重点说DATE_FORMAT它是报表开发中最常用的一个。比如要按小时统计订单量SELECT DATE_FORMAT(create_time, %Y-%m-%d %H:00:00) AS hour_slot, COUNT(*) AS order_cnt FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 24 HOUR) GROUP BY hour_slot ORDER BY hour_slot;格式化占位符有严格区分%Y和%y不一样四位年份和两位年份%m和%i也不一样月份和分钟首次用容易写错。我建议直接记常用格式定期查阅官方文档核对别凭印象写。1.3 时区带来的坑TIMESTAMP和DATETIME的选择另一个高频翻车点是TIMESTAMP和DATETIME的时区差异。TIMESTAMP是UTC存储、查询时按会话时区转换而DATETIME是原样存储不带时区信息。如果你的数据库连接串设置了serverTimezoneAsia/Shanghai但部署服务器实际是UTC时区那NOW()取到的结果会凭空差8个小时。这个坑我实测遇到过不止一次排查时往往先怀疑代码最后才发现是时区配置有偏差。我的建议是统一约定所有环境统一使用CST或UTC08:00时区连接串、服务器时区、MySQL的time_zone变量三者保持一致。表结构选型如果需要跨时区使用或者对接的是全球化业务优先TIMESTAMP如果只是国内业务、要精确存储用户输入的日期时间DATETIME更直观。上线前自查写一条SELECT NOW(), 连接到测试库对比一下如果和服务器当前时间不一致说明时区配置有问题。2. 字符串函数数据处理和报表清洗的核心弹药库日期函数解决时间怎么算的问题字符串函数则是解决字段怎么撕的问题。在实际业务里字符串处理几乎每天都在发生——用户昵称带特殊字符、手机号中间四位要脱敏、商品编码要截取前缀分类、多张小表要拼接成一张宽表。2.1 CONCAT家族拼接的正确姿势与NULL陷阱先看最基础的拼接。常见的三种写法是CONCAT、CONCAT_WS和直接在SQL里用||取决于sql_mode是否开启了PIPES_AS_CONCAT。-- 常规拼接 SELECT CONCAT(first_name, last_name) AS full_name FROM users; -- 带分隔符拼接第二个参数是分隔符 SELECT CONCAT_WS(-, province, city, district) AS full_address FROM users; -- 注意如果first_name为NULLCONCAT返回NULL SELECT CONCAT(first_name, COALESCE(last_name, )) FROM users;这里必须单独说CONCAT和NULL的行为。很多人以为CONCAT(NULL, abc)会返回abc但MySQL的实际行为是返回NULL。要处理NULL要么用IFNULL包一层要么用CONCAT_WS——它有自动跳过NULL的能力算是个冷门优点。我在日常写项目时喜欢用CONCAT_WS多于CONCAT因为处理邮政编码、地址这种带分隔符的拼接时不用手动处理NULL和多余的-。2.2 SUBSTRING、LEFT、RIGHT准确裁出你需要的那段截取函数在脱敏、格式清洗里常驻。函数作用示例结果LEFT(s, n)取左侧n个字符LEFT(13812345678, 3)138RIGHT(s, n)取右侧n个字符RIGHT(13812345678, 4)5678SUBSTRING(s, pos, len)从位置pos开始取len个SUBSTRING(MySQL, 2, 3)ySQSUBSTRING_INDEX(s, delim, n)按分隔符截取SUBSTRING_INDEX(a,b,c, ,, 2)a,b脱敏的经典写法是SELECT CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) AS masked_phone FROM users;凡是涉及拼接和截取建议先在SELECT里跑一条不带WHERE的语句验证结果再套进UPDATE或大批量处理避免因为NULL或长度不足产生脏数据。2.3 REPLACE、TRIM和大小写转换清洗数据三件套从Excel导入的数据经常带空格、全角字符或者字段里混着不一致的大小写。这时候用TRIM、REPLACE和大小写函数组合就能快速清洗。-- 去除两端空格 SELECT TRIM( hello ); -- 结果为 hello -- 去除两端指定字符注意是去除两端出现的所有该字符 SELECT TRIM(BOTH x FROM xxxMySQLxxx); -- 结果为 MySQL -- 替换字符串中的特定内容 SELECT REPLACE(2025-01-15, -, /); -- 结果为 2025/01/15 -- 大小写标准化 SELECT LOWER(MySQL), UPPER(mysql);还有LPAD和RPAD这类补位函数做流水号、订单号格式化时很有用。比如把订单号补齐到8位SELECT LPAD(order_id, 8, 0) FROM orders;2.4 字符串切分与正则匹配比LIKE更强大的REGEXP模糊匹配大家都会用LIKE但遇到以A开头、中间包含数字、以B结尾这种组合条件时LIKE就很痛苦。MySQL的正则表达式函数REGEXP能帮你解决这类需求。-- 查找手机号以138开头的用户 SELECT * FROM users WHERE phone REGEXP ^138; -- 查找邮箱属于任意主流免费服务商163/qq/126的用户 SELECT * FROM users WHERE email REGEXP (163|qq|126)\.com$;REGEXP在MySQL 8.0中升级成了REGEXP_LIKE、REGEXP_REPLACE、REGEXP_SUBSTR等新函数功能更强。但要注意正则匹配没法像LIKE prefix%那样利用普通索引数据量大了要谨慎。3. 数学函数报表和积分系统里的实用计算工具数学函数不像日期和字符串用得那么密但一碰上就会用到尤其是做报表、算金额、抽奖、排名这些场景。3.1 ROUND、FLOOR、CEILING取整不只是四舍五入说到取整大多数人第一反应是ROUND但业务中向下取整和向上取整的需求也不少见。-- 四舍五入到两位小数 SELECT ROUND(3.14159, 2); -- 3.14 -- 向下取整到整数 SELECT FLOOR(3.999); -- 3 -- 向上取整到整数 SELECT CEILING(3.001); -- 4 -- 截断小数直接丢掉指定位之后的内容 SELECT TRUNCATE(3.14159, 2); -- 3.14一个容易踩的坑ROUND在MySQL里对.5的处理遵循四舍五入在部分语言里是银行家舍入所以ROUND(2.5)结果是3ROUND(3.5)结果是4这一点与Python的round不同。如果团队内有多种语言混合开发务必确认统一口径。3.2 RAND()抽奖和随机取样的玩法RAND()返回0到1之间的随机小数常用于随机排序和抽样。-- 随机取5条记录 SELECT * FROM products ORDER BY RAND() LIMIT 5; -- 生成指定范围的随机整数比如1到100 SELECT FLOOR(RAND() * 100) 1;但是ORDER BY RAND()在大表上性能很糟糕因为它会对全表每行生成随机数再排序。数据量超过几万行时我更推荐先SELECT COUNT(*)得到总数然后在应用层随机取一个偏移量再LIMIT 1配合OFFSET取数。这是典型的看似简单、实则费性能的场景。3.3 聚合函数中的数学函数SUM、AVG遇上NULL的规则严格来说SUM和AVG不是数学函数而是聚合函数但它们离不开数学计算所以一起说。关键规则是聚合函数会忽略NULL值。AVG不会把NULL当作0来算这一点很多人会搞错。比如一个班有10个人其中一个人缺考AVG(score)算的是9个人的平均分而不是总分除以10。另外一个高频需求是按金额区间统计可以配合数学函数分组SELECT CASE WHEN amount 100 THEN 0-100 WHEN amount 500 THEN 100-500 ELSE 500 END AS amount_bucket, COUNT(*), SUM(amount) FROM orders GROUP BY amount_bucket;这种分组不直接用数学函数但CASE WHEN表达式本身就是一个转译函数配合聚合能做出非常灵活的分档统计。4. 其他内置函数控制流程、判空与类型转换的组合艺术MySQL的函数体系里除了按数据类型划分的日期、字符串、数学函数还有一大批通用函数负责流程控制、空值处理和类型转换。它们是让SQL从一个查询工具变成业务逻辑引擎的关键。4.1 IF与IFNULL最简单的二选一逻辑IF(expr, val1, val2)是SQL里最直白的条件函数。比如在查询里标记是否大客户SELECT customer_name, total_amount, IF(total_amount 10000, VIP, 普通) AS customer_level FROM customer_summary;IFNULL(val1, val2)则专注于处理NULL。比如用户表里nickname为空时显示默认名SELECT IFNULL(nickname, 匿名用户) AS display_name FROM users;这里有个使用习惯问题我知道很多开发喜欢在SELECT里对NULL字段做处理但如果你在大批量导出或报表统计时习惯用IFNULL它会在每行上执行一次判断。量级小无所谓量级大就要评估。4.2 CASE WHEN比IF更灵活的多分支流程控制一旦条件超过两个CASE WHEN就远比嵌套IF可读性好。它还能和聚合函数配合做条件统计。SELECT store_id, SUM(CASE WHEN status completed THEN 1 ELSE 0 END) AS completed_orders, SUM(CASE WHEN status cancelled THEN 1 ELSE 0 END) AS cancelled_orders FROM orders GROUP BY store_id;这种写法叫条件聚合是替代多条SQL各查一次的最高频手段。很多人写日报、周报时会连发四五条查询统计不同状态的数据其实一条SQL就能解决。4.3 COALESCE一连串备胎中的第一个非NULL值COALESCE返回参数列表里第一个非NULL的值。它比IFNULL更灵活因为可以给多个备选值。比如电商项目里一个商品可能会有多个维度的价格秒杀价、折扣价、原价。查询时希望能拿到当前有效的最低价SELECT product_name, COALESCE(seckill_price, discount_price, original_price) AS final_price FROM products;如果seckill_price为空就用折扣价再为空用原价。这个逻辑如果用CASE WHEN写会非常啰嗦COALESCE一行搞定。4.4 CAST与CONVERT类型不对函数白学很多时候函数运行结果和预期不符不是函数用错了而是数据类型根本不对。CAST和CONVERT就是用来做显式类型转换的。-- 字符串转数字注意123abc会被转成123而abc123会转成0这个行为一定要知道 SELECT CAST(123 AS SIGNED); -- 123 SELECT CAST(123abc AS SIGNED); -- 123 SELECT CAST(abc123 AS SIGNED); -- 0 -- 日期转字符串再格式化 SELECT CAST(NOW() AS CHAR); -- CONVERT的风格略有不同 SELECT CONVERT(2025-01-15, DATE);转换函数的隐式规则很容易埋雷尤其是字符串转数字时MySQL会尽可能提取开头的数字部分提取不到就返回0。在对接外部导入的数据时一定要先跑查询检查转换结果否则容易把脏数据洗成看似正常的0。5. 内置函数的组合实战从需求描述到一条SQL的完整推导函数单独讲都很简单真正考验功力的是把它们组合起来解决实际业务问题。这一节我用两个真实案例完整走一遍从需求到SQL的推导过程。5.1 实战案例一会员活跃周期统计需求描述统计每个会员在过去90天内的活跃天数并且要区分工作日和周末活跃天数。先拆解过去90天要用DATE_SUB(NOW(), INTERVAL 90 DAY)作为起点活跃天数意味着要对每天的去重用COUNT(DISTINCT DATE(login_time))工作日/周末用DAYOFWEEK()判断注意MySQL里DAYOFWEEK返回1周日2周一所以工作日是2到6。最终SQL大概长这样SELECT user_id, COUNT(DISTINCT DATE(login_time)) AS active_days, SUM(CASE WHEN DAYOFWEEK(login_time) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) AS weekday_active_days, SUM(CASE WHEN DAYOFWEEK(login_time) IN (1, 7) THEN 1 ELSE 0 END) AS weekend_active_days FROM login_log WHERE login_time DATE_SUB(CURDATE(), INTERVAL 90 DAY) GROUP BY user_id;注意CASE WHEN里用了SUM而不是COUNT因为要按行累加满足条件的记录数。这种写法刚入门的人容易写成COUNT(CASE WHEN ...)结果永远返回总行数因为COUNT只看值是否非NULL对0和1都计数。要用SUM包CASE WHEN或者让CASE WHEN返回NULL而不是0两种选其一。5.2 实战案例二商品价格脱敏与分档需求描述管理后台需要展示商品名称、价格档位、脱敏后的商品编码。拆解思路商品编码脱敏编码规则是类别-流水号要把流水号中间两位用*替换用SUBSTRING_INDEX和CONCAT组合。价格档位用CASE WHEN分档位。SELECT product_name, CASE WHEN price 50 THEN 廉价 WHEN price 200 THEN 平价 WHEN price 1000 THEN 中高端 ELSE 高端 END AS price_tier, CONCAT( SUBSTRING_INDEX(product_code, -, 1), -, REPLACE(SUBSTRING_INDEX(product_code, -, -1), SUBSTRING(SUBSTRING_INDEX(product_code, -, -1), 3, 2), **) ) AS masked_code FROM products;这段代码的REPLACE部分有些绕但对这种固定格式编码的脱敏非常有效而且不需要额外写存储过程。组合使用函数的原则是什么我的经验是先在独立的SELECT里验证每个函数的返回值确认无误再层层嵌套。SQL嵌套一旦超过三层可读性和排错成本都会直线上升不要一上来就憋大招。5.3 函数嵌套的Debug技巧逐步拆解验证法写复杂SQL时我几乎不用debugger就用拆解法。把整个表达式拆成几步先在查询里单独看每一步的结果-- 第1步单独看提取部分 SELECT product_code, SUBSTRING_INDEX(product_code, -, 1) AS part1, SUBSTRING_INDEX(product_code, -, -1) AS part2, SUBSTRING(SUBSTRING_INDEX(product_code, -, -1), 3, 2) AS mask_target FROM products LIMIT 10;跑到这一步你就能清楚看到每一层返回了什么。如果某一步返回NULL说明分割符或边界条件与预期不符立刻就能定位。这比写好一大段SQL然后对着报错信息猜高效得多。6. 避坑清单与性能红线一次大查询教会我的事最后这部分我把这些年踩过和见过的内置函数相关的大坑打包整理一下。很多问题不是函数本身复杂而是大家默认函数嘛随手一写不就行了结果一上线就出事。6.1 函数使用导致索引失效的情况这是性能问题里最高频的一条。对索引列使用函数会导致索引失效。-- 全表扫描索引失效 SELECT * FROM orders WHERE DATE(create_time) 2025-01-15; -- 走索引的范围查询 SELECT * FROM orders WHERE create_time 2025-01-15 00:00:00 AND create_time 2025-01-16 00:00:00;同样的问题也出现在字符串函数上。比如你想查姓氏为张的用户如果写了WHERE SUBSTRING(name, 1, 1) 张索引就废了。正确做法是WHERE name LIKE 张%。带REGEXP的查询更是如此它基本没有索引可走。大数据量下要做正则匹配应尽量在应用层处理或者引入搜索引擎而不是直接在MySQL里硬扛。6.2 隐式类型转换函数参数的类型陷阱前面在CAST部分提到过字符串转数字时会尽量解析数字前缀。这个特性在隐式转换上会带来很隐蔽的BUG。比如下面这条SELECT * FROM users WHERE phone 13812345678;如果phone列是VARCHAR类型MySQL会把右边的数字转成字符串去比较结果看起来没问题。但一旦phonel列存的是138-1234-5678这类带格式的数据比较结果就完全不可控了。所以开发规范里要有一条规定字符串列与数字值比较时永远显式转换别靠MySQL隐式处理。6.3 NULL参与运算的结果COALESCE的进一步运用任何普通函数只要参数里带NULL结果大概率是NULL。算术运算也一样NULL 1还是NULL。所以在做金额汇总时如果某个字段允许NULLSUM会自动忽略但如果你在SELECT里用普通算术做了额外的计算比如SELECT amount * discount_rate FROM orders;一旦discount_rate为NULL整行金额都变成NULL。处理方式是COALESCE(discount_rate, 1)SELECT amount * COALESCE(discount_rate, 1) FROM orders;这算是一个黄金习惯涉及可能为空的字段做四则运算之前先想一下要不要用COALESCE兜底。6.4 日期函数使用中的其他常见坑月末、闰年、夏令时日期函数还有个很少人提但很麻烦的点月末和夏令时。计算上个月最后一天如果用DATE_SUB(DATE_SUB(..., INTERVAL DAY(...)-1 DAY), INTERVAL 1 DAY)这类方式非常容易出错。MySQL 8.0提供了LAST_DAY函数直接返回所在月的最后一天SELECT LAST_DAY(2025-02-01); -- 2025-02-28 SELECT LAST_DAY(2024-02-01); -- 2024-02-29闰年夏令时影响的是TIMESTAMP类型的计算。如果业务服务器设定了非UTC时区且使用夏令时某些日期不存在跨时切换时刻处理起来极其复杂。国内没有夏令时问题但如果你维护的是海外业务一定要用UTC存储、展示层再转换不要在数据库层做时区换算。6.5 存储过程里使用函数的注意点最后提醒一句写在存储过程或触发器里的人函数在存储过程中每次调用都有开销而且对NULL的处理逻辑不变。如果存储过程里循环逐行调用函数性能会很难看。尽量把函数调用放到SQL语句本身的力量里靠集合操作而不是循环。比如你要给一批用户更新最后登录日期就写一条UPDATE配合NOW()而不是开一个游标逐行更新UPDATE users SET last_login_at NOW() WHERE user_id IN (...);这种集合式写法既简洁又高效也让内置函数的价值真正发挥出来。MySQL内置函数这组工具说到底是让你在工作中少写几百行Java或Python的日期处理、字符串处理代码。掌握它们的正确姿势不只是背函数名而是要理解数据类型、NULL语义和性能影响。希望这份拆解能帮你在日常SQL里少踩几个坑。