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 相关参数,日期类型可保存原本非法的值,比如开启 设置项时,可设置日期类型保存一个 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。但这种情况下就无法从日期相关的操作中获得到准确的结果,比如使用 或 函数时。禁用月日的零值可通过开启 MySQL 的 模式。

除了月日可零,MySQL 还支持设置年月日都零的值 0000-00-00,对于日期非必填的情况比较有用,因为此时它比单纯的 NULL 更有语义。可通过开启 MySQL 的 模式来禁用这个全零的值。

各日期时间零值格式如下,但实际时用时,直接简写成一个 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 转换到具体的时间。对于支持多语及国际化全球部署的应用来说,显得尤为方便。需要注意的是,这里操作基于的时区默认为服务器的时区,可通过改变 SET GLOBAL time_zone=time_zone 来修改。时区的设置也可以是以连接为单位,这样来自不同时区的请求可得到不同的时间。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/fdb9aa591f75a37a5c471ca6501a4a65.html