mysql中的日期与时间函数使用与优化

推荐在 DEFAULT 和 ON UPDATE 子句中统一使用 CURRENT_TIMESTAMP，因其符合 SQL 标准、兼容性更好、可被优化器识别为常量；NOW() 虽功能相似但语义弱且旧版本支持差。

MySQL 日期函数选 NOW() 还是 CURRENT_TIMESTAMP？

两者在大多数场景下行为一致，但语义和隐式类型转换有差异。NOW() 返回 DATETIME 类型，CURRENT_TIMESTAMP 是 SQL 标准函数，可被优化器识别为常量（尤其在分区表或生成列中）。如果建表时用 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP，后续插入不显式赋值会自动填充；而用 NOW() 作默认值虽可行，但部分旧版本 MySQL 会报错或触发警告。

• 推荐在 DEFAULT 和 ON UPDATE 子句中统一用 CURRENT_TIMESTAMP，兼容性更好
• NOW(3) 支持毫秒精度，CURRENT_TIMESTAMP(6) 可到微秒，但需字段定义匹配（如 TIMESTAMP(6)）
• 在 WHERE 条件里直接写 WHERE created_at > NOW() - INTERVAL 1 DAY 没问题，但若字段是 DATE 类型，应改用 CURDATE() 避免隐式转换

用 DATE()、DATE_FORMAT() 还是直接比较？

对 DATETIME 字段查“今天的数据”，别写 WHERE DATE(created_at) = CURDATE() —— 这会让索引失效。MySQL 无法对函数结果使用 B+ 树索引，DATE() 强制全表扫描。

• 正确写法：WHERE created_at >= CURDATE() AND created_at
• DATE_FORMAT(created_at, '%Y-%m') 适合展示，不适合查询条件；它返回字符串，无法走索引
• 若频繁按年月查询，考虑添加生成列：ALTER TABLE logs ADD COLUMN ym CHAR(7) STORED GENERATED ALWAYS AS (DATE_FORMAT(created_at, '%Y-%m'))，再给 ym 加索引

TIMESTAMP 和 DATETIME 字段选哪个？

不是只看“要不要时区转换”。TIMESTAMP 占 4 字节，范围是 1970-01-01 00:00:01 到 2038-01-19 03:14:07；DATETIME 占 8 字节，范围大得多（1000 到 9999 年），且存值不随系统时区变化。

• 业务时间线明确在 2038 年前、且需自动转时区（如日志记录 UTC 时间但前端显示本地时间），用 TIMESTAMP
• 存储生日、合同到期日、历史归档时间等固定时刻，必须用 DATETIME，否则跨时区读取会出错
• 注意：TIMESTAMP 默认开启 explicit_defaults_for_timestamp 后行为更可控；老版本未设该参数时，第一个 TIMESTAMP 列可能被自动设为 CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

批量更新时间字段为什么越来越慢？

常见于定时任务执行 UPDATE orders SET updated_at = NOW() WHERE status = 'pending'，随着数据量增长，哪怕有 status 索引，也可能因回表、锁竞争、二级索引维护开销变慢。

• 加 updated_at 到联合索引末尾，例如 INDEX idx_status_updated (status, updated_at)，让覆盖扫描成为可能
• 避免在高并发更新场景中依赖 NOW() —— 它每次调用都触发一次系统调用，不如用应用层传入统一时间戳（如 UPDATE ... SET updated_at = ?）
• 若只是标记“已处理”，考虑用无符号整型 processed_at INT UNSIGNED 存 Unix 时间戳，减少类型转换与索引体积

时区设置、字段类型选择、函数是否可索引——这三处不注意，性能问题往往在数据量上万后才暴露，但修复成本远高于设计阶段就定好规则。