浅析TIMESTAMP类型
● 引入
● 一、DATE\TIME\DATETIME
● 二、TIMESTAMP
● 三、TIMESTAMP和DATETIME的区别
● 四、TIMESTAMP总结
引入
日期与时间是非常重要的信息,在我们的系统中,几乎所有的数据表都用得到。原因是客户需要知道数据的时间标签,从而进行数据查询、统计和处理。因此,日期与时间类型也是我们最常用到的类型之一,今天就来聊一聊日期与时间类型中的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)
可以发现,系统tm自动创建了默认值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)
我们也可以将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)
当你每次INSERT和UPDATE操作的时候,会自动记录当前操作的时间:
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)
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)
三、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个字节。
● 允许为空值,但是不可以自定义值,所以为空值时没有任何意义。
● TIMESTAMP值的下限是1970上限是2037。例如’1968-01-01’,虽然对于DATETIME或DATE类型是有效的,但对于TIMESTAMP值却无效,如果插入这样一个值将被转换为0。
● TIMESTAMP存储上限快到了,使用TIMESTAMP存在风险。
● 默认值为CURRENT_TIMESTAMP(),也就是当前的系统时间。
● 数据库会自动修改其值,所以在插入记录时不需要指定TIMESTAMP字段的名称和TIMESTAMP字段的值,你只需要在设计表的时候添加一个TIMESTAMP字段即可,插入后该字段的值会自动变为当前系统时间。
● 默认情况下以后任何时间修改表中的记录时,对应记录的TIMESTAMP值会自动被更新为当前的系统时间。
● 如果需要可以设置不自动更新。通过设置explicit_defaults_for_timestamp 可以实现。
● 推荐日期类型使用DATETIME,而不是TIMESTAMP。
本文转载自公众号GreatSQL社区