mysql timestamp欄位規範使用詳情

2022-09-30 14:02:28

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資料顯示時不受時區影響；

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欄位的行為無影響。

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欄位的行為無影響。

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叢集引數不統一，可能帶來應用報錯的後果，因此建議：