MySQL 中的日期时间类型
MySQL 中的日期时间类型
日期时间类型中包含以下几种数据类型:
各类型都有具体的取值范围,超出或非法的其他值时,MySQL 会回退到 0。TIMESTAMP 类型是个例外,给它设置一个超出范围的值时,将保存上该类型允许的最大值。
MySQL 按标准格式 YYYY-MM-DD hh:mm:ss[.fraction]
输出日期时间,但设置或进行日期时间相关的比较时却支持灵活的多种格式,会自动解析。具体支持的输入格式可参见 Section 9.1.3, “Date and Time Literals”。其中 fraction
部分为秒后面的小数部分,取值范围为 0~6 位。
虽然 MySQL 支持多种格式进行日期时间的设置,但日期部分要求必须是 年-月-日 的形式才能正确解析。比如 98-09-04
是按年月日顺序解析的,而不是英文里常用的月日年,或者日月年。
年在只给了两位数的情况下,MySQL 尝试使用以下规则来补全:
- 给定的两位数为 70~99 时解析成 1970 ~ 1999。
- 给定为 00 ~ 69 时解析成 2000 ~ 2069。
所以,为了避免不可预测的结果,使用时还是指定全一些。
- 在需要使用数字的语境下,MySQL 会将日期时间自动转成数字。同理,在需要日期时间的相关操作语境下,会尝试将数字解析成日期时间。
- 通过设置 MySQL 相关参数,日期类型可保存原本非法的值,比如开启 ALLOW_INVALID_DATES 设置项时,可设置日期类型保存一个
2009-11-31
值,但正常情况下我们知道 11 月哪来什么 31 号。此时 MySQL 仅仅只是不检查月分与日期的关联性,但月分的取值范围 112 及日期的取值范围 131 还是要单独各自做校验的。
mysql> INSERT INTO todo (title,created_on) VALUES ('blah','2019-09-31');
ERROR 1292 (22007): Incorrect date value: '2019-09-31' for column 'created_on' at row 1
mysql> SET SESSION sql_mode = 'ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO todo (title,created_on) VALUES ('blah','2019-09-31');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> SELECT * FROM todo;
+----+------+------------+
| id | title | created_on |
+----+------+------------+
| 1 | blah | 2019-09-31 |
+----+------+------------+
1 rows in set (0.00 sec)
某些场景下你可能需要保存部分日期,比如用户只输入了年没输入月日。所以 MySQL 是支持将月日设置成 0,比如 2019-00-00
。但这种情况下就无法从日期相关的操作中获得到准确的结果,比如使用 DATE_SUB()
或 DATE_ADD()
函数时。禁用月日的零值可通过开启 MySQL 的 NO_ZERO_IN_DATE
模式。
除了月日可零,MySQL 还支持设置年月日都零的值 0000-00-00
,对于日期非必填的情况比较有用,因为此时它比单纯的 NULL
更有语义。可通过开启 MySQL 的 NO_ZERO_DATE 模式来禁用这个全零的值。
各日期时间零值格式如下,但实际时用时,直接简写成一个 0 效果是等效的。
Data Type | “Zero” Value |
---|---|
DATE | '0000-00-00' |
TIME | '00:00:00' |
DATETIME | '0000-00-00 00:00:00' |
TIMESTAMP | '0000-00-00 00:00:00' |
YEAR | 0000 |
DATE,DATETIME,及 TIMESTAMP
三者具有相关性,都支持多种格式的自动解析,详见 Date and Time Literals。
DATE
日期格式不带时间 TIME
部分,查询时输出格式为 YYYY-MM-DD
,取值范围为 1000-01-01
到 9999-12-31
。
DATETIME
包含日期及时间,输出格式为 YYYY-MM-DD hh:mm:ss
,取值范围 1000-01-01 00:00:00
到 9999-12-31 23:59:59
。
TIMESTAMP
同 DATETIME
,但取值范围基于 UTC 时间,较 DATETIME
要小,为 1970-01-01 00:00:01
UTC 到 2038-01-19 03:14:07
UTC。所以使用 TIMESTAMP 格式的时间,到 2038 年会溢出,这就是 Year 2038 problem。关于该问题的讨论和解决可参见这个 StackOverflow 的回答。
既然如此,为何要使用这个取值范围更小的呢。TIMESTAMP
存储的值是带时区的。在存储时会根据当前时区转成 UTC(universal time zone) 存储,查询时也会根据时区从 UTC 转换到具体的时间。对于支持多语及国际化全球部署的应用来说,显得尤为方便。需要注意的是,这里操作基于的时区默认为服务器的时区,可通过改变 time_zone
SET GLOBAL time_zone=time_zone
来修改。时区的设置也可以是以连接为单位,这样来自不同时区的请求可得到不同的时间。
TIMESTAMP
和 DATETIME
都可包含至多 6 位的小数来表示时间中毫秒(microseconds)的部分。所以,带上毫秒时完整的格式是 YYYY-MM-DD hh:mm:ss[.fraction]
。前者取值范围为 1970-01-01 00:00:01.000000
到 2038-01-19 03:14:07.999999
,后者为 1000-01-01 00:00:00.000000
到 9999-12-31 23:59:59.999999
。
在写入时,对于非法的日期时间值,将自动存成零值,即 '0000-00-00' 或 '0000-00-00 00:00:00'。
关于日期时间需要注意的点:
- 因为 MySQL 支持比较宽松的格式来设置日期时间,所以理论上你可以用你想用的值来做为数字之间的分界符,但使用时需要关注其解析的原理。比如给一个日期格式的列设置
10:11:12
,虽然这个值看起来像时间类型,但还是可以正确在被解析成目标列的格式,即日期。如果这这个日期列设置10:45:15
则会认为是非法值,因为45
不是一个合法的月份值,所以存储时变成零值0000-00-00
。 - 日期时间与毫秒的分界符必需是小数点。
- 默认 MySQL 除了检查日月值是否有有效范围 1~ 31,1~12。还会将两者结合进来检查,比如 4 月没有 31。所以对于日期
2004-04-31
算是非法的,会变成零值0000-00-00
。如果不需要这样的约束检查,可开启 MySQL 的ALLOW_INVALID_DATES
模式。
日期时间的自动初始化及更新
TIMESTAMP
和 DATETIME
还支持自动初始化(auto-initialized)和更新到当前时间(auto-updated)。
- 创建表定义列时,指定
DEFAULT CURRENT_TIMESTAMP
来使相应的日期时间列自动初始化。 - 指定
ON UPDATE CURRENT_TIMESTAMP
来使相应的日期时间列自动更新。
两者可同时作用于一个日期时间列,表示插入记录时自动初始化成当前时间,后续记录更新时自动更新到当前时间。
其中 CURRENT_TIMESTAMP
指代当前时间,与其有相同效果的还有 CURRENT_TIMESTAMP()
, NOW()
, LOCALTIME
, LOCALTIME()
, LOCALTIMESTAMP
以及 LOCALTIMESTAMP()
。
DEFAULT
除了可指定成当前时间外,也可指定一个任意的固定值,比如 DEFAULT 0
或 `DEFAULT '2000-01-01 00:00:00'。
对于指定了自动初始化的列,插入时如果没指定该列的值,则会自动设置为当前的时间。
对于指定为自动更新的列,一旦一条记录中有字段变更,该日期会自动更新成变更时的时间。如果不想它更新,可在插入其他值时手动设置一下该日期列为原有的值,让其保持不变。
TIMESTAMP
和 DATETIME
在列的定义时,如果指定了小数部分,那么在配合使用 CURRENT_TIMESTAMP(fsp)
时,这个小数部分的精度需要保持一致。比如:
-- ✅
CREATE TABLE t1 (
ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);
-- 🚨
CREATE TABLE t1 (
ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
);
TIME
时间 TIME
格式即日期时间中时间的部分,输出格式为 hh:mm:ss
或时间较大时为 hhh:mm:ss
,取值范围 -838:59:59
到 838:59:59
。同样地,也是支持带至多 6 位小数表示毫秒。
设置时也是支持将多种格式自动解析。对于带冒号的情况,比如 11:12
解析成 11:12:00
而不是 00:11:12
。不带冒号的情况,将最右边的两位数字解析成秒(按逝去的时间来解析),比如 '1112'
和 1112
不是 11:12:00
而会解析成 00:11:12
。同理,'12'
和 12
会解析成 00:00:12
。
YEAR
YEAR
表示日期中年的部分,是一个 1 字节大小的类型,可通过 YEAR
或 YEAR(4)
来声明,其展示宽度(display width)为 4。查询时输出格式为 YYYY
,取值范围 1901 到 2155。 0000
也是合法的值。
支持使用以下格式进行设置:
- 使用 1901 ~ 2155 间的四位数字值。
- 或将上面的数字以字符串形式给定。
- 1 ~ 99 之间的数字,此时 1 ~ 69 解析成 2001 ~ 2069,70 ~ 99 解析成 1970 ~ 1999.
- 其他可返回合法值的方法,比如
NOW()
。