MySQL：日期时间类型

最新推荐文章于 2023-08-25 18:16:31 发布

研究林纳斯的那个系统

最新推荐文章于 2023-08-25 18:16:31 发布

阅读量115

点赞数

本文链接：https://blog.csdn.net/u013670453/article/details/117172012

版权

原文地址：MySQL：日期时间类型（永久地址，保存网址不迷路 🙃）

对于DATE和DATETIME范围说明，“supported”表示尽管较早的值可能正常工作，但不能保证。

类型	大小(B)	范围	显示格式	用途
YEAR[(4)]	1	0000，1901 ~ 2155	YYYY	年份值
TIME[(fsp)]	3	-838:59:59 ~ 838:59:59	HH:MM:SS[.fraction]	时间值或持续时间。允许使用字符串或数字将值进行插入。
TIMESTAMP[(fsp)]	4	UTC: 1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.999999	YYYYMMDD HHMMSS	混合日期和时间值，时间戳
DATETIME[(fsp)]	8	1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999	YYYY-MM-DD HH:MM:SS[.fraction]	混合日期和时间值。允许使用字符串或数字将值进行插入。
DATE	4	1000-01-01 ~ 9999-12-31	YYYY-MM-DD	日期值。允许使用字符串或数字将值进行插入。

在MySQL中，TIME、DATETIME、TIMESTAMP支持小数秒，精度可以高达微秒（6位）。

关于fsp
要定义包含小数秒的列，使用语法type_name(fsp)，其中type_name为TIME，DATETIME或TIMESTAMP，fsp为小数秒精度。fsp值（如果给出了）必须在0到6之间。值为0表示没有小数部分。如果省略，默认精度为0.（与标准SQL默认值6不同，用于于以前的MySQL版本兼容）。

表中的任何TIMESTAMP或DATETIME列都可以具有自动初始化和更新属性。可以使用DEFAULT和ON UPDATE子句指定自动初始化和更新到DATETIME列的当前日期和时间。

关于TIMESTAMP
TIMESTAMP值存储为自epoch（'1970-01-01 00:00:00'UTC）时代以来经过的秒数。
TIMESTAMP不能代表值“1970-01-01 00:00:00”，因为这相当于从时代开始的0秒，值0被保留以表示“0000-00-00 00:00:00”。
MySQL处理TIMESTAMP定义的方式取决于系统变量explicit_defaults_for_timestamp。
如果启用explicit_defaults_for_timestamp，则不会将DEFAULT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP属性自动分配给任何TIMESTAMP列。它们必须明确地包含在列定义中。而且，没有明确声明为NOT NULL的任何TIMESTAMP都允许NULL值。
如果禁用explicit_defaults_for_timestamp，那么MySQL按照如下方式处理：

除非另有规定，否则表中的第一个TIMESTAMP列被定义为自动设置为最近修改的日期和时间，如果未明确指定值。这使得TIMESTAMP可用于记录INSERT或UPDATE操作的时间戳。您也可以将TIMESTAMP列设置为当前日期和时间，方法是将其分配给NULL值，除非已使用NULL属性定义了NULL值。
可以使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP列定义子句来指定自动初始化和更新到当前日期和时间。默认情况下，第一个TIMESTAMP列具有这些属性，如前所述。但是，可以将表中的任何TIMESTAMP列定义为具有这些属性。

关于YEAR类型
YEAR(2)数据类型已被弃用，并在MySQL 5.7.5中删除它的支持。要将YEAR（2）列转换为YEAR（4），可以查看Section 11.3.4, “YEAR(2) Limitations and Migrating to YEAR(4)”

其他注意事项
SUM()和AVG()聚合函数不适用于时间值，因为他们会将值转换为数字，会丢弃第一个非数字字符之后的所有内容。要解决此问题，应该先将时间转数值，然后执行聚合操作，然后转换为时间值：

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;

可以在启用MAXDB SQL模式的情况下运行MySQL服务器。在这种情况下，TIMESTAMP与DATETIME相同。如果在创建表时启用了此模式，则TIMESTAMP列将创建为DATETIME列。因此，这些列使用DATETIME显示格式，具有相同的值范围，并且没有自动初始化或更新到当前日期和时间。参见Section 5.1.8, “Server SQL Modes”

字段默认值

可以为时间和日期字段设置默认值。

但这与具体的版本有关，不同的版本行为不同。

MySQL 5.5

不能将日期列的默认值设置为一个函数，比如NOW()或者CURRENT_DATE等等。

但是可以将CURRENT_TIMESTAMP作为TIMESTAMP列的默认值。

MySQL 5.6.5

可以将TIMESTAMP和DATETIME列自动初始化，并更新为当前时间和日期。

还有另外一种用法：ON UPDATE，在更新该列时，自动更新为当前日期：

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

「MySQL」- String Types（学习笔记）