MySQL / 系统课时
日期与时间类型
日期与时间类型
本课解决 MySQL 中“日期、时间、日期时间到底该选哪种类型”的问题。你将区分 DATE、TIME、DATETIME、TIMESTAMP 和 YEAR 的语义与常见用途,并能根据业务是否需要时区转换、是否需要独立保存日期或时间来选择字段。
学习目标
- 区分 DATE、TIME、DATETIME、TIMESTAMP、YEAR 的数据语义
- 根据业务场景选择合适的日期时间类型
- 能用 SQL 建表并验证字段定义
先按业务语义选择类型
日期和时间类型首先应该表达“业务到底需要什么”。只需要年月日,就使用 DATE;只需要一天中的时间,就使用 TIME;需要完整日期和时间但通常不希望数据库自动做时区语义转换时,可以使用 DATETIME;需要时间点并需要结合会话时区进行处理时,则重点考虑 TIMESTAMP。
YEAR 适合只保存年份的场景,例如统计年度、车型年份等。它不是完整日期,也不应拿来替代需要具体月份和日期的字段。
| 类型 | 保存内容 | 常见场景 | 关键特点 |
|---|---|---|---|
DATE |
日期 | 生日、账期日、合同日期 | 不包含时分秒 |
TIME |
时间或时间间隔表示 | 营业时间、每日时间点 | 不包含日期 |
DATETIME |
日期 + 时间 | 业务发生时间 | 适合保存业务日历时间 |
TIMESTAMP |
时间戳 | 创建时间、更新时间 | 时区处理语义需要特别注意 |
YEAR |
年份 | 年度统计、年份属性 | 只表达年份 |
DATETIME 与 TIMESTAMP 怎么选
实际开发中最容易混淆的是 DATETIME 和 TIMESTAMP。选择时不要只记“一个更大、一个更小”,而应先判断这个字段保存的是哪一种时间概念。
如果字段表达的是业务日历中的具体日期时间,例如“会议开始时间”“门店营业开始时间”,DATETIME 往往更直观。如果字段表达的是系统事件发生的时间点,例如“记录创建时间”“更新时间”,则常见设计会考虑 TIMESTAMP,并明确应用和数据库的时区约定。
尤其需要注意:时间类型的正确性不仅取决于字段本身,还取决于应用如何读取、写入以及处理时区。跨地区系统应统一时间基准,避免把本地显示时间直接当成全球唯一时间点保存。
建表示例与字段验证
下面建立一个订单表,订单日期是纯日期,创建和更新时间是完整时间点,营业开始时间只关心一天中的时刻,统计年份则单独使用 YEAR。
CREATE TABLE order_demo (
id BIGINT PRIMARY KEY,
order_date DATE NOT NULL,
open_time TIME,
created_at TIMESTAMP NOT NULL,
updated_at DATETIME NOT NULL,
report_year YEAR NOT NULL
);
DESCRIBE order_demo;预期结果
Query OK, 0 rows affected
+-------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+---------+-------+
| id | bigint | NO | PRI | NULL | |
| order_date | date | NO | | NULL | |
| open_time | time | YES | | NULL | |
| created_at | timestamp | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
| report_year | year | NO | | NULL | |
+-------------+-----------+------+-----+---------+-------+这里的重点不是某一种类型永远更好,而是字段语义必须稳定。比如“生日”通常是日期,不应该因为当前系统习惯记录完整时间就强行保存成 DATETIME。
精度、时区与默认值
需要保存小数秒时,可以使用带精度的日期时间类型,例如 DATETIME(6) 或 TIMESTAMP(6)。其中 6 表示最多保存 6 位小数秒。是否真的需要微秒级精度,应由业务和审计要求决定。
默认值和自动更新时间也要单独检查。例如系统创建时间常需要插入时自动填写,更新时间则可能在更新记录时自动刷新。无论采用哪种方式,都应明确数据库与应用层谁负责赋值,避免两边同时处理造成混乱。
什么时候不该混用日期时间类型
不要为了“以后可能需要更多信息”就把所有字段都改成 DATETIME。如果字段本质上只有日期,额外保存时分秒只会增加语义噪音;同样,如果字段本质上是一个跨时区的事件时间点,也不能因为看起来方便就随意当成本地日历时间处理。
设计时可以按这个顺序判断:先确定业务语义,再确定是否需要日期、时间还是二者组合,最后处理时区、精度和默认值。这样比背类型名称更不容易出错。