MySQL 时间相关介绍

2018-09-15 database mysql

简单介绍 MySQL 中与时间相关的概念。

简介

MySQL 中有三种时间类型，官方的解释如下：

The DATE type is used for values with a date part but no time part. MySQL
retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported
range is '1000-01-01' to '9999-12-31'.

The DATETIME type is used for values that contain both date and time parts.
MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format.
The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time
parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19
03:14:07' UTC.

特殊属性

timestamp 有两个比较特殊的属性，分别是 ON UPDATE CURRENT_TIMESTAMP 和 CURRENT_TIMESTAMP 两种，使用情况分别如下：

CURRENT_TIMESTAMP

当要向数据库执行 INSERT 操作时，如果有 timestamp 字段属性设为 CURRENT_TIMESTAMP，则无论这个字段有没有值都会插入当前系统时间。

ON UPDATE CURRENT_TIMESTAMP

当执行 update 操作时，并且字段有上述的属性时，则字段无论值有没有变化，它的值也会跟着更新为当前 UPDATE 操作时的时间。

测试

简单测试如下。

----- 新建测试表
mysql> CREATE TABLE foobar (
  id INT(10) UNSIGNED NOT NULL PRIMARY KEY,
  gmt_create TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  gmt_modify TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

----- 新写入数据时，会自动更新DEFAULT CURRENT_TIMESTAMP对应字段
mysql> INSERT INTO foobar(id, gmt_modify) VALUES(1, '2019-08-09T11:35:52+08:00');
mysql> INSERT INTO foobar(id, gmt_modify) VALUES(1, now());
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM foobar;
+----+---------------------+---------------------+
| id | gmt_create          | gmt_modify          |
+----+---------------------+---------------------+
|  1 | 2016-04-23 13:04:59 | 2016-04-23 13:04:59 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

----- 在执行UPDATE语句时，会更新ON UPDATE CURRENT_TIMESTAMP对应字段
mysql> UPDATE foobar SET id=2 WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM foobar;
+----+---------------------+---------------------+
| id | gmt_create          | gmt_modify          |
+----+---------------------+---------------------+
|  2 | 2016-04-23 13:04:59 | 2016-04-23 13:06:27 |
+----+---------------------+---------------------+
1 row in set (0.01 sec)

默认值

从 MySQL 5.6.6 之后，可能会发现如下的 Warning 日志，TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 这是因为这一版本之后，timestamp 类型的默认行为已经取消。

在此之前，TIMESTAMP 类型的默认行为是：

  • TIMESTAMP 列如果没有明确声明 NULL 属性，则默认为 NOT NULL (而其它数据类型，未显示声明 NOT NULL，则允许 NULL值)，当设置 TIMESTAMP 的列值为 NULL，会自动存储为当前 timestamp。
  • 表中的第一个 TIMESTAMP 列，如果没有声明 NULL 属性、DEFAULT 或者 ON UPDATE，会自动分配 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性。
  • 表中第二个 TIMESTAMP 列，如果没有声明 NULL 或 DEFAULT 值，则默认设置 '0000-00-00 00:00:00'；插入行时没有指明改列的值，该列默认分配 '0000-00-00 00:00:00'，且无警告。

如果要关闭警告，如上所述，可以在启动时添加 --explicit_defaults_for_timestamp 参数，或者在下面的配置文件中添加如下的参数：

[mysqld]
explicit_defaults_for_timestamp=true

重启 MySQL 后，此时 TIMESTAMP 的行为如下：

  • 如果没有显示声明 NOT NULL 属性，也就是是允许 NULL 值的，可以直接设置改列为 NULL，而没有默认的填充行为。
  • 不会默认分配 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性。
  • 声明为 NOT NULL 且没有默认子句的 TIMESTAMP 列是没有默认值的；往数据表中插入列，又没有给 TIMESTAMP 列赋值时，如果是严格 SQL 模式，会抛出一个错误，如果严格 SQL 模式没有启用，该列会赋值为 '0000-00-00 00:00:00' ，同时出现一个警告(和 MySQL 处理其他时间类型数据一样，如 DATETIME)。

显然，通过该参数关闭了 timestamp 类型字段所拥有的一些会让人感到奇怪的默认行为，如果仍需要默认行为，则需要在建表的时候指定。