MySQL的TIMESTAMP数据类型

我们可能经常用数据库中的日期和时间类型，但他能存什么精度的，有什么坑，能怎么使用，其实还是很讲究的。

Oracle数据库关于日期类型的历史文章，

《Oracle时间戳类型内部表示的转换方式》

《时间戳相减的几种方法》

《Oracle中日期字段未定义日期类型的案例补充说明》

《Oracle中日期字段未定义日期类型的案例一则》

《日期字段未定义DATE类型所带来的一些问题》

《为什么日期不建议使用VARCHAR2或者NUMBER？》

《Oracle日期类型占用的空间》

《PLSQL Developer日期格式如何定制化》

MySQL数据库关于时间戳的历史文章，

《小白学习MySQL - TIMESTAMP类型字段非空和默认值属性的影响》

SQL Server数据库关于日期类型的历史文章，

《SQL Server生成随机日期模拟测试数据的需求》

Java语言关于日期类型的历史文章，

《Java日期中“y”和“Y”的区别》

碰巧看到GreatSQL社区写了这篇文章《浅析TIMESTAMP类型》，可以借此很系统地了解一下MySQL中TIMESTAMP数据类型。

MySQL中提供的常用的日期类型有如下几种：DATE、TIME、DATETIME、TIMESTAMP，本文测试场景为MySQL 8.0。

• YEAR类型通常用来表示年。

• DATE类型通常用来表示年、月、日。

• TIME类型通常用来表示时、分、秒。

• DATETIME类型通常用来表示年、月、日、时、分、秒。

• TIMESTAMP类型通常用来表示带时区的年、月、日、时、分、秒。

在这几种类型中，只有TIMESTAMP能够与实际时区相对应，

一、DATE\TIME\DATETIME

DATE、TIME和DATETIME是经常使用到的3种日期类型，以下在三种类型字段中插入相同日期值，来看看显示效果，

#创建表
mysql> CREATE TABLE t (
    -> date DATE,
    -> time TIME,
    -> datetime DATETIME
    -> );
#插入数据
mysql> INSERT INTO t VALUES(
    -> now(),now(),now()
    -> );
#查看结果
mysql> SELECT * FROM t;
+------------+----------+---------------------+
| date       | time     | datetime            |
+------------+----------+---------------------+
| 2022-07-13 | 09:40:47 | 2022-07-13 09:40:47 |
+------------+----------+---------------------+

二、TIMESTAMP

TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是YYYY-MM-DD HH:MM:SS，需要4个字节的存储空间。

但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫作世界标准时间。

TIMESTAMP类型最大优点就是可以带有时区属性，因为他的本质就是从毫秒转换而来的，如果你的需求是需要对应不同的国家时区，那么这个类型是个不错的选择。

TIMESTAMP的标准格式为：YY-MM-DD HH:MM:SS

我们首先来看一下explicit_defaults_for_timestamp(5.6版本后引入)参数默认值，

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

我们创建一个测试表 test_t1，字段tsp为TIMESTAMP类型，

mysql> CREATE TABLE test_t1(
    -> tsp TIMESTAMP
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC test_t1;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| tsp   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.01 sec)

可以发现，系统自动创建了默认值CURRENT_TIMESTAMP(系统日期)，并且设置了NOT NULL和ON UPDATE CURRENT_TIMESTAMP属性。

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

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

现在插入一个null试试，

mysql> INSERT INTO test_t1 VALUES(null);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_t1;
+---------------------+
| tsp                 |
+---------------------+
| 2022-07-13 11:09:56 |
+---------------------+
1 row in set (0.00 sec)

果然，test_t1中自动插入了系统的时间，

mysql> ALTER TABLE test_t1 ADD tsp2 TIMESTAMP;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC test_t1;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| tsp   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| tsp2  | timestamp | YES  |     | NULL              |                             |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

注意MySQL只给表中第一个TIMESTAMP字段设置默认值为系统时间，如果有第二个TIMESTAMP类型，则设置默认值为0。

但是这个默认值也是可以修改的，在MySQL5.6之前，如果强行修改是会报错的，但是在MySQL5.6之后，这个限制已经去掉了，可以随意修改，例如，

mysql> ALTER TABLE test_t1 MODIFY tsp2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC test_t1;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| tsp   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| tsp2  | timestamp | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

当每次执行INSERT和UPDATE操作的时候，会自动记录当前操作的时间，

(1) INSERT操作

mysql> CREATE TABLE temp_test1( 
    -> timestamp TIMESTAMP,
    -> name VARCHAR(10)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into temp_test1 values(null,'小江');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM temp_test1;
+---------------------+--------+
| timestamp           | name   |
+---------------------+--------+
| 2022-07-13 16:00:40 | 小江   |
+---------------------+--------+
1 row in set (0.00 sec)

(2) UPDATE操作

mysql> UPDATE temp_test1 SET name='小张';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM temp_test1;
+---------------------+--------+
| timestamp           | name   |
+---------------------+--------+
| 2022-07-13 16:02:34 | 小张   |
+---------------------+--------+
1 row in set (0.00 sec)

我们也可以将explicit_defaults_for_timestamp设置为ON，则默认值、not null和on update CURRENT_TIMESTAMP属性都不会自动设置，需要手动操作，具体如下，

mysql> SET explicit_defaults_for_timestamp=on;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE test_t2 (notsp TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)

mysql> DESC test_t2;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| notsp | timestamp | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)

三、TIMESTAMP和DATETIME的区别：

• TIMESTAMP存储空间比较小，表示的日期时间范围比较小。

• 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。

• 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。

• TIMESTAMP和时区有关，TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

查看当前时区，

mysql> SHOW VARIABLES LIKE 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)

可以看到，时区值为"SYSTEM"，这个值默认是和主机的时区一致的，因为我们在中国，这里的"SYSTEM"实际是东八区(+8:00)。下面创建表temp_time，包含字段DATETIME和TIMESTAMP，设置两个表的目的是为了做对比，

mysql> CREATE TABLE temp_time( 
    -> datetime DATETIME,
    -> timestamp TIMESTAMP
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO temp_time 
    -> VALUES('2022-7-2 15:54:52','2022-7-2 15:54:52'); 
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> INSERT INTO temp_time 
    -> VALUES(NOW(),NOW());
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * 
    -> FROM temp_time;
+---------------------+---------------------+
| datetime            | timestamp           |
+---------------------+---------------------+
| 2022-07-02 15:54:52 | 2022-07-02 15:54:52 |
| 2022-07-13 14:18:29 | 2022-07-13 14:18:29 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

用now()函数插入的时间完全相同，现在修改时区为东九区，再次查看表中情况，

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

mysql> SELECT * FROM temp_time;
+---------------------+---------------------+
| datetime            | timestamp           |
+---------------------+---------------------+
| 2022-07-02 15:54:52 | 2022-07-02 16:54:52 |
| 2022-07-13 14:18:29 | 2022-07-13 15:18:29 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

在结果中发现，TIMESTAMP比DATETIME的值快了1小时，就是说，东九区的人看到的"2022-07-13 15:18:29"是当地时区的实际时间，就是东八区的'2022-07-13 14:18:29"如果还是以"2022-07-13 14:18:29"理解时间，必然造成时间误差。

但是如果要将时间精确到毫秒，TIMESTAMP要7个字节，和DATETIME差别不多，另一个方面，现在距离TIMESTAMP的最大值已经很接近了，这是我们选择类型需要好好考虑的问题。

四、TIMESTAMP总结

• TIMESTAMP占用4个字节。(Oracle中DATE类型占7字节，TIMESTAMP类型占11字节)
• 允许为空值，但是不可以自定义值，所以为空值时没有任何意义。
• TIMESTAMP值的下限是1970上限是2037。例如"1968-01-01"，虽然对于DATETIME或DATE类型是有效的，但对于TIMESTAMP值却无效，如果插入这样一个值将被转换为0。
• TIMESTAMP存储上限快到了，使用TIMESTAMP存在风险。
• 默认值为CURRENT_TIMESTAMP()，也就是当前的系统时间。
• 数据库会自动修改其值，所以在插入记录时不需要指定TIMESTAMP字段的名称和TIMESTAMP字段的值，只需要设计表时候添加一个TIMESTAMP字段即可，插入后该字段的值会自动变为当前系统时间。
• 默认情况下以后任何时间修改表中的记录时，对应记录的TIMESTAMP值会自动被更新为当前的系统时间。
• 可以通过设置explicit_defaults_for_timestamp实现不自动更新。
• 推荐日期类型使用DATETIME，而不是TIMESTAMP。

现在很多朋友可能都在做xc，或者在做数据库迁移的工作，这个过程中，我们要知道，不同的数据库，相近的数据类型可能还是存在一些区别，这就要我们了解看着很简单的知识点背后到底有什么不同的设计原则和运行机制，知其然，更要知其所以然，对待知识的态度，一定要端正，不能是得过且过，需要认真对待，否则出问题，就是必然。

如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，

近期更新的文章：

《你知道雨的类型有几种？》

《最近碰到的一些问题》

《小白学习MySQL - Derived Table》

《麒麟OS和龙芯环境编译安装GreatSQL》

《上小学之前要学会的本领指引》

近期的热文：

《"红警"游戏开源代码带给我们的震撼》

文章分类和索引：

《公众号1000篇文章分类和索引》