mysql timestamp字段规范使用详情

吾爱主题阅读：121 2022-11-22 16:26:00 评论：0

1. 前言

这个世界离不开时间，同样，数据库中也是如此，表中的每条记录除了数据模型的时间字段（如生日，生产日期，出发日期等），一般至少还有两个固定的时间字段：记录插入时间，记录更新时间。

然而，看似很简单时间字段，谁能想到会导致应用报错，引发血案：

个中缘由，正是接下来要讲到的。

2. mysql中的时间字段

因时间字段的一些特性与版本有关，且目前我司统一使用的mysql 5.7版本，因此本文内容都基于mysql 5.7。
mysql时间相关的字段主要有DATE、DATETIME、TIMESTAMP。

其中datatime和timestamp字段都可以包含小数，如datetime(6)，字节长度的可变部分（0-3）由小数位数决定：

2.1. 数据的存储方式

DATE：

3个字节的整型，按照这种方式进行压缩： YYYY×16×32 + MM×32 + DD

DATETIME：

整数部分5个字节，由以下部分组成

TIMESTAMP：

整数部分4个字节，存储从(‘1970-01-01 00:00:00’ UTC)到指定时间的秒数；

timestamp类型是4个字节，最大值是2的31次方减1，也就是2147483647，转换成北京时间就是2038-01-19 11:14:07

2.2. DATETIME和TIMESTMAP的区别

数据的存储方式决定了timestamp的计算和索引效率比datetime更快；
timestamp存储的时间范围比datetime小很多；
timestamp数据显示时要根据时区换算，datetime数据显示时不受时区影响；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 admin@test 04:42:41>show variables like 'time_zone' ; + ---------------+--------+ | Variable_name | Value | + ---------------+--------+ | time_zone | +08:00 | + ---------------+--------+ admin@test 04:42:42> create table t1(dt datetime,ts timestamp ); admin@test 04:43:07> insert into t1 values (now(),now()); admin@test 04:43:17> select * from t1; + ---------------------+---------------------+ | dt | ts | + ---------------------+---------------------+ | 2021-03-27 16:43:17 | 2021-03-27 16:43:17 | + ---------------------+---------------------+ admin@test 04:43:50> set time_zone= '+09:00' ; admin@test 04:44:00> select * from t1; + ---------------------+---------------------+ | dt | ts | + ---------------------+---------------------+ | 2021-03-27 16:43:17 | 2021-03-27 17:43:17 | + ---------------------+---------------------+ admin@test 04:44:07>

timestamp在处理默认值和null值时的行为时受mysql参数explicit_defaults_for_timestamp控制，datatime不受影响。

3. timestamp字段处理默认值和null值时的行为

3.1. 参数禁用

当禁用该值时(explicit_defaults_for_timestamp=0)，mysql启用timestamp字段的特有行为（和数字、字符串等类型的表现不同），

具体特性如下:

timestamp字段默认设置为not null
表中的第一个timestamp字段插入时默认设置当前时间，更新时，默认更新为当前时间，即默认设置为以下特性：NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
表中的第二个timestamp字段默认为'0000-00-00 00:00:00'
显式向timestamp字段插入null值时，不会报错，且都设置为当前时间;
对datetime字段的行为无影响。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 admin@test 05:49:00> create table t2(id int auto_increment, name varchar (100), dt1 datetime, ts1 timestamp , ts2 timestamp , primary key (id)); admin@test 05:49:48>show create table t2; ± ------±--------------------------------------------------------------------------+ | Table | Create Table | ± ------±--------------------------------------------------------------------------+ | t2 | CREATE TABLE t2 ( id int (11) NOT NULL AUTO_INCREMENT, name varchar (100) DEFAULT NULL , dt1 datetime DEFAULT NULL , ts1 timestamp NULL DEFAULT NULL , ts2 timestamp NULL DEFAULT NULL , PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | ± ------±--------------------------------------------------------------------------+ 1 row in set (0.00 sec) admin@test 05:50:20> insert into t2( name ) values (‘a1 '); Query OK, 1 row affected (0.00 sec) admin@test 05:51:07>select * from t2; ±—±-----±-----±-----±-----+ | id | name | dt1 | ts1 | ts2 | ±—±-----±-----±-----±-----+ | 1 | a1 | NULL | NULL | NULL | ±—±-----±-----±-----±-----+ 1 row in set (0.00 sec) ##注：插入记录时，默认为null admin@test 05:54:20>update t2 set name=‘aa1' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 admin@test 05:54:31> select * from t2; ±—± -----±-----±-----±-----+ | id | name | dt1 | ts1 | ts2 | ±—± -----±-----±-----±-----+ | 1 | aa1 | NULL | NULL | NULL | ±—± -----±-----±-----±-----+ 1 row in set (0.00 sec) ##注：更新记录时，默认为 null admin@test 05:58:10> create table t3(id int auto_increment, name varchar (100),ts1 timestamp not null default current_timestamp , primary key (id)); admin@test 05:58:18> insert into t3( name ) values (‘a1 '); Query OK, 1 row affected (0.00 sec) admin@test 05:58:22>select * from t3; ±—±-----±--------------------+ | id | name | ts1 | ±—±-----±--------------------+ | 1 | a1 | 2021-03-23 17:58:22 | ±—±-----±--------------------+ 1 row in set (0.00 sec) ##注：创建表手动设置not null default current_timestamp，插入记录不含timestamp字段时，默认为当前时间 admin@test 05:58:25>insert into t3(name,ts1) values(‘a1' , null ); ERROR 1048 (23000): Column ‘ts1 ' cannot be null ##注：timestamp字段显式插入null时，报错Column ‘ts1' cannot be null admin@test 05:59:11> create table t4(id int auto_increment, name varchar (100),ts1 timestamp not null , primary key (id)); Query OK, 0 rows affected (0.04 sec) admin@test 05:59:44> insert into t4( name ) values (‘a1 '); ERROR 1364 (HY000): Field ‘ts1' doesn 't have a default value admin@test 05:59:49> ##注：创建表手动设置not null，插入记录不含timestamp字段时，报错Field doesn' t have a default value admin@test 05:59:50> insert into t4( name ,ts1) values (‘a1 ',null); ERROR 1048 (23000): Column ‘ts1' cannot be null admin@test 05:59:57> ##注： timestamp 字段显式插入 null 时，报错 Column ‘ts1' cannot be null

3.2. 参数启用

当启用该值时(explicit_defaults_for_timestamp=1)，mysql禁用timestamp字段的特有行为，具体表现和数字、字符串类型一样。

timestamp字段默认属性是“NULL DEFAULT NULL”；
timestamp字段手动设置了not null和default后，显式插入null值会报错：Column cannot be null；
timestamp字段同时设置了not null但未设置default后，显式插入null值会报错：Column cannot be null，插入记录不含timestamp字段时会报错Field doesn’t have a default value；
对datetime字段的行为无影响。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 admin@test 05:49:00> create table t2(id int auto_increment, name varchar (100), dt1 datetime, ts1 timestamp , ts2 timestamp , primary key (id)); admin@test 05:49:48>show create table t2; + -------+---------------------------------------------------------------------------+ | Table | Create Table | + -------+---------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `id` int (11) NOT NULL AUTO_INCREMENT, ` name ` varchar (100) DEFAULT NULL , `dt1` datetime DEFAULT NULL , `ts1` timestamp NULL DEFAULT NULL , `ts2` timestamp NULL DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | + -------+---------------------------------------------------------------------------+ 1 row in set (0.00 sec) admin@test 05:50:20> insert into t2( name ) values (‘a1 '); Query OK, 1 row affected (0.00 sec) admin@test 05:51:07>select * from t2; ±—±-----±-----±-----±-----+ | id | name | dt1 | ts1 | ts2 | ±—±-----±-----±-----±-----+ | 1 | a1 | NULL | NULL | NULL | ±—±-----±-----±-----±-----+ 1 row in set (0.00 sec) ## 注：插入记录时，默认为null admin@test 05:54:20>update t2 set name=‘aa1' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 admin@test 05:54:31> select * from t2; ±—± -----±-----±-----±-----+ | id | name | dt1 | ts1 | ts2 | ±—± -----±-----±-----±-----+ | 1 | aa1 | NULL | NULL | NULL | ±—± -----±-----±-----±-----+ 1 row in set (0.00 sec) ## 注：更新记录时，默认为 null admin@test 05:58:10> create table t3(id int auto_increment, name varchar (100),ts1 timestamp not null default current_timestamp , primary key (id)); admin@test 05:58:18> insert into t3( name ) values (‘a1 '); Query OK, 1 row affected (0.00 sec) admin@test 05:58:22>select * from t3; ±—±-----±--------------------+ | id | name | ts1 | ±—±-----±--------------------+ | 1 | a1 | 2021-03-23 17:58:22 | ±—±-----±--------------------+ 1 row in set (0.00 sec) ##注：创建表手动设置not null default current_timestamp，插入记录不含timestamp字段时，默认为当前时间 admin@test 05:58:25>insert into t3(name,ts1) values(‘a1' , null ); ERROR 1048 (23000): Column ‘ts1 ' cannot be null ##注：timestamp字段显式插入null时，报错Column ‘ts1' cannot be null admin@test 05:59:11> create table t4(id int auto_increment, name varchar (100),ts1 timestamp not null , primary key (id)); Query OK, 0 rows affected (0.04 sec) admin@test 05:59:44> insert into t4( name ) values (‘a1 '); ERROR 1364 (HY000): Field ‘ts1' doesn 't have a default value admin@test 05:59:49> ##注：创建表手动设置not null，插入记录不含timestamp字段时，报错Field doesn' t have a default value admin@test 05:59:50> insert into t4( name ,ts1) values (‘a1 ',null); ERROR 1048 (23000): Column ‘ts1' cannot be null admin@test 05:59:57> ##注： timestamp 字段显式插入 null 时，报错 Column ‘ts1' cannot be null

4. 总结

启用该参数(explicit_defaults_for_timestamp=1)

timestamp字段在null、default属性的表现和其他普通字段表现类似：

如果没有显式设置default值，该值的维护完全需要应用程序显式插入和更新；
如果设置了not null，那么一定不能显式插入null值，否则应用会报错。

禁用该参数(explicit_defaults_for_timestamp=0)

timestamp字段在null、default属性的表现和其他普通字段表现有明显差异：

默认会设置NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP；
显式插入null值，默认为当前时间，应用不会报错。

案例发生的场景：

公司所有集群已经统一启用该参数；
某集群过去某个时间因为研发的要求，将该参数禁用，但是这次集群切换后的新服务器采用了统一的参数模板，启用了参数；
应用程序显式向timestamp字段插入null值，且该字段已经设置了not null，在禁用该参数的集群不会报错，但是切换到启用了该参数的集群时，就报column cannot be null.

统一规范：

个别集群禁用该参数导致公司所有的mysql集群参数不统一，可能带来应用报错的后果，因此建议：