1. 先聊清楚日期时间函数为什么值得单独整一篇做业务开发的人早晚都会在时间字段上栽一次跟头。我自己印象最深的一次是 2019 年做一张月活报表逻辑看着没问题跑出来的数据却比运营那边少了一截排查了大半天最后发现是DATE_FORMAT用%m分组时把跨年的月份合并处理了而我没加年份。那一晚之后我就养成了一个习惯凡是跟 MySQL 日期时间相关的写法全部单独整理一份清单随用随查。这篇就是这份清单的完整版本覆盖 MySQL 中日期时间类型选型、当前时间获取、格式化与解析、加减运算、差值计算、周期归类、时间戳互转以及报表场景里的实战写法和索引性能陷阱。MySQL 里的日期时间处理函数官方文档列出来的大概有六七十个但真正高频使用的也就二十来个。问题在于这二十来个里面有相当一部分存在看起来一样、实际不一样的情况比如NOW()和SYSDATE()比如DATEDIFF和TIMESTAMPDIFF比如DATE_ADD和直接写 INTERVAL。用得顺手的时候你不会觉得有问题一旦线上出状况这些细枝末节就是排查的入口。这篇内容适合三类人看。第一类是刚上手 MySQL、写 SQL 还停留在SELECT *阶段的同学可以把它当成一份带解释的速查手册第二类是写了几年业务 SQL、但对时间函数的边界情况心里没底的中级开发重点看第 4 章和第 8 章第三类是经常写统计报表、跑数据核对的分析和运营同学第 7 章的四个实战案例基本能覆盖日常八成的需求。我尽量不堆砌函数签名而是把每个函数什么时候该用、什么时候不该用讲清楚配上面向真实业务的可复现写法。2. 选型先行MySQL 五种日期时间类型到底怎么挑2.1 DATETIME 和 TIMESTAMP 的差别不只是范围很多人以为这两个类型的区别就是取值范围其实真正的差异有三层。范围只是第一层DATETIME支持1000-01-01 00:00:00到9999-12-31 23:59:59TIMESTAMP只支持1970-01-01 00:00:01到2038-01-19 03:14:07。第二层是存储与时区的关系TIMESTAMP在写入时会把客户端时区的时间转成 UTC 存起来读取时再转回当前会话时区DATETIME则是原样存取不做任何时区换算。第三层是索引和默认值的行为差异TIMESTAMP列在explicit_defaults_for_timestamp关闭时第一个TIMESTAMP列会自动带上DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP这个隐式行为坑过不少人。理解了时区那一层选型就清晰了。如果你的系统需要跨时区展示同一个时间点在不同地区看到不同本地时间那TIMESTAMP的自动换算反而是优势。但要注意它的换算基准是会话时区如果连接池里不同连接设置了不同时区同一个字段读出来会不一样这类问题极难排查。我个人的做法是业务表统一用DATETIME应用层统一以 UTC 或固定时区处理把一个时间点只存一种形态展示时再按用户所在地区转换。这样虽然多写一点转换代码但排查问题时链路是干净的。2.2 精度、时区和小数秒的取舍MySQL 5.6 以后DATETIME、TIMESTAMP和TIME都支持小数秒写法是DATETIME(3)表示毫秒精度DATETIME(6)表示微秒精度圆括号里的数字就是保留几位小数。不写括号等价于DATETIME(0)也就是整秒。这里有个容易忽略的点加上小数秒后存储空间会增加。DATETIME(0)是 5 字节DATETIME(3)是 6 字节DATETIME(6)是 7 字节TIMESTAMP(0)是 4 字节TIMESTAMP(3)是 5 字节。那到底要不要加精度看业务是否真的需要。订单创建时间、用户注册时间这类字段毫秒级别的区分度意义不大反而会让存储和比较都更啰嗦。但如果是事件流水、埋点日志、高频交易记录同一秒内可能产生几十条数据这时候就必须上毫秒甚至微秒精度否则排序会失去稳定性。我在日志类表里统一用DATETIME(3)够用且不至于浪费。注意MySQL 5.6 以前版本不支持小数秒从老版本迁移时导出脚本里的DATETIME(3)会直接报语法错误需要在导数前做替换。还有一种情况是用INT存 Unix 时间戳。这种做法在老系统里很常见好处是时区无关、排序和比较高效坏处是完全丧失了可读性写 SQL 查数据时得手动转换调试体验很差。我的建议是业务表用DATETIME只有在对接外部系统、对方强制要求时间戳时才考虑在中间层做转换而不是直接把存储格式改成INT。2.3 五种类型速查对照类型存储空间取值范围时区敏感小数秒支持DATE3 字节1000-01-01 ~ 9999-12-31否无TIME3~6 字节-838:59:59 ~ 838:59:59否支持YEAR1 字节1901 ~ 2155否无DATETIME5~8 字节1000-01-01 00:00:00 ~ 9999-12-31 23:59:59否支持TIMESTAMP4~7 字节1970-01-01 00:00:01 ~ 2038-01-19 03:14:07是支持顺带提一句TIME类型的范围它不是只能存 0 到 24 小时最大能到 838 小时这个设计是为了支持时间间隔运算。我第一次看到TIME存着120:30:00的时候也愣了一下后来才明白这是有意为之。所以如果你的字段是TIME类型别默认它一定是一天内的时间做校验时要注意这个上限。3. 取时间与格式化从 NOW() 到 DATE_FORMAT 的完整链路3.1 获取当前时间的几个函数别混着用NOW()、CURRENT_TIMESTAMP()、LOCALTIME()、LOCALTIMESTAMP()这几个在 MySQL 里返回的是同一个东西当前会话时区下的当前日期时间。它们可以互换使用我一般写NOW()短且语义直白。CURDATE()和CURRENT_DATE()返回当前日期CURTIME()和CURRENT_TIME()返回当前时间这两个基本不会记错。真正需要留意的是SYSDATE()和UTC_TIMESTAMP()。SYSDATE()和NOW()在一条语句里的表现完全不同NOW()取的是语句开始执行的时间同一条 SQL 里调用多少次都是同一个值SYSDATE()取的是函数实际执行那一刻的时间同一条语句里两次调用可能差几毫秒。看下面这个对比就很清楚了。SELECT NOW(), SLEEP(2), NOW(); -- 三列的值完全相同因为 NOW() 在语句开始时就固定了 SELECT SYSDATE(), SLEEP(2), SYSDATE(); -- 前后两个 SYSDATE() 相差约 2 秒这个差异在大多数业务里无关紧要但在需要严格时间对齐的场景下就会出问题。更关键的是主从复制NOW()是确定性函数语句会原样复制到从库执行主从时间自然一致SYSDATE()在基于语句的复制模式下从库执行时会取从库自己的时间主从数据就可能不一致。所以除非明确知道自己在做什么业务 SQL 里一律用NOW()。UTC_TIMESTAMP()返回的是 UTC 时区的时间跟会话时区无关。存日志、存需要跨时区一致的时间戳时很有用。我一般会在应用连接建立后执行一次SET time_zone 08:00把会话时区固定下来这样后续所有的NOW()输出都是可预期的。3.2 DATE_FORMAT 的格式符一张表记住常用的那十几个DATE_FORMAT(date, format)是最常用的格式化函数把你手里的日期时间转成想要的字符串形态。格式符有二十多个实际项目里反复用的也就十来个格式符含义示例输出%Y四位年份2024%y两位年份24%m两位月份03%c月份不带前导零3%d两位日期09%e日期不带前导零9%H24 小时制小时15%i分钟07%s秒42%f微秒000000%W星期英文全名Saturday%w星期数字周日为 06%M月份英文全名March%b月份英文缩写Mar%T24 小时制完整时间15:07:42%j一年中的第几天069拼装方面几个特别常用的组合记下来能省不少事。按日期分组用DATE_FORMAT(t, %Y-%m-%d)按月分组用DATE_FORMAT(t, %Y-%m)按小时分组用DATE_FORMAT(t, %Y-%m-%d %H:00:00)。这里必须强调按天按月分组时一定要带上%Y只写%m会把不同年份的同一个月合并到一起这就是我当年踩过的那个坑。-- 按月统计订单量注意 %Y 不能省 SELECT DATE_FORMAT(created_at, %Y-%m) AS ym, COUNT(*) AS order_cnt FROM orders WHERE created_at 2024-01-01 GROUP BY ym ORDER BY ym;3.3 STR_TO_DATE字符串进数据库的正确姿势STR_TO_DATE(str, format)是DATE_FORMAT的反向操作把一个字符串按给定格式解析成日期时间。这个函数在数据导入场景里出镜率极高比如从 CSV 里导入的日期是2024/03/09这种斜杠格式直接插会报错得先转。INSERT INTO logs (log_date) VALUES (STR_TO_DATE(2024/03/09 15:07:42, %Y/%m/%d %H:%i:%s));有个实战经验值得说STR_TO_DATE在处理脏数据时的行为。如果传入的字符串跟格式对不上MySQL 会尽量解析能解析出部分就返回部分完全解析不了的返回NULL同时在严格模式下可能直接报错。比如STR_TO_DATE(abc, %Y-%m-%d)返回NULL但STR_TO_DATE(2024-13-45, %Y-%m-%d)在非严格模式下也可能返回NULL或截断值。批量导入数据时我惯用的做法是先建一张字段全是VARCHAR的临时表把原始数据灌进去再用SELECT ... WHERE STR_TO_DATE(x) IS NULL把解析失败的脏行捞出来人工核对最后才写入正式表。这个两步走的流程比直接往目标表插要稳妥得多尤其是数据来源不可控的时候。注意STR_TO_DATE和DATE_FORMAT的格式符要严格对应把%i分钟写成%m月份是很常见的手误解析结果会莫名其妙地错排查时优先检查格式符。4. 加减与差值计算INTERVAL、DATE_ADD、TIMESTAMPDIFF 怎么选4.1 INTERVAL 的单位与边界表现日期加减有两个入口DATE_ADD(date, INTERVAL expr unit)和DATE_SUB(date, INTERVAL expr unit)。同时MySQL 也允许直接用运算符和-写法是date INTERVAL expr unit。这两种写法功能等价 INTERVAL更简洁我平时更倾向这种。支持的unit很丰富常用的有SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR还有几个复合单位如YEAR_MONTH、DAY_HOUR、DAY_MINUTE、DAY_SECOND等。复合单位的写法有点特殊右边的表达式用引号包起来比如DATE_ADD(2024-03-09, INTERVAL 1 2 DAY_HOUR)表示加 1 天 2 小时。SELECT DATE_ADD(2024-03-09 10:00:00, INTERVAL 1 DAY) AS add_1d, 2024-03-09 10:00:00 INTERVAL 2:30 HOUR_MINUTE AS add_2h30m, DATE_SUB(2024-03-09, INTERVAL 1 WEEK) AS sub_1w;边界处理是最容易出意外的地方。DATE_ADD(2024-01-31, INTERVAL 1 MONTH)的结果是2024-02-29因为 2024 年是闰年但如果换成2023-01-31加一个月结果就是2023-02-28超出当月天数会被裁剪到当月最后一天。这不是 bug是设计如此但如果你在写续费一个月这类业务逻辑时没意识到这一点在月末可能出现比预期少一两天的情况。做会员到期时间计算时我一般会显式用LAST_DAY()把结果对齐到月末而不是直接依赖这个裁剪行为。4.2 DATEDIFF、TIMEDIFF、TIMESTAMPDIFF 三者的分工这三个函数经常被混用其实职责很分明。DATEDIFF(expr1, expr2)返回两个日期之间相差的天数只算日期部分忽略时分秒。TIMEDIFF(expr1, expr2)返回两个时间之间的差值结果是TIME类型最大到 838 小时。TIMESTAMPDIFF(unit, dt1, dt2)是最通用的能按指定单位返回差值unit可以是SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR。SELECT DATEDIFF(2024-03-09, 2024-03-01) AS diff_days, -- 8 TIMEDIFF(2024-03-09 12:00:00, 2024-03-09 09:30:00) AS diff_time, -- 02:30:00 TIMESTAMPDIFF(HOUR, 2024-03-09 09:30:00, 2024-03-09 12:00:00) AS diff_hours; -- 2这里有个关键细节TIMESTAMPDIFF的结果是向下取整的整数不是四舍五入。TIMESTAMPDIFF(HOUR, 2024-03-09 09:30:00, 2024-03-09 12:00:00)返回 2 而不是 3因为不满 3 小时。TIMESTAMPDIFF(MONTH, 2024-01-31, 2024-02-29)返回 0因为从 1 月 31 日到 2 月 29 日不足一个自然月。理解了这个按完整单位计数的逻辑很多看似奇怪的结果就都能解释了。参数顺序也要特别注意DATEDIFF(expr1, expr2)是expr1 - expr2TIMESTAMPDIFF(unit, dt1, dt2)也是dt2 - dt1注意这里顺序和DATEDIFF是反的。我见过不止一个人在这里写反了导致年龄算成负数。记法很简单TIMESTAMPDIFF里晚的时间放后面跟自然语言顺序一致。4.3 月份加减的月末陷阱与规避写法前面提到月末裁剪再展开说一个更隐蔽的场景如果要做最近 N 个自然月的区间统计用NOW() - INTERVAL 3 MONTH是安全的因为它是从一个具体日期往回推不会涉及月末对齐。但如果是每月同一天这种业务规则比如账单日是每月 15 号到期日是账单日加一个月那DATE_ADD(2024-01-31, INTERVAL 1 MONTH)的裁剪行为就会让账单日在 2 月的客户永远对不齐。我处理这类需求的方式是拆成两步先确定目标月份再用LAST_DAY和LEAST组合确定具体日期。-- 账单日为 31 号时2 月的到期日对齐到月末 SELECT LAST_DAY(2024-02-01) AS due_date; -- 结果2024-02-29换句话说把加一个月翻译成业务语言后你得先问清楚需求方如果目标月份没有这一天是顺延到下个月还是对齐到当月最后一天这个问题问出来很多隐藏的边界 bug 在上线前就能挡住。5. 提取、截断与周期归类统计报表里最常用的那一撮5.1 年季月周日提取函数与 EXTRACT单值提取函数很直观YEAR(d)、MONTH(d)、DAY(d)、HOUR(d)、MINUTE(d)、SECOND(d)、MICROSECOND(d)各取所需。QUARTER(d)返回季度1 到 4WEEK(d)返回周序号DAYOFYEAR(d)返回一年中的第几天DAYOFMONTH(d)就是DAY(d)的别名。EXTRACT(unit FROM date)是个万金油能替代上面大部分函数还支持复合单位。SELECT EXTRACT(YEAR FROM 2024-03-09) AS y, -- 2024 EXTRACT(YEAR_MONTH FROM 2024-03-09) AS ym, -- 202403 EXTRACT(QUARTER FROM 2024-03-09) AS q; -- 1EXTRACT(YEAR_MONTH FROM d)这个写法在做月度分组时特别好用直接得到一个202403这样的整数比字符串拼接更省事排序也天然正确。不过要注意它返回的是数字类型如果你后续要跟2024-03这种字符串比较会触发类型转换索引和语义上都不是好选择。星期相关的函数有两套返回值基准不同这是高频混淆点。DAYOFWEEK(d)返回 1 表示周日、2 表示周一一直到 7 表示周六。WEEKDAY(d)返回 0 表示周一、1 表示周二一直到 6 表示周日。两套体系差一天又差基准写工作日相关逻辑时一定要在注释里标明用的是哪套。我一般在项目里统一约定只用WEEKDAY因为 0 对应周一更符合国内习惯。-- 筛选出周末的数据用 WEEKDAY5 和 6 分别是周六和周日 SELECT * FROM orders WHERE WEEKDAY(created_at) IN (5, 6);5.2 LAST_DAY、WEEK 模式和季度统计LAST_DAY(d)返回该月最后一天在做月度汇总时非常顺手比如要生成一张每月最后一行的报表直接按LAST_DAY(created_at)分组就够了。WEEK(d, mode)的第二个参数mode控制周的起始日和计算规则取值 0 到 7行为各不相同。默认值是 0表示周日为一周第一天周日到周六算一周返回值范围 0 到 53。如果按国内习惯周一为一周第一天应该用mode 1。不同mode在同一日期上可能给出完全不同的周序号跨年周的处理方式也不同。mode一周起始日范围是否按 ISO 标准0周日0~53否1周一0~53否3周一1~53是ISO 86017周一1~53否做周报统计的时候WEEK的返回值必须配合年份一起用否则和第 3.2 节说的月份问题一样跨年时会合并。稳妥做法是用YEARWEEK(d, mode)它直接返回202409这样的年份周序号组合不用手动拼接。SELECT YEARWEEK(created_at, 1) AS yw, COUNT(*) AS cnt FROM orders GROUP BY yw ORDER BY yw;季度统计相对简单QUARTER(d)返回 1 到 4拼接年份就是分组键CONCAT(YEAR(d), -Q, QUARTER(d))。这个字符串形态可读性最好直接给到报表前端就能用。5.3 按天、按小时做时间截断的几种写法时间截断是个我在报表里天天用的技巧指的是把一个带时分秒的时间戳向下取整到某个粒度然后按这个粒度分组。按天截断最简单DATE(d)直接返回日期部分。按小时截断可以用DATE_FORMAT(d, %Y-%m-%d %H:00:00)。按 5 分钟或 15 分钟截断稍微绕一点思路是把时间换算成秒做整数除法后再乘回去-- 按 15 分钟粒度截断 SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(created_at) / 900) * 900) AS bucket, COUNT(*) AS cnt FROM events GROUP BY bucket ORDER BY bucket;这里的 900 就是 15 分钟对应的秒数5 分钟用 30030 分钟用 1800。这个方法的好处是粒度任意可控缺点是UNIX_TIMESTAMP在 2038 年之后会溢出不过对绝大多数系统来说这不是当下要考虑的问题。注意DATE(d)和DATE_FORMAT(d, %Y-%m-%d)在结果上看着一样但前者返回DATE类型后者返回字符串。在GROUP BY里两者都能用但如果后续还要做日期计算用DATE()更合适因为它能继续参与日期运算。6. 时间戳与 Unix 时间跨系统对接那一环6.1 UNIX_TIMESTAMP 与 FROM_UNIXTIME 的配对使用UNIX_TIMESTAMP([date])把你手里的日期时间转成 Unix 秒级时间戳不传参数时返回当前时间戳。FROM_UNIXTIME(timestamp[, format])反向操作把秒级时间戳转回日期时间。这两个函数在处理接口对接时特别常用因为很多第三方系统传过来的就是时间戳。SELECT UNIX_TIMESTAMP(2024-03-09 15:07:42) AS ts, -- 1709977662 FROM_UNIXTIME(1709977662) AS dt, -- 2024-03-09 15:07:42 FROM_UNIXTIME(1709977662, %Y-%m-%d) AS dt_str; -- 2024-03-09有个细节值得记下来FROM_UNIXTIME转换的结果受会话时区影响而UNIX_TIMESTAMP是把会话时区下的本地时间转成 UTC 秒数。也就是说这两个函数的进出依赖于当前会话的时区设置。如果你的应用连接没设置时区服务器默认时区又是 UTC那转换出来的时间可能跟预期差 8 小时。这类跨时区问题排查起来很费劲因为代码逻辑本身没错错在环境。6.2 毫秒时间戳的处理方法UNIX_TIMESTAMP只处理秒级Java 的System.currentTimeMillis()返回的是毫秒级前端Date.now()也是毫秒级两组数据混用时很容易出问题。比如把 13 位的毫秒时间戳直接传给FROM_UNIXTIME它会把前 10 位当秒数处理后面的丢掉得到一个完全不对的年份。-- 毫秒时间戳转日期时间先除以 1000 SELECT FROM_UNIXTIME(1709977662123 / 1000) AS dt; -- 如果要保留毫秒精度用小数部分 SELECT FROM_UNIXTIME(1709977662123 / 1000, %Y-%m-%d %H:%i:%s.%f) AS dt;反过来把DATETIME(3)转成毫秒时间戳用UNIX_TIMESTAMP(dt) * 1000不过这样会丢掉毫秒部分。要保留的话得写成UNIX_TIMESTAMP(dt) * 1000 MICROSECOND(dt) / 1000。这类换算我一般封装成一个应用层的工具方法而不是散落在 SQL 里否则早晚有人写错。6.3 CONVERT_TZ 时区转换的使用与限制CONVERT_TZ(dt, from_tz, to_tz)把一个日期时间从一个时区转换到另一个时区时区参数支持08:00这种偏移写法也支持Asia/Shanghai这种命名写法。偏移写法不需要额外数据命名写法则要求 MySQL 的时区表已经导入否则会返回NULL。-- 偏移写法任何环境都能用 SELECT CONVERT_TZ(2024-03-09 15:00:00, 00:00, 08:00); -- 结果2024-03-09 23:00:00 -- 命名写法需要先导入时区表 SELECT CONVERT_TZ(2024-03-09 15:00:00, UTC, Asia/Shanghai);命名写法返回NULL是个非常隐蔽的坑因为NULL在后面参与计算会静默传染最终报表里少数据但你一时看不出原因。我建议在生产环境统一用偏移写法或者在建库时就确认时区表已经导入。导入时区表的操作一般是加载系统时区文件到mysql库的时区表里具体命令随版本略有差异部署文档里写清楚就行。注意CONVERT_TZ只做时区换算不改变数据类型。如果输入是字符串且格式不规范可能直接返回NULL所以传入前确保是合法的日期时间值。7. 四个能直接抄的实战写法7.1 案例一按日、按月统计订单量这是最基础也最高频的需求。核心就是选对分组表达式并且把时间范围限制在WHERE里让查询能走索引。-- 近 30 天每日订单量 SELECT DATE(created_at) AS stat_date, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE created_at CURDATE() - INTERVAL 29 DAY AND created_at CURDATE() INTERVAL 1 DAY GROUP BY stat_date ORDER BY stat_date; -- 近 12 个自然月每月订单量 SELECT DATE_FORMAT(created_at, %Y-%m) AS stat_month, COUNT(*) AS order_cnt FROM orders WHERE created_at DATE_FORMAT(CURDATE() - INTERVAL 11 MONTH, %Y-%m-01) GROUP BY stat_month ORDER BY stat_month;第二段里那个DATE_FORMAT(CURDATE() - INTERVAL 11 MONTH, %Y-%m-01)是个实用小技巧不管当前是几号它都能算出 11 个月前的 1 号保证区间是完整的自然月。如果直接写CURDATE() - INTERVAL 11 MONTH会带上当天的日期季节性分析时首月数据会缺一截。7.2 案例二计算年龄和工龄年龄计算看着简单其实是个经典陷阱。用YEAR(NOW()) - YEAR(birthday)是错的因为生日还没到的人会被算大一岁。正确写法用TIMESTAMPDIFFSELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age, TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) AS months_worked FROM employees;TIMESTAMPDIFF(YEAR, birthday, CURDATE())会严格按完整年数计算生日当天才会加一岁这正是我们要的语义。工龄同理想按年就换YEAR想按月就换MONTH完全不用改结构。7.3 案例三订单超时未支付判定电商场景里判断下单超过 30 分钟未支付就自动关闭通常是定时任务扫表。写法的关键在于时间比较的精确性和索引友好度。SELECT id, order_no, created_at FROM orders WHERE status UNPAID AND created_at NOW() - INTERVAL 30 MINUTE;这里的NOW()是语句执行时间同一批扫描里所有行用的是同一个基准点结果是一致的。如果写成SYSDATE()理论上同一批扫到的行会基于略微不同的时间判断虽然实际差异极小但为了语义严谨还是用NOW()。另外created_at上最好有索引status和created_at的联合索引效果更好具体按实际数据分布来定。7.4 案例四次日留存与间隔分布留存计算的核心是同一批用户在不同日期是否活跃过通常会拆成两步先取口径人群再关联后续行为。-- 计算某日新用户的次日留存 WITH cohort AS ( SELECT user_id, DATE(first_login) AS reg_date FROM users WHERE first_login 2024-03-01 AND first_login 2024-03-08 ) SELECT c.reg_date, COUNT(DISTINCT c.user_id) AS new_users, COUNT(DISTINCT CASE WHEN DATE(l.login_at) c.reg_date INTERVAL 1 DAY THEN l.user_id END) AS retained_d1 FROM cohort c LEFT JOIN user_login_logs l ON l.user_id c.user_id AND l.login_at c.reg_date INTERVAL 1 DAY AND l.login_at c.reg_date INTERVAL 2 DAY GROUP BY c.reg_date ORDER BY c.reg_date;这种写法的好处是JOIN条件里已经限定了时间窗口login_at上的索引能派上用场。如果改成DATE(l.login_at) c.reg_date INTERVAL 1 DAY放在ON或WHERE里索引就用不上了数据量一大就慢得没法看。8. 索引与性能函数写在字段这一侧就是灾难8.1 函数包裹字段为什么会让索引失效B 树索引是按字段原始值排序的你写WHERE DATE(created_at) 2024-03-09MySQL 必须先对每一行计算DATE(created_at)得到结果再拿结果去比对这就退化成全表扫描了。这跟把书翻一遍找符合条件的那页是一个道理索引是目录目录是按原书页内容编的你非要按每页第一个字来找目录就白编了。常见的会让索引失效的写法有这几类WHERE DATE(created_at) ?、WHERE YEAR(created_at) 2024、WHERE DATE_FORMAT(created_at, %Y-%m) 2024-03、WHERE UNIX_TIMESTAMP(created_at) ?。这几类在真实项目里出现的频率相当高因为写起来直观但代价是全表扫描。还有个更隐蔽的情况隐式类型转换。如果字段是VARCHAR存日期你写WHERE date_str 20240309不带引号的数字MySQL 会把每行字符串转成数字再比较索引同样失效。反之如果字段是DATE或DATETIME跟字符串常量比较时是把常量转成日期这个转换是单向的索引仍然可用所以问题不大。真正要警惕的是字段类型和比较值类型不一致的那种情况。8.2 可落地的改写模板第一类改写是把函数从字段侧挪到常量侧或者改成范围查询。-- 改前全表扫描 SELECT * FROM orders WHERE DATE(created_at) 2024-03-09; -- 改后走 created_at 索引 SELECT * FROM orders WHERE created_at 2024-03-09 00:00:00 AND created_at 2024-03-10 00:00:00;第二类改写是月度统计时不要先算月份再筛先把范围限定在月份区间再在SELECT里做格式化。SELECT DATE_FORMAT(created_at, %Y-%m) AS stat_month, COUNT(*) AS cnt FROM orders WHERE created_at 2024-01-01 AND created_at 2024-04-01 GROUP BY stat_month;WHERE里的两个边界都是常量created_at上的索引正常工作DATE_FORMAT只出现在SELECT和GROUP BY里不影响过滤阶段的索引使用。这是一个很实用的思维转换过滤条件尽量用原始字段和常量格式化留到输出阶段。第三类改写是用计算列。MySQL 5.7 以后支持生成列如果某种格式化查询是高频刚需可以加一个虚拟生成列并建索引把函数计算的成本前置到写入时。ALTER TABLE orders ADD COLUMN stat_date DATE GENERATED ALWAYS AS (DATE(created_at)) VIRTUAL, ADD INDEX idx_stat_date (stat_date);虚拟生成列不占存储空间查询时按需计算但可以建索引算是一个折中方案。不过引入生成列会让表结构复杂化我一般只在高频报表表的场景下用普通业务表还是靠改写 SQL 解决。9. 常见问题与排查速查9.1 高频报错和返回 NULL 的原因日常遇到的日期函数问题大半都能归到几类原因上参数顺序写反、格式符用错、时区不一致、数据本身是脏的。现象可能原因排查方式TIMESTAMPDIFF结果为负两个参数顺序写反确认晚的时间放第二个参数STR_TO_DATE返回 NULL字符串格式与格式符不匹配单独SELECT该字符串逐个核对CONVERT_TZ返回 NULL命名时区未导入时区表改用08:00偏移写法验证时间差 8 小时会话时区与预期不符SELECT session.time_zone查看DATE_ADD结果日期比预期小目标月份缺该日期触发月末裁剪用LAST_DAY显式对齐月份分组数据偏少%m没带%Y跨年合并检查DATE_FORMAT格式串对NULL的处理要特别小心。NULL参与任何算术或函数运算结果都是NULL会一路静默传递到最终结果里。我做报表时习惯在关键字段外面套一层判空或者直接用COALESCE(expr, 默认值)避免一整列数据因为个别NULL而整体异常。9.2 主从复制相关的一个坑前面提过SYSDATE()在基于语句的复制下会导致主从数据不一致这里再补充另一个相关点NOW()在主从复制中是安全的因为二进制日志记录的是语句开始执行的时间从库重放时会用同样的时间值。但如果你的 SQL 里混用了NOW()和SYSDATE()比如INSERT INTO t (a, b) VALUES (NOW(), SYSDATE())从库重放时两个值可能不同主从数据就会出现细微差异。这类差异平时看不出来等到做数据核对时才会暴露排查成本极高。规避方法很直接整个项目约定统一使用NOW()或CURRENT_TIMESTAMP作为时间来源禁止在业务 SQL 中使用SYSDATE()。这个约定写进代码规范比事后排查划算得多。9.3 最后分享几个我常用的小技巧LAST_DAY(CURDATE() - INTERVAL 1 MONTH)一行拿到上月最后一天做月结报表时天天用。DATE_FORMAT(CURDATE(), %Y-%m-01)拿到本月 1 号配合INTERVAL能做各种自然月区间。想判断某天是不是当月最后一天用LAST_DAY(d) d就够了。要生成一个连续的日期序列但不想建日历表可以用数字辅助表配合DATE_ADD展开适合中小规模的对齐需求。关于日期时间的调试我的习惯是先把可疑表达式单独SELECT出来验证而不是直接塞进复杂 SQL 里跑。因为复杂 SQL 出问题时你分不清是逻辑错还是函数用错拆开验证能快速定位。另外在导入数据、调整时区、切换数据库版本这三类操作前后务必对同一批数据跑一次校验 SQL把关键日期字段的最大值、最小值、NULL 数量、格式分布都看一眼这是我踩过几次坑之后固定下来的流程比事后救火省心太多。