MySQL 8.0中DATE，DATETIME和 TIMESTAMP类型和5.7之间的差异学习心得分享

DATE，DATETIME和 TIMESTAMP类型都和时间有关。本文介绍MySQL 8.0和MySQL 5.7之间的差异等；本文MySQL测试环境为8.0.23；

MySQL允许对DATETIME和 TIMESTAMP值使用小数秒，精度最高为微秒（6位数）

CREATE TABLE t1 (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));

DATE

格式为： ‘YYYY-MM-DD’，支持的范围是 ‘1000-01-01’到 ‘9999-12-31’。

DATETIME[(fsp)]

日期和时间组合。格式为：’YYYY-MM-DD hh:mm:ss’，支持的范围是 ‘1000-01-01 00:00:00.000000’到 ‘9999-12-31 23:59:59.999999’。

fsp指定一个介于0到6之间的可选值，以指定小数秒精度。值为0表示没有小数部分。如果省略，则默认精度为0。

DATETIME可以使用DEFAULT和 ON UPDATE列定义子句指定自动初始化和更新到列的当前日期和时间

TIMESTAMP[(fsp)]

时间戳。格式为：’YYYY-MM-DD hh:mm:ss’。范围是’1970-01-01 00:00:01.000000’UTC到’2038-01-19 03:14:07.999999’UTC。 TIMESTAMP值存储为自纪元（’1970-01-01 00:00:00’UTC）以来的秒数。 TIMESTAMP不能代表值’1970-01-01 00:00:00’，因为这是等同于从所述历元和值00秒被保留用于表示’0000-00-00 00:00:00’，该“零” TIMESTAMP值。

fsp指定一个介于0到6之间的可选值，以指定小数秒精度。值为0表示没有小数部分。如果省略，则默认精度为0。
服务器处理TIMESTAMP 定义的方式取决于explicit_defaults_for_timestamp 系统变量的值（请参见第5.1.8节“服务器系统变量”）。

如果 explicit_defaults_for_timestamp 启用，则不会自动将DEFAULT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP属性分配给任何 TIMESTAMP列。它们必须明确包含在列定义中。同样，任何 TIMESTAMP未明确声明为NOT NULL允许 NULL值的值。

如果 explicit_defaults_for_timestamp 禁用，则服务器TIMESTAMP 将按以下方式处理：

除非另有说明，如果未显式分配值，则表中的第一列TIMESTAMP被定义为自动设置为最新修改的日期和时间。这TIMESTAMP 对于记录“INSERT或” UPDATE操作的时间戳很有用。也可以TIMESTAMP通过为其分配NULL值来将任何列设置为当前日期和时间，除非已使用NULL，允许NULL值的属性对其进行了定义。

可以使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP列定义子句指定自动初始化和更新到当前日期和时间。默认情况下，第一TIMESTAMP 列具有这些属性，如前所述。但是，TIMESTAMP可以将表中的任何列定义为具有这些属性。

小数部分应始终与其余时间间隔一个小数点；无法识别其他小数秒分隔符。

该TIMESTAMP和DATETIME 数据类型提供自动初始化和更新到当前的日期和时间。后续文章会进行讲解；

MySQL将TIMESTAMP值从当前时区转换为UTC以进行存储，然后从UTC转换为当前时区以进行检索。（对于其他类型，例如DATETIME。不会发生这种情况。）默认情况下，每个连接的当前时区是服务器的时间。可以在每个连接的基础上设置时区。只要时区设置保持不变，您将获得与存储相同的值。如果您存储一个TIMESTAMP值，然后更改时区并检索该值，则检索到的值与您存储的值不同。发生这种情况是因为没有在两个方向上使用相同的时区进行转换。当前时区可作为time_zone系统变量。

从MySQL 8.0.19开始，可以在向表中插入TIMESTAMP和 DATETIME值时指定时区偏移量。偏移量被附加到datetime文字的时间部分，中间没有空格，并且使用与设置time_zone系统变量相同的格式，但以下情况除外：

如果小时值小于10，则需要前导零。

该值’-00:00’被拒绝。

时区名称，例如’EET’和 ‘Asia/Shanghai’不能使用； ‘SYSTEM’也不能在这种情况下使用。

从MySQL8.0.22开始，插入值的月份、日部分或两者都不能为零，这是强制执行的，不管服务器SQL模式如何设置。

此示例演示如何使用不同的时区设置将带有时区偏移的datetime值插入TIMESTAMP和datetime列，然后检索它们：

mysql>CREATE TABLE ts (
    ->     id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     col TIMESTAMP NOT NULL
    -> ) AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.09 sec)

mysql>CREATE TABLE dt (
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     col DATETIME NOT NULL
    -> ) AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.01 sec)

mysql>SET @@time_zone = 'SYSTEM';
Query OK, 0 rows affected (0.01 sec)

mysql>INSERT INTO ts(col) VALUES('2020-04-25 09:10:10'),('2020-04-25 10:10:10+05:30'),('2020-04-25 10:10:10-08:00');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>SET @@time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql>INSERT INTO ts(col) VALUES ('2020-04-25 10:10:10'),('2020-04-25 10:10:10+05:30'), ('2020-04-25 10:10:10-08:00');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>SET @@time_zone = 'SYSTEM';
Query OK, 0 rows affected (0.00 sec)

mysql>INSERT INTO dt(col) VALUES ('2020-04-25 10:10:10'),('2020-04-25 10:10:10+05:30'), ('2020-04-25 10:10:10-08:00');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>SET @@time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql>INSERT INTO dt(col) VALUES ('2020-04-25 10:10:10'),('2020-04-25 10:10:10+05:30'), ('2020-04-25 10:10:10-08:00');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>SET @@time_zone = 'SYSTEM';
Query OK, 0 rows affected (0.00 sec)

mysql>SELECT @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| CST                |
+--------------------+
1 row in set (0.01 sec)

mysql>SELECT col, UNIX_TIMESTAMP(col) FROM dt ORDER BY id;
+---------------------+---------------------+
| col                 | UNIX_TIMESTAMP(col) |
+---------------------+---------------------+
| 2020-04-25 10:10:10 |          1587780610 |
| 2020-04-25 12:40:10 |          1587789610 |
| 2020-04-26 02:10:10 |          1587838210 |
| 2020-04-25 10:10:10 |          1587780610 |
| 2020-04-25 04:40:10 |          1587760810 |
| 2020-04-25 18:10:10 |          1587809410 |
+---------------------+---------------------+
6 rows in set (0.00 sec)

mysql>SELECT col, UNIX_TIMESTAMP(col) FROM ts ORDER BY id;
+---------------------+---------------------+
| col                 | UNIX_TIMESTAMP(col) |
+---------------------+---------------------+
| 2020-04-25 09:10:10 |          1587777010 |
| 2020-04-25 12:40:10 |          1587789610 |
| 2020-04-26 02:10:10 |          1587838210 |
| 2020-04-25 18:10:10 |          1587809410 |
| 2020-04-25 12:40:10 |          1587789610 |
| 2020-04-26 02:10:10 |          1587838210 |
+---------------------+---------------------+
6 rows in set (0.00 sec)

当选择 datetime类型时，即使插入时使用了偏移量，也不会显示偏移量。

支持的偏移值范围是 -13:59至+14:00。

包含时区偏移量的Datetime文字被准备好的语句接受为参数值。

如果SQL模式允许此转换，则将无效的日期、日期时间或时间戳值转换为相应类型的“零”值（’0000-00-00’或’0000-00-00 00:00:00’）。精确的行为取决于是否启用了严格SQL模式和NO_ZERO_DATE模式；

在MySQL 8.0.22和更高版本，可以转换 TIMESTAMP值UTC DATETIME使用提取它们的值 CAST()与AT TIME ZONE操作，如下所示：

mysql>SELECT col,CAST(col AT TIME ZONE INTERVAL '+00:00' AS DATETIME) AS ut FROM ts ORDER BY id;
+---------------------+---------------------+
| col                 | ut                  |
+---------------------+---------------------+
| 2020-04-25 09:10:10 | 2020-04-25 01:10:10 |
| 2020-04-25 12:40:10 | 2020-04-25 04:40:10 |
| 2020-04-26 02:10:10 | 2020-04-25 18:10:10 |
| 2020-04-25 18:10:10 | 2020-04-25 10:10:10 |
| 2020-04-25 12:40:10 | 2020-04-25 04:40:10 |
| 2020-04-26 02:10:10 | 2020-04-25 18:10:10 |
+---------------------+---------------------+
6 rows in set (0.00 sec)

注意MySQL中日期值解释的某些属性：

MySQL允许对指定为字符串的值使用“放松”格式，其中任何标点字符都可以用作日期部分或时间部分之间的分隔符。在某些情况下，这种语法可能是欺骗的。例如，例如“10:11:12”之类的值可能因为：，看起来像一个时间值，但如果在日期上下文中使用，则解释为“2010-11-12”年。值“10:45:15”转换为“0000-00-00”，因为“45”不是有效月份。

在日期和时间部分与小数秒部分之间唯一识别的分隔符是小数点。

服务器要求月份和日期值有效，而不仅仅是分别在1到12和1到31范围内。禁用严格模式后，无效日期（例如） ‘2004-04-31’将转换为 ‘0000-00-00’并生成警告。启用严格模式后，无效日期会产生错误。要允许这样的日期，请启用 ALLOW_INVALID_DATES。

MySQL不接受TIMESTAMP值在day或month列中包含零的值或不是有效日期的值。唯一的例外是特殊的“零”值 ‘0000-00-00 00:00:00’，如果SQL模式允许该值。精确的行为取决于是否启用了严格SQL模式和NO_ZERO_DATE无零日期SQL模式；

包含两位数年份值的日期是不明确的，因为世纪是未知的。MySQL使用以下规则解释两位数的年份值：

范围内的年值00-69变为 2000-2069。

范围内的年值70-99变为 1970-1999。