1. 为什么 DATE_ADD 和 DATE_SUB 值得单独拿出来写做后端开发的几乎没有不跟日期时间打交道的。订单要算超时时间会员要算到期日报表要按天、按周、按月汇总这些场景全绕不开一个操作——在某个时间点上加一段间隔或者减一段间隔。MySQL 为此提供了两个看起来特别简单的函数DATE_ADD和DATE_SUB。我第一次用这两个函数的时候觉得这有什么好学的不就是DATE_ADD(now(), INTERVAL 1 DAY)吗加一天、减一天完事了。但随着项目越做越深我发现这里面能聊的东西远比想象中多INTERVAL 后面能接的单位有十几种每种单位的行为还不一样函数在字符串和日期类型之间怎么隐式转换DATE_ADD和直接写 INTERVAL到底有没有区别为什么有人在WHERE条件里用了函数以后索引就失效了等等。这篇博客就专门把这两个函数掰开揉碎讲清楚。我会从语法基础开始然后逐个拆解 INTERVAL 支持的单位结合实际业务场景给出用法示例再聊聊那些你不踩一次坑就不会注意到的边界问题。适合刚接触 MySQL 的初学者也适合写了好几年 SQL 但没系统整理过日期函数的老手——说不定哪一条就能帮你省下一个 Bug。2. 语法拆解与 INTERVAL 单位的完整对照2.1 函数签名与等价写法先看最基本的语法结构DATE_ADD(date, INTERVAL expr unit) DATE_SUB(date, INTERVAL expr unit)date是起始日期或时间可以是DATE、DATETIME、TIMESTAMP类型也可以是能隐式转换成日期的字符串。expr是间隔数值可以是整数、小数甚至是一个计算结果为数值的表达式。unit是间隔单位决定expr被解释成什么。举个例子SELECT DATE_ADD(2024-11-22 10:00:00, INTERVAL 30 MINUTE); -- 结果2024-11-22 10:30:00 SELECT DATE_SUB(2024-11-22 10:00:00, INTERVAL 3 HOUR); -- 结果2024-11-22 07:00:00值得注意的是MySQL 还支持一种等价的简写语法date INTERVAL expr unit和date - INTERVAL expr unit。比如上面第一个查询可以写成SELECT 2024-11-22 10:00:00 INTERVAL 30 MINUTE;这两个写法在绝大多数场景下结果完全一致DATE_ADD/DATE_SUB本质上是这种运算式写法的函数封装。不过有一个细节差异在ONLY_FULL_GROUP_BY这种 SQL 模式比较严格的环境里函数写法通常更稳因为解析器对表达式的处理路径略有不同虽然实际中很少遇到但知道这个差异对排查一些莫名其妙的报错有帮助。2.2 INTERVAL 支持的单位列表INTERVAL 单位是这套语法的核心变量。MySQL 官方文档里列了一大串我按使用频率整理成下面这张表单位说明常见用法示例MICROSECOND微秒加 500 微秒SECOND秒加 30 秒MINUTE分钟加 15 分钟HOUR小时加 6 小时DAY天加 7 天WEEK周加 2 周MONTH月加 3 个月QUARTER季度加 1 个季度YEAR年加 2 年SECOND_MICROSECOND秒 微秒10.500 表示 10 秒 500 微秒MINUTE_MICROSECOND分钟 微秒10:20.500MINUTE_SECOND分钟 秒10:20HOUR_MICROSECOND小时 微秒10:20:30.500HOUR_SECOND小时 秒10:20:30HOUR_MINUTE小时 分钟10:20DAY_MICROSECOND天 微秒1 10:20:30.500DAY_SECOND天 秒1 10:20:30DAY_MINUTE天 分钟1 10:20DAY_HOUR天 小时1 10YEAR_MONTH年 月2-3 表示 2 年 3 个月像HOUR_SECOND、DAY_MINUTE这类复合单位平时用得少但架不住有些历史遗留 SQL 里会见到。你只需要记住一点只要表达式格式和单位能对上号MySQL 就会按组合方式解析不会报错。2.3 expr 参数的类型陷阱expr不一定非得写死成一个数字。它可以是一个字段、一个子查询、一个计算结果。比如你有一个settings表存着不同业务的过期策略具体天数存在expire_days字段里SELECT order_id, DATE_ADD(create_time, INTERVAL expire_days DAY) AS expire_time FROM orders;这里expire_days是整数类型没问题。但要注意如果你的字段是字符串类型里面存的是3MySQL 通常也能转换可一旦字符串里有空格、中文或者其他非数字字符转换就可能失败或得到奇怪的结果。我建议在存储层就把这种字段定义成整数别让数据库在日期函数里做隐式类型转换。另外一个是小数问题。expr传小数时MySQL 会按四舍五入处理吗不会。它遵循的是“截断取整”规则只是不同单位的截断粒度不一样。比如INTERVAL 1.5 DAY实际效果等同于INTERVAL 1 DAY因为天这个单位只能按整数天处理。但如果用INTERVAL 1.5 SECOND_MICROSECOND就能精确表达 1 秒 500 微秒。这个差异需要心里有个底否则很容易在计算时间差时产生“差了一秒”的困惑。3. 实战中最常见的用法组合与业务场景3.1 生成连续日期序列报表系统里最经典的需求按天补全最近 30 天的数据没有记录的日期也要显示 0。做法是先生成一张日期维表核心就是DATE_ADD配合递归或者数字辅助表。假设我们用一个数字辅助表nums里面存着 0 到 9999SELECT DATE_ADD(2024-11-01, INTERVAL n.day_offset DAY) AS stat_date FROM nums n WHERE n.day_offset 30;这条 SQL 一次性生成从 2024 年 11 月 1 号开始的 30 天日期。之后再用LEFT JOIN把业务数据挂上去缺失日期自然就是 NULL再交给IFNULL补成 0 即可。这里有两点值得说明。第一起始日期我会建议用DATE类型而不是DATETIME因为后面再用DATE_FORMAT做分组展示时更省心。第二辅助表法比递归 CTE 在低版本 MySQL 里可移植性更好如果你的环境是 5.7 或更早WITH RECURSIVE根本用不了辅助表是最稳妥的解法。3.2 订单超时关单与状态机判断电商场景里下单后 15 分钟未支付要自动关单。多数实现是定时任务扫描SQL 写法类似这样UPDATE orders SET status cancelled WHERE status pending AND create_time DATE_SUB(NOW(), INTERVAL 15 MINUTE);这段逻辑等价于create_time 15 分钟 NOW()也就是说订单创建时间已经落在当前时间往前 15 分钟之前了。用DATE_SUB把 NOW() 往前拨比直接写create_time NOW() - INTERVAL 15 MINUTE可读性更好。这里有一个性能层面的常见问题在create_time上有索引的情况下WHERE create_time DATE_SUB(NOW(), INTERVAL 15 MINUTE)是可以走索引的因为DATE_SUB是在常量上做计算而不是在字段上做计算。但如果你改成WHERE DATE_ADD(create_time, INTERVAL 15 MINUTE) NOW()那索引就会失效因为每一行的字段都先要做一次函数运算才能跟NOW()比较。这是使用日期函数时特别需要留意的原则——把函数放在字段这一侧还是放在常量这一侧直接决定执行计划好不好看。3.3 会员到期与订阅续费提醒另一个高频场景是会员体系。给用户算到期时间往往是从支付成功时间往后加一个月、一个季度或者一年SELECT user_id, DATE_ADD(pay_time, INTERVAL 1 MONTH) AS expire_time FROM user_payments WHERE pay_type monthly;这里就牵扯出一个有意思的细节INTERVAL 1 MONTH的加月行为跟自然月的概念并不完全一致。如果pay_time是 1 月 31 号加一个月应该是 2 月 31 号——可 2 月没有 31 号。MySQL 的处理方式是如果加完后的日期超出了当月最大天数就自动截断到该月的最后一天。所以 1 月 31 号加一个月得到 2 月 28 号或 29 号再加一个月会得到 3 月 31 号吗不会因为第二次加月是从 2 月 28 号开始算的加一个月会得到 3 月 28 号。这种“月末漂移”问题在财务系统的账期计算里是大坑。如果业务要求必须保持“每个月的同一天”这种语义你需要自己在应用层额外处理靠DATE_ADD是解决不了的。我会在后面的边界问题章节再展开。3.4 报表周、季度、年维度的聚合做报表的时候经常需要按周或按季度分组。周一开始作为一周起点的话可以用DATE_SUB把任意日期归一化到本周一SELECT DATE_SUB(stat_date, INTERVAL WEEKDAY(stat_date) DAY) AS week_start, SUM(amount) AS total_amount FROM daily_sales GROUP BY week_start;WEEKDAY函数返回周一为 0、周日为 6从当前日期往前减对应天数就能得到这周周一。季度分组类似可以用QUARTER函数直接取季度编号但如果需要季度的起始日期可以结合DATE_ADD从年份起始日推算。这些用法都非常依赖对 INTERVAL 单位和函数组合的理解。单独看每个函数都很简单组合起来才能解决实际问题。4. 容易翻车的边界情况与排查思路4.1 月末日期与闰年的隐性规则前面已经提到月末漂移问题这里给出一个具体例子来验证。我们分别执行SELECT DATE_ADD(2024-01-31, INTERVAL 1 MONTH); -- 结果2024-02-29因为 2024 是闰年 SELECT DATE_ADD(2024-01-31, INTERVAL 2 MONTH); -- 结果2024-03-31 SELECT DATE_ADD(2024-02-29, INTERVAL 1 MONTH); -- 结果2024-03-29注意第二和第三个结果的区别。2024-01-31加两个月是2024-03-31但因为中间经过了一个被截断的 2 月所以第二次加月是从 2 月 29 号起算得到 3 月 29 号。如果你的业务每次加月都基于同一原始日期最好每次都基于最初的时间点计算不要在上一次结果上继续累加。比如-- 推荐基于原始日期直接加 N 个月 DATE_ADD(2024-01-31, INTERVAL 2 MONTH) -- 不推荐分两次累加 DATE_ADD(DATE_ADD(2024-01-31, INTERVAL 1 MONTH), INTERVAL 1 MONTH)在季末、年末这类时间点上YEAR、QUARTER等单位也存在类似规则。这里不展开每一个核心结论就是MySQL 的日期加单位操作是“尽量保持同一天超出就截断”的语义不是“满 N 个自然月后精确到同一天”的语义。4.2 类型混合导致的时间部分丢失DATE_ADD的返回类型会跟随输入类型变化。如果第一个参数是DATE类型结果通常也是DATE时间部分会被丢掉如果第一个参数是DATETIME或TIMESTAMP结果会保留时间部分。这个规则大多数人知道但实际踩坑的地方往往在字符串上。比如SELECT DATE_ADD(2024-11-22, INTERVAL 1 DAY); -- 结果2024-11-23 SELECT DATE_ADD(2024-11-22 10:30:00, INTERVAL 1 DAY); -- 结果2024-11-23 10:30:00 SELECT DATE_ADD(2024-11-22 10:30:00, INTERVAL 1 HOUR); -- 结果2024-11-22 11:30:00如果你期望一个带时间的字符串在加天之后保留时间必须保证字符串能被识别为完整的DATETIME格式。如果字符串省略了时间部分MySQL 会把时间部分默认成00:00:00而返回时会不会带时间则由解析出的类型决定。关于这一点我见过有人在统计脚本里因为日期字符串没带时间导致加了一天之后时间信息被抹掉后面所有按小时维度的统计全部错位。排查起来特别隐蔽。4.3 负数 expr 与 DATE_SUB 的等价关系DATE_ADD的第二个参数支持负数这就导致了一个有趣的事实DATE_ADD(date, INTERVAL 1 DAY)等价于DATE_SUB(date, INTERVAL -1 DAY)。倒过来也一样。这个特性可以用但不建议在业务代码里到处混用。团队协作时语义混乱比性能问题更致命。如果一个 SQL 文件里同时出现DATE_SUB(..., INTERVAL -3 DAY)和DATE_ADD(..., INTERVAL 3 DAY)后面维护的人大概率要停下来想半天。我个人的习惯是加就是DATE_ADD加正数减就是DATE_SUB减正数负号只在少数动态拼 SQL 的场景才会用。不过有一种情况负数特别方便——当你从配置表里读到的是一个可正可负的偏移量时SELECT user_id, DATE_ADD(base_time, INTERVAL offset_days DAY) AS target_time FROM user_rules;这样一条语句同时覆盖了加和减两种情况不需要 CASE 分支。4.4 时区与 TIMESTAMP 的隐藏差异TIMESTAMP类型在存储时会根据会话时区做转换而DATETIME不会。这意味着如果数据库连接串上的时区设置和你应用服务器的时区不一致DATE_ADD(NOW(), INTERVAL 1 DAY)这类查询在不同环境本地、测试、生产里得到的结果可能出现小时级偏差。MySQL 8.0 默认的时区往往是系统时区但连接池可能会设置connectionTimeZone之类的参数。排查这类问题确认一下当前会话时区SELECT session.time_zone;如果业务对时间精度敏感我建议所有时间字段统一使用DATETIME并且在应用层统一传 UTC 时间或带时区的时间字符串。日期函数本身没有错错的是不同类型在不同时区下的隐式行为这个锅经常被甩到DATE_ADD头上。5. 与其他日期时间函数配合的进阶玩法5.1 与 LAST_DAY 配合做月初月末计算LAST_DAY(date)能返回某个月的最后一天。配合DATE_ADD可以轻松拿到月初SELECT LAST_DAY(2024-11-22) AS month_end, DATE_ADD(LAST_DAY(2024-11-22), INTERVAL 1 DAY) AS next_month_start;这个组合在自然月账单、房租计算、订阅扣费通知里很常见。更骚一点的玩法是计算某个月的天数SELECT DAY(LAST_DAY(2024-11-22)) AS days_in_month;不过严格来说计算天数用DAY(LAST_DAY(...))有点绕直接DATEDIFF(DATE_ADD(LAST_DAY(...), INTERVAL 1 DAY), DATE_SUB(LAST_DAY(...), INTERVAL 1 MONTH))也能做但可读性差很多。我在生产环境里更倾向于直接DAY(LAST_DAY(...))一行搞定。5.2 与 DATE_FORMAT 组合实现时间窗口日期函数最怕的是在字段上做格式化然后分组因为会导致索引失效。但如果只是展示层需要配合使用完全没问题。比如统计每天 24 小时段的订单量SELECT DATE_FORMAT(create_time, %Y-%m-%d %H:00) AS hour_bucket, COUNT(*) AS order_cnt FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 24 HOUR) GROUP BY hour_bucket;WHERE条件里对常量用DATE_SUB保持索引可用SELECT里用DATE_FORMAT做展示格式化这是比较推荐的姿势。如果反过来在WHERE里写DATE_FORMAT(create_time, %Y-%m-%d) 2024-11-22那么索引基本就废了。5.3 与 UNIX_TIMESTAMP 互相转换有些业务会把时间戳存在INT字段里展示时需要换算成可读时间或者反过来。DATE_ADD可以配合FROM_UNIXTIME来实现灵活的时间戳运算SELECT FROM_UNIXTIME(ts) AS readable_time, DATE_ADD(FROM_UNIXTIME(ts), INTERVAL 1 HOUR) AS one_hour_later FROM event_log;这个组合在日志系统里用得特别多。不过从性能角度如果event_log表特别大尽量避免在WHERE里对每一行的ts做转换之后再过滤更好的方式是直接把时间戳范围算好用纯数值比较。比如要查最近 24 小时的日志先把边界时间戳算出来SET end_ts UNIX_TIMESTAMP(NOW()); SET start_ts UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 24 HOUR)); SELECT * FROM event_log WHERE ts BETWEEN start_ts AND end_ts;这种写法既用上了DATE_SUB又不会在表数据量大的时候拖垮查询。5.4 递归 CTE 生成日期维度表上面提到过辅助表法这里再补充一种现代写法MySQL 8.0 可以使用WITH RECURSIVE生成日期序列本质上还是在用DATE_ADDWITH RECURSIVE date_series AS ( SELECT 2024-11-01 AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM date_series WHERE d 2024-11-30 ) SELECT * FROM date_series;递归 CTE 的可读性比辅助表好很多但要注意递归深度限制。MySQL 默认的cte_max_recursion_depth是 1000如果一次要生成 365 天的序列需要提前调大SET SESSION cte_max_recursion_depth 1000;顺便提一句如果只是生成固定长度的连续数字用通用表表达式加上DATE_ADD这套组合几乎可以解决所有日期维度生成需求。6. 索引、执行计划与函数使用位置的提醒数据库里很多“慢查询”问题根源不在函数本身而在函数写在了字段这一侧。DATE_ADD和DATE_SUB本身没有错但你要注意用法。看下面这条SELECT * FROM orders WHERE DATE_ADD(create_time, INTERVAL 1 DAY) NOW();这个是“反向”用法。优化器需要对orders表每一行的create_time都执行一次DATE_ADD运算后再和NOW()比较。哪怕create_time上建了二级索引也派不上用场因为在比较之前字段已经被函数包装过了。这种情况下你只能退化成全表扫描。正确的等价写法是SELECT * FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 1 DAY);把函数挪到常量这边create_time直接参与范围比较索引就能正常工作。类似的还有DATE_SUB(create_time, INTERVAL 1 DAY) NOW()这种如果只是对常量做DATE_ADD或DATE_SUB优化器计算的成本极低完全不用担心。另外一个优化器相关但容易被忽略的点BETWEEN和/在日期范围查询中并不完全等价尤其是涉及DATETIME的毫秒级别时。比如WHERE create_time BETWEEN 2024-11-22 10:00:00 AND 2024-11-22 10:30:00这个查询包含10:30:00整点那一瞬间的数据但不包含10:30:00.500的数据因为BETWEEN是闭区间精确匹配边界值。如果你希望严格排除右边界用和更合适。用DATE_SUB和DATE_ADD动态计算边界时也要注意边界值的第二精度问题。比如要查“最近 1 小时”的订单如果写成create_time NOW() - INTERVAL 1 HOURNOW()带当前毫秒数边界会精确到毫秒。这通常没问题但如果你后续要做缓存或跟其他系统对账边界值的确定性就很重要了。我的做法是把边界时间先取整到秒再统一比较避免排查时出现莫名其妙的“差几毫秒”。7. 函数使用习惯与团队规范建议代码评审的时候我经常看到同一条日期逻辑在不同模块里有不同写法。有人习惯写DATE_ADD有人习惯写 INTERVAL还有人喜欢先算出时间戳再用数值加减。每一种都对但在同一个项目里混搭就会增加维护成本。这里分享几条我自己一直在用的经验第一统一使用函数语法。虽然date INTERVAL 1 DAY更简短但DATE_ADD的可读性更强尤其在表达式比较复杂时。函数名本身就是语义扫描代码时一眼就能看出来“这里在加一个时间间隔”。第二字段侧绝不套函数。这条是最重要的性能红线。凡是能在常量侧完成的日期运算绝不套在字段侧。这样既保索引也让执行计划更容易被优化器把控。第三月份加法的业务语义要写在文档里。如果系统里存在“每个月的同一天”这种需求请在接口层单独处理不要直接依赖DATE_ADD的月末截断行为。否则过了几个月就会有人踩到“1 月 31 号续费的用户到期日不对”的坑。第四对结果做类型检查。在把DATE_ADD的返回值插入到另一个表之前先确认目标字段是DATE、DATETIME还是TIMESTAMP。如果源数据是字符串先显式转换比如用CAST或STR_TO_DATE避免隐式类型转换埋雷。第五测试用例里专门覆盖月末。不管你的业务逻辑是加天、加月还是加年一定要把 1 月 31 号、4 月 30 号、闰年 2 月 29 号这些日期写进单元测试。日期函数的行为在常规日期上一点问题都没有全在边界日期上暴露问题。我这里有一个相对稳妥的“月末安全加法”思路如果需要加 N 个月并且希望结果日期的日分量尽量靠近原日期的日分量可以这么写-- 先取目标月份的最后一天和原日期在当月的位置 SELECT CASE WHEN DAY(2024-01-31) DAY(LAST_DAY(DATE_ADD(2024-01-31, INTERVAL 1 MONTH))) THEN DATE_ADD(2024-01-31, INTERVAL 1 MONTH) ELSE DATE_SUB(LAST_DAY(DATE_ADD(2024-01-31, INTERVAL 1 MONTH)), INTERVAL DAY(LAST_DAY(DATE_ADD(2024-01-31, INTERVAL 2 MONTH))) - DAY(2024-01-31) DAY) END;这段逻辑会比较原日期的日分量和目标月份最大天数如果目标月份天数不足就倒退到目标月最后一天再回退相应差额。不过说实话生产环境里我见过的大多数团队最终都不会走这种复杂 SQL而是直接取出时间戳后在应用层用第三方时间库计算。我比较认同这种取舍——日期逻辑放在应用层往往更可控数据库层只做范围过滤和简单日期生成。对于必须放在 SQL 里的场景尽量限定在“加固定天数、小时数”这类无歧义的操作上。MySQL 提供的DATE_ADD和DATE_SUB本质上就是让时间运算从“人肉心算”变成“SQL 一步到位”的工具。理解它的规则不在它身上寄托它本不具备的复杂语义是正确使用的前提。我这些年踩过的坑基本都集中在月末截断和函数位置导致索引失效这两类问题上也因此在代码评审时养成了凡是看到日期函数都要多看两眼的习惯。函数没有智商但用函数的人要有。
1. 为什么 DATE_ADD 和 DATE_SUB 值得单独拿出来写做后端开发的几乎没有不跟日期时间打交道的。订单要算超时时间会员要算到期日报表要按天、按周、按月汇总这些场景全绕不开一个操作——在某个时间点上加一段间隔或者减一段间隔。MySQL 为此提供了两个看起来特别简单的函数DATE_ADD和DATE_SUB。我第一次用这两个函数的时候觉得这有什么好学的不就是DATE_ADD(now(), INTERVAL 1 DAY)吗加一天、减一天完事了。但随着项目越做越深我发现这里面能聊的东西远比想象中多INTERVAL 后面能接的单位有十几种每种单位的行为还不一样函数在字符串和日期类型之间怎么隐式转换DATE_ADD和直接写 INTERVAL到底有没有区别为什么有人在WHERE条件里用了函数以后索引就失效了等等。这篇博客就专门把这两个函数掰开揉碎讲清楚。我会从语法基础开始然后逐个拆解 INTERVAL 支持的单位结合实际业务场景给出用法示例再聊聊那些你不踩一次坑就不会注意到的边界问题。适合刚接触 MySQL 的初学者也适合写了好几年 SQL 但没系统整理过日期函数的老手——说不定哪一条就能帮你省下一个 Bug。2. 语法拆解与 INTERVAL 单位的完整对照2.1 函数签名与等价写法先看最基本的语法结构DATE_ADD(date, INTERVAL expr unit) DATE_SUB(date, INTERVAL expr unit)date是起始日期或时间可以是DATE、DATETIME、TIMESTAMP类型也可以是能隐式转换成日期的字符串。expr是间隔数值可以是整数、小数甚至是一个计算结果为数值的表达式。unit是间隔单位决定expr被解释成什么。举个例子SELECT DATE_ADD(2024-11-22 10:00:00, INTERVAL 30 MINUTE); -- 结果2024-11-22 10:30:00 SELECT DATE_SUB(2024-11-22 10:00:00, INTERVAL 3 HOUR); -- 结果2024-11-22 07:00:00值得注意的是MySQL 还支持一种等价的简写语法date INTERVAL expr unit和date - INTERVAL expr unit。比如上面第一个查询可以写成SELECT 2024-11-22 10:00:00 INTERVAL 30 MINUTE;这两个写法在绝大多数场景下结果完全一致DATE_ADD/DATE_SUB本质上是这种运算式写法的函数封装。不过有一个细节差异在ONLY_FULL_GROUP_BY这种 SQL 模式比较严格的环境里函数写法通常更稳因为解析器对表达式的处理路径略有不同虽然实际中很少遇到但知道这个差异对排查一些莫名其妙的报错有帮助。2.2 INTERVAL 支持的单位列表INTERVAL 单位是这套语法的核心变量。MySQL 官方文档里列了一大串我按使用频率整理成下面这张表单位说明常见用法示例MICROSECOND微秒加 500 微秒SECOND秒加 30 秒MINUTE分钟加 15 分钟HOUR小时加 6 小时DAY天加 7 天WEEK周加 2 周MONTH月加 3 个月QUARTER季度加 1 个季度YEAR年加 2 年SECOND_MICROSECOND秒 微秒10.500 表示 10 秒 500 微秒MINUTE_MICROSECOND分钟 微秒10:20.500MINUTE_SECOND分钟 秒10:20HOUR_MICROSECOND小时 微秒10:20:30.500HOUR_SECOND小时 秒10:20:30HOUR_MINUTE小时 分钟10:20DAY_MICROSECOND天 微秒1 10:20:30.500DAY_SECOND天 秒1 10:20:30DAY_MINUTE天 分钟1 10:20DAY_HOUR天 小时1 10YEAR_MONTH年 月2-3 表示 2 年 3 个月像HOUR_SECOND、DAY_MINUTE这类复合单位平时用得少但架不住有些历史遗留 SQL 里会见到。你只需要记住一点只要表达式格式和单位能对上号MySQL 就会按组合方式解析不会报错。2.3 expr 参数的类型陷阱expr不一定非得写死成一个数字。它可以是一个字段、一个子查询、一个计算结果。比如你有一个settings表存着不同业务的过期策略具体天数存在expire_days字段里SELECT order_id, DATE_ADD(create_time, INTERVAL expire_days DAY) AS expire_time FROM orders;这里expire_days是整数类型没问题。但要注意如果你的字段是字符串类型里面存的是3MySQL 通常也能转换可一旦字符串里有空格、中文或者其他非数字字符转换就可能失败或得到奇怪的结果。我建议在存储层就把这种字段定义成整数别让数据库在日期函数里做隐式类型转换。另外一个是小数问题。expr传小数时MySQL 会按四舍五入处理吗不会。它遵循的是“截断取整”规则只是不同单位的截断粒度不一样。比如INTERVAL 1.5 DAY实际效果等同于INTERVAL 1 DAY因为天这个单位只能按整数天处理。但如果用INTERVAL 1.5 SECOND_MICROSECOND就能精确表达 1 秒 500 微秒。这个差异需要心里有个底否则很容易在计算时间差时产生“差了一秒”的困惑。3. 实战中最常见的用法组合与业务场景3.1 生成连续日期序列报表系统里最经典的需求按天补全最近 30 天的数据没有记录的日期也要显示 0。做法是先生成一张日期维表核心就是DATE_ADD配合递归或者数字辅助表。假设我们用一个数字辅助表nums里面存着 0 到 9999SELECT DATE_ADD(2024-11-01, INTERVAL n.day_offset DAY) AS stat_date FROM nums n WHERE n.day_offset 30;这条 SQL 一次性生成从 2024 年 11 月 1 号开始的 30 天日期。之后再用LEFT JOIN把业务数据挂上去缺失日期自然就是 NULL再交给IFNULL补成 0 即可。这里有两点值得说明。第一起始日期我会建议用DATE类型而不是DATETIME因为后面再用DATE_FORMAT做分组展示时更省心。第二辅助表法比递归 CTE 在低版本 MySQL 里可移植性更好如果你的环境是 5.7 或更早WITH RECURSIVE根本用不了辅助表是最稳妥的解法。3.2 订单超时关单与状态机判断电商场景里下单后 15 分钟未支付要自动关单。多数实现是定时任务扫描SQL 写法类似这样UPDATE orders SET status cancelled WHERE status pending AND create_time DATE_SUB(NOW(), INTERVAL 15 MINUTE);这段逻辑等价于create_time 15 分钟 NOW()也就是说订单创建时间已经落在当前时间往前 15 分钟之前了。用DATE_SUB把 NOW() 往前拨比直接写create_time NOW() - INTERVAL 15 MINUTE可读性更好。这里有一个性能层面的常见问题在create_time上有索引的情况下WHERE create_time DATE_SUB(NOW(), INTERVAL 15 MINUTE)是可以走索引的因为DATE_SUB是在常量上做计算而不是在字段上做计算。但如果你改成WHERE DATE_ADD(create_time, INTERVAL 15 MINUTE) NOW()那索引就会失效因为每一行的字段都先要做一次函数运算才能跟NOW()比较。这是使用日期函数时特别需要留意的原则——把函数放在字段这一侧还是放在常量这一侧直接决定执行计划好不好看。3.3 会员到期与订阅续费提醒另一个高频场景是会员体系。给用户算到期时间往往是从支付成功时间往后加一个月、一个季度或者一年SELECT user_id, DATE_ADD(pay_time, INTERVAL 1 MONTH) AS expire_time FROM user_payments WHERE pay_type monthly;这里就牵扯出一个有意思的细节INTERVAL 1 MONTH的加月行为跟自然月的概念并不完全一致。如果pay_time是 1 月 31 号加一个月应该是 2 月 31 号——可 2 月没有 31 号。MySQL 的处理方式是如果加完后的日期超出了当月最大天数就自动截断到该月的最后一天。所以 1 月 31 号加一个月得到 2 月 28 号或 29 号再加一个月会得到 3 月 31 号吗不会因为第二次加月是从 2 月 28 号开始算的加一个月会得到 3 月 28 号。这种“月末漂移”问题在财务系统的账期计算里是大坑。如果业务要求必须保持“每个月的同一天”这种语义你需要自己在应用层额外处理靠DATE_ADD是解决不了的。我会在后面的边界问题章节再展开。3.4 报表周、季度、年维度的聚合做报表的时候经常需要按周或按季度分组。周一开始作为一周起点的话可以用DATE_SUB把任意日期归一化到本周一SELECT DATE_SUB(stat_date, INTERVAL WEEKDAY(stat_date) DAY) AS week_start, SUM(amount) AS total_amount FROM daily_sales GROUP BY week_start;WEEKDAY函数返回周一为 0、周日为 6从当前日期往前减对应天数就能得到这周周一。季度分组类似可以用QUARTER函数直接取季度编号但如果需要季度的起始日期可以结合DATE_ADD从年份起始日推算。这些用法都非常依赖对 INTERVAL 单位和函数组合的理解。单独看每个函数都很简单组合起来才能解决实际问题。4. 容易翻车的边界情况与排查思路4.1 月末日期与闰年的隐性规则前面已经提到月末漂移问题这里给出一个具体例子来验证。我们分别执行SELECT DATE_ADD(2024-01-31, INTERVAL 1 MONTH); -- 结果2024-02-29因为 2024 是闰年 SELECT DATE_ADD(2024-01-31, INTERVAL 2 MONTH); -- 结果2024-03-31 SELECT DATE_ADD(2024-02-29, INTERVAL 1 MONTH); -- 结果2024-03-29注意第二和第三个结果的区别。2024-01-31加两个月是2024-03-31但因为中间经过了一个被截断的 2 月所以第二次加月是从 2 月 29 号起算得到 3 月 29 号。如果你的业务每次加月都基于同一原始日期最好每次都基于最初的时间点计算不要在上一次结果上继续累加。比如-- 推荐基于原始日期直接加 N 个月 DATE_ADD(2024-01-31, INTERVAL 2 MONTH) -- 不推荐分两次累加 DATE_ADD(DATE_ADD(2024-01-31, INTERVAL 1 MONTH), INTERVAL 1 MONTH)在季末、年末这类时间点上YEAR、QUARTER等单位也存在类似规则。这里不展开每一个核心结论就是MySQL 的日期加单位操作是“尽量保持同一天超出就截断”的语义不是“满 N 个自然月后精确到同一天”的语义。4.2 类型混合导致的时间部分丢失DATE_ADD的返回类型会跟随输入类型变化。如果第一个参数是DATE类型结果通常也是DATE时间部分会被丢掉如果第一个参数是DATETIME或TIMESTAMP结果会保留时间部分。这个规则大多数人知道但实际踩坑的地方往往在字符串上。比如SELECT DATE_ADD(2024-11-22, INTERVAL 1 DAY); -- 结果2024-11-23 SELECT DATE_ADD(2024-11-22 10:30:00, INTERVAL 1 DAY); -- 结果2024-11-23 10:30:00 SELECT DATE_ADD(2024-11-22 10:30:00, INTERVAL 1 HOUR); -- 结果2024-11-22 11:30:00如果你期望一个带时间的字符串在加天之后保留时间必须保证字符串能被识别为完整的DATETIME格式。如果字符串省略了时间部分MySQL 会把时间部分默认成00:00:00而返回时会不会带时间则由解析出的类型决定。关于这一点我见过有人在统计脚本里因为日期字符串没带时间导致加了一天之后时间信息被抹掉后面所有按小时维度的统计全部错位。排查起来特别隐蔽。4.3 负数 expr 与 DATE_SUB 的等价关系DATE_ADD的第二个参数支持负数这就导致了一个有趣的事实DATE_ADD(date, INTERVAL 1 DAY)等价于DATE_SUB(date, INTERVAL -1 DAY)。倒过来也一样。这个特性可以用但不建议在业务代码里到处混用。团队协作时语义混乱比性能问题更致命。如果一个 SQL 文件里同时出现DATE_SUB(..., INTERVAL -3 DAY)和DATE_ADD(..., INTERVAL 3 DAY)后面维护的人大概率要停下来想半天。我个人的习惯是加就是DATE_ADD加正数减就是DATE_SUB减正数负号只在少数动态拼 SQL 的场景才会用。不过有一种情况负数特别方便——当你从配置表里读到的是一个可正可负的偏移量时SELECT user_id, DATE_ADD(base_time, INTERVAL offset_days DAY) AS target_time FROM user_rules;这样一条语句同时覆盖了加和减两种情况不需要 CASE 分支。4.4 时区与 TIMESTAMP 的隐藏差异TIMESTAMP类型在存储时会根据会话时区做转换而DATETIME不会。这意味着如果数据库连接串上的时区设置和你应用服务器的时区不一致DATE_ADD(NOW(), INTERVAL 1 DAY)这类查询在不同环境本地、测试、生产里得到的结果可能出现小时级偏差。MySQL 8.0 默认的时区往往是系统时区但连接池可能会设置connectionTimeZone之类的参数。排查这类问题确认一下当前会话时区SELECT session.time_zone;如果业务对时间精度敏感我建议所有时间字段统一使用DATETIME并且在应用层统一传 UTC 时间或带时区的时间字符串。日期函数本身没有错错的是不同类型在不同时区下的隐式行为这个锅经常被甩到DATE_ADD头上。5. 与其他日期时间函数配合的进阶玩法5.1 与 LAST_DAY 配合做月初月末计算LAST_DAY(date)能返回某个月的最后一天。配合DATE_ADD可以轻松拿到月初SELECT LAST_DAY(2024-11-22) AS month_end, DATE_ADD(LAST_DAY(2024-11-22), INTERVAL 1 DAY) AS next_month_start;这个组合在自然月账单、房租计算、订阅扣费通知里很常见。更骚一点的玩法是计算某个月的天数SELECT DAY(LAST_DAY(2024-11-22)) AS days_in_month;不过严格来说计算天数用DAY(LAST_DAY(...))有点绕直接DATEDIFF(DATE_ADD(LAST_DAY(...), INTERVAL 1 DAY), DATE_SUB(LAST_DAY(...), INTERVAL 1 MONTH))也能做但可读性差很多。我在生产环境里更倾向于直接DAY(LAST_DAY(...))一行搞定。5.2 与 DATE_FORMAT 组合实现时间窗口日期函数最怕的是在字段上做格式化然后分组因为会导致索引失效。但如果只是展示层需要配合使用完全没问题。比如统计每天 24 小时段的订单量SELECT DATE_FORMAT(create_time, %Y-%m-%d %H:00) AS hour_bucket, COUNT(*) AS order_cnt FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 24 HOUR) GROUP BY hour_bucket;WHERE条件里对常量用DATE_SUB保持索引可用SELECT里用DATE_FORMAT做展示格式化这是比较推荐的姿势。如果反过来在WHERE里写DATE_FORMAT(create_time, %Y-%m-%d) 2024-11-22那么索引基本就废了。5.3 与 UNIX_TIMESTAMP 互相转换有些业务会把时间戳存在INT字段里展示时需要换算成可读时间或者反过来。DATE_ADD可以配合FROM_UNIXTIME来实现灵活的时间戳运算SELECT FROM_UNIXTIME(ts) AS readable_time, DATE_ADD(FROM_UNIXTIME(ts), INTERVAL 1 HOUR) AS one_hour_later FROM event_log;这个组合在日志系统里用得特别多。不过从性能角度如果event_log表特别大尽量避免在WHERE里对每一行的ts做转换之后再过滤更好的方式是直接把时间戳范围算好用纯数值比较。比如要查最近 24 小时的日志先把边界时间戳算出来SET end_ts UNIX_TIMESTAMP(NOW()); SET start_ts UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 24 HOUR)); SELECT * FROM event_log WHERE ts BETWEEN start_ts AND end_ts;这种写法既用上了DATE_SUB又不会在表数据量大的时候拖垮查询。5.4 递归 CTE 生成日期维度表上面提到过辅助表法这里再补充一种现代写法MySQL 8.0 可以使用WITH RECURSIVE生成日期序列本质上还是在用DATE_ADDWITH RECURSIVE date_series AS ( SELECT 2024-11-01 AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM date_series WHERE d 2024-11-30 ) SELECT * FROM date_series;递归 CTE 的可读性比辅助表好很多但要注意递归深度限制。MySQL 默认的cte_max_recursion_depth是 1000如果一次要生成 365 天的序列需要提前调大SET SESSION cte_max_recursion_depth 1000;顺便提一句如果只是生成固定长度的连续数字用通用表表达式加上DATE_ADD这套组合几乎可以解决所有日期维度生成需求。6. 索引、执行计划与函数使用位置的提醒数据库里很多“慢查询”问题根源不在函数本身而在函数写在了字段这一侧。DATE_ADD和DATE_SUB本身没有错但你要注意用法。看下面这条SELECT * FROM orders WHERE DATE_ADD(create_time, INTERVAL 1 DAY) NOW();这个是“反向”用法。优化器需要对orders表每一行的create_time都执行一次DATE_ADD运算后再和NOW()比较。哪怕create_time上建了二级索引也派不上用场因为在比较之前字段已经被函数包装过了。这种情况下你只能退化成全表扫描。正确的等价写法是SELECT * FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 1 DAY);把函数挪到常量这边create_time直接参与范围比较索引就能正常工作。类似的还有DATE_SUB(create_time, INTERVAL 1 DAY) NOW()这种如果只是对常量做DATE_ADD或DATE_SUB优化器计算的成本极低完全不用担心。另外一个优化器相关但容易被忽略的点BETWEEN和/在日期范围查询中并不完全等价尤其是涉及DATETIME的毫秒级别时。比如WHERE create_time BETWEEN 2024-11-22 10:00:00 AND 2024-11-22 10:30:00这个查询包含10:30:00整点那一瞬间的数据但不包含10:30:00.500的数据因为BETWEEN是闭区间精确匹配边界值。如果你希望严格排除右边界用和更合适。用DATE_SUB和DATE_ADD动态计算边界时也要注意边界值的第二精度问题。比如要查“最近 1 小时”的订单如果写成create_time NOW() - INTERVAL 1 HOURNOW()带当前毫秒数边界会精确到毫秒。这通常没问题但如果你后续要做缓存或跟其他系统对账边界值的确定性就很重要了。我的做法是把边界时间先取整到秒再统一比较避免排查时出现莫名其妙的“差几毫秒”。7. 函数使用习惯与团队规范建议代码评审的时候我经常看到同一条日期逻辑在不同模块里有不同写法。有人习惯写DATE_ADD有人习惯写 INTERVAL还有人喜欢先算出时间戳再用数值加减。每一种都对但在同一个项目里混搭就会增加维护成本。这里分享几条我自己一直在用的经验第一统一使用函数语法。虽然date INTERVAL 1 DAY更简短但DATE_ADD的可读性更强尤其在表达式比较复杂时。函数名本身就是语义扫描代码时一眼就能看出来“这里在加一个时间间隔”。第二字段侧绝不套函数。这条是最重要的性能红线。凡是能在常量侧完成的日期运算绝不套在字段侧。这样既保索引也让执行计划更容易被优化器把控。第三月份加法的业务语义要写在文档里。如果系统里存在“每个月的同一天”这种需求请在接口层单独处理不要直接依赖DATE_ADD的月末截断行为。否则过了几个月就会有人踩到“1 月 31 号续费的用户到期日不对”的坑。第四对结果做类型检查。在把DATE_ADD的返回值插入到另一个表之前先确认目标字段是DATE、DATETIME还是TIMESTAMP。如果源数据是字符串先显式转换比如用CAST或STR_TO_DATE避免隐式类型转换埋雷。第五测试用例里专门覆盖月末。不管你的业务逻辑是加天、加月还是加年一定要把 1 月 31 号、4 月 30 号、闰年 2 月 29 号这些日期写进单元测试。日期函数的行为在常规日期上一点问题都没有全在边界日期上暴露问题。我这里有一个相对稳妥的“月末安全加法”思路如果需要加 N 个月并且希望结果日期的日分量尽量靠近原日期的日分量可以这么写-- 先取目标月份的最后一天和原日期在当月的位置 SELECT CASE WHEN DAY(2024-01-31) DAY(LAST_DAY(DATE_ADD(2024-01-31, INTERVAL 1 MONTH))) THEN DATE_ADD(2024-01-31, INTERVAL 1 MONTH) ELSE DATE_SUB(LAST_DAY(DATE_ADD(2024-01-31, INTERVAL 1 MONTH)), INTERVAL DAY(LAST_DAY(DATE_ADD(2024-01-31, INTERVAL 2 MONTH))) - DAY(2024-01-31) DAY) END;这段逻辑会比较原日期的日分量和目标月份最大天数如果目标月份天数不足就倒退到目标月最后一天再回退相应差额。不过说实话生产环境里我见过的大多数团队最终都不会走这种复杂 SQL而是直接取出时间戳后在应用层用第三方时间库计算。我比较认同这种取舍——日期逻辑放在应用层往往更可控数据库层只做范围过滤和简单日期生成。对于必须放在 SQL 里的场景尽量限定在“加固定天数、小时数”这类无歧义的操作上。MySQL 提供的DATE_ADD和DATE_SUB本质上就是让时间运算从“人肉心算”变成“SQL 一步到位”的工具。理解它的规则不在它身上寄托它本不具备的复杂语义是正确使用的前提。我这些年踩过的坑基本都集中在月末截断和函数位置导致索引失效这两类问题上也因此在代码评审时养成了凡是看到日期函数都要多看两眼的习惯。函数没有智商但用函数的人要有。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
