Mysql timestamp数据类型学习

Mysql timestamp数据类型学习

首先看一下explicit_defaults_for_timestamp（5.6版本后引入）参数的值：

mysql> show variables like 'explicit%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON    |
+---------------------------------+-------+
1 row in set, 1 warning (0.20 sec)

如果explicit_defaults_for_timestamp的值为ON，则默认值、not null和on update CURRENT_TIMESTAMP属性都不会自动设置

mysql> create table t(id1 timestamp);
Query OK, 0 rows affected (0.71 sec)

mysql> desc t;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id1   | timestamp | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.04 sec)

如果explicit_defaults_for_timestamp的值为OFF，系统会自动创建默认值为CURRENT_TIMESTAMP（系统日期），并设置为not null和on update CURRENT_TIMESTAMP

mysql> set explicit_defaults_for_timestamp=off;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'explicit%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> create table t(id1 timestamp);
Query OK, 0 rows affected (0.49 sec)

mysql> desc t;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type      | Null | Key | Default           | Extra                                         |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
1 row in set (0.00 sec)

插入一个NULL值试试

mysql> insert into t values(null);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t;
+---------------------+
| id1                 |
+---------------------+
| 2019-12-02 17:22:28 |
+---------------------+
1 row in set (0.00 sec)

其中on update CURRENT_TIMESTAMP的作用为更新字段时更新为系统时间

mysql> update t set id1=null;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+---------------------+
| id1                 |
+---------------------+
| 2019-12-02 17:24:38 |
+---------------------+
1 row in set (0.00 sec)

可以看到，id1的时间变为update时的系统时间
在这里出现了一个问题，我的Mysql版本是8.0.17，如果此时向表中再新增一个名为id2的timestamp字段并且不指定任何属性，会出现如下情况

mysql> alter table t add id2 timestamp;
ERROR 1067 (42000): Invalid default value for 'id2'

而如果指定对应属性则顺利新增字段

mysql> alter table t add id2 timestamp default current_timestamp on update CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.61 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type      | Null | Key | Default           | Extra                                         |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| id2   | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
2 rows in set (0.00 sec)

TIMESTAMP还有一个重要的特点，就是和时区相关。当插入日期时，会先转换为本地时区后存放；而从数据库里面取出时，也同样需要将日期转换为本地时区后显示。这样，两个不同时区的用户看到的同一个日期可能是不一样的，下面的例子演示了这个差别：

创建表t，包含字段id1（TIMESTAMP）和id2（DATETIME），设置id2的目的是和id1做对比

mysql> create table t(id1 timestamp not null default current_timestamp,
    -> id2 datetime default null);
Query OK, 0 rows affected (0.37 sec)

查看当前时区

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set, 1 warning (0.00 sec)

可以发现，时区的值为“SYSTEM”，这个值默认和主机的时区值一致，因为我们在中国，这里的“SYSTEM”实际是东八区（+8:00）。
用now（）函数插入当前日期

mysql> insert into t values(now(),now());
Query OK, 1 row affected (0.33 sec)

mysql> select * from t;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2019-12-02 17:43:24 | 2019-12-02 17:43:24 |
+---------------------+---------------------+
1 row in set (0.00 sec)

结果显示id1和id2的值完全相同
修改时区为东九区，再次查看表中的日期

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

mysql> select * from t;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2019-12-02 18:43:24 | 2019-12-02 17:43:24 |
+---------------------+---------------------+
1 row in set (0.00 sec)

TIMESTAMP的取值范围为19700101080001到2038年的某一天，如果插入日期低于下限或者高于上限，会出现警告提示且插入值变为0值。