**前言：**

時間戳欄位在MySQL中經常使用到，比如需要記錄一行資料建立的時間或修改的時間時，我們通常會使用時間戳即timestamp欄位。本篇文章主要介紹timestamp欄位的使用方法及相關引數，希望大家讀完能對timestamp有更深的認識。

#### 1.TIMESTAMP欄位型別簡介

timestamp欄位型別可儲存時間型別資料，timestamp所能儲存的時間範圍為：'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'，timestamp欄位佔用4個位元組。在MySQL5.7版本，timestamp可以指定精度，即TIMESTAMP(fsp)中fsp可以指定一個介於0到6之間的可選值，以代表小數秒精度。值為0表示沒有小數部分，如果省略，則預設精度為0。

下面我們來簡單測試下：

```sql
# timestamp可以指定精度
mysql> CREATE TABLE `stu_tb` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
    ->   `stu_id` int(11) NOT NULL COMMENT '學號',
    ->   `stu_name` varchar(20) DEFAULT NULL COMMENT '學生姓名',
    ->   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    ->   `update_time` timestamp(2) NOT NULL DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP(2) COMMENT '修改時間',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> show create table stu_tb\G
*************************** 1. row ***************************
       Table: stu_tb
Create Table: CREATE TABLE `stu_tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  `stu_id` int(11) NOT NULL COMMENT '學號',
  `stu_name` varchar(20) DEFAULT NULL COMMENT '學生姓名',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `update_time` timestamp(2) NOT NULL DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP(2) COMMENT '修改時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into stu_tb (stu_id,stu_name) values (1001,'dfdfa');
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu_tb;
+----+--------+----------+---------------------+------------------------+
| id | stu_id | stu_name | create_time         | update_time            |
+----+--------+----------+---------------------+------------------------+
|  1 |   1001 | dfdfa    | 2020-01-07 17:33:51 | 2020-01-07 17:33:51.63 |
+----+--------+----------+---------------------+------------------------+
1 row in set (0.00 sec)
```

#### 2.影響時間戳的引數

影響時間戳顯示的引數主要有兩個，分別是explicit_defaults_for_timestamp，time_zone。下面簡單介紹下這兩個引數對時間戳的影響。

explicit_defaults_for_timestamp引數決定MySQL服務端對timestamp列中的預設值和`NULL`值的不同處理方法。此變數自MySQL 5.6.6 版本引入，分為全域性級別和會話級別，可動態更新，預設值為OFF。

當explicit_defaults_for_timestamp引數預設設定為OFF時，其行為如下：

- 在預設情況下，如果timestamp列沒有顯式的指明null屬性，那麼該列會被自動加上not null屬性(而其他型別的列如果沒有被顯式的指定not null，那麼是允許null值的)，如果往這個列中插入null值，會自動的設定該列的值為current timestamp值。
- 表中的第一個timestamp列，如果沒有指定null屬性或者沒有指定預設值，也沒有指定ON UPDATE語句。那麼該列會自動被加上DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP屬性。
- 對於其它TIMESTAMP列，如果沒有顯示指定NULL和DEFAULT屬性的話，會自動設定為NOT NULL DEFAULT '0000-00-00 00:00:00'。(當然，這個與SQL_MODE有關，如果SQL_MODE中包含'NO_ZERO_DATE'，實際上是不允許將其預設值設定為'0000-00-00 00:00:00'的。)

當explicit_defaults_for_timestamp引數設定為ON時，其行為如下：

- 如果timestamp列沒有顯式的指定not null屬性，那麼預設的該列可以為null，此時向該列中插入null值時，會直接記錄null，而不是current timestamp。
- 不會自動的為表中的第一個timestamp列加上DEFAULT CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP屬性。
- 如果timestamp列被加上了not null屬性，並且沒有指定預設值。這時如果向表中插入記錄，但是沒有給該TIMESTAMP列指定值的時候，如果strict sql_mode被指定了，那麼會直接報錯。如果strict sql_mode沒有被指定，那麼會向該列中插入'0000-00-00 00:00:00'並且產生一個warning。

time_zone引數指定了資料庫時區，time_zone分為全域性級別和會話級別，可動態更新，預設為SYSTEM，表示資料庫服務時區與系統時區相同。MySQL儲存timestamp時間戳時，存入資料庫的實際是UTC的時間，查詢顯示時會根據具體的時區來顯示不同的時間。可以在配置檔案中寫入default_time_zone指定時區，比如指定中國時間東八區可以這樣寫：default_time_zone='+8:00'。下面簡單演示下不同時區下時間的顯示：

# 改為UTC時區 並重新連線 發現timestamp儲存的時間有變化
mysql> set global time_zone='+0:00';

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +00:00 |
+------------------+--------+
2 rows in set (0.01 sec)
mysql> select * from testdb.stu_tb;
+----+--------+----------+---------------------+------------------------+
| id | stu_id | stu_name | create_time         | update_time            |
+----+--------+----------+---------------------+------------------------+
|  1 |   1001 | dfdfa    | 2020-01-07 09:33:51 | 2020-01-07 09:33:51.63 |
+----+--------+----------+---------------------+------------------------+
1 row in set (0.00 sec)
```

#### 3.TIMESTAMP使用最佳實踐

TIMESTAMP在使用是有很多變體，列舉常用的幾個如下：

- TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP<br>
在建立新記錄和修改現有記錄的時候都對這個資料列重新整理
- TIMESTAMP DEFAULT CURRENT_TIMESTAMP<br>
在建立新記錄的時候把這個欄位設定為當前時間，但以後修改時，不再重新整理它
- TIMESTAMP ON UPDATE CURRENT_TIMESTAMP<br>
在建立新記錄的時候把這個欄位設定為0，以後修改時重新整理它
- TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss’ ON UPDATE CURRENT_TIMESTAMP<br>
在建立新記錄的時候把這個欄位設定為給定值，以後修改時重新整理它

實際情況下，這裡建議這樣建立表：

```sql
CREATE TABLE `table_name` (
  `increment_id` INT UNSIGNED NOT NULL auto_increment COMMENT '自增主鍵',
  ...
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
  PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
```

即每一行資料都包含建立時間和修改時間，程式寫入資料並不需要顯示指定建立時間和修改時間，由預設值自動填充。

關於時間戳，下面總結下幾點經驗建議，希望對你有所幫助：

1. 資料表具有create_time、update_time時間戳欄位，並設定好預設值。
1. 對於時間戳欄位，插入資料時建議不指定或插入now()函式。
1. explicit_defaults_for_timestamp引數建議預設為OFF。
1. 若採用中國時間，建議time_zone設定為'+8:00'。
1. 不同環境資料庫時區建議保持一致，不要隨意更改資料庫時區。
1. 不同例項間遷移資料要注意時區是否相同。
1. 程式jdbc連線串建議指定時區並與資料庫保持一致。

**總結：**

本篇文章詳細介紹了timestamp的用法，對於影響時間戳的幾個引數，我們也簡單做了介紹。timestamp時間戳使用實踐及相關建議希望對你有所幫助，其實這些內容在官方文件中都有記錄，大家有事沒事可以多翻閱官方文件哦。

![wx_blog.png](https://imgconvert.csdnimg.cn/aHR0cHM6Ly9jZG4ubmxhcmsuY29tL3l1cXVlLzAvMjAyMC9wbmcvMTE5NTM3LzE1ODk0NDUxNTUwNjgtNjE3N2NiYjQtNzkyMS00YzIzLWIwNDAtN2Y3NjhlYzNhZTA3LnBuZw?x-oss-process=image/format,png)

時間戳，這樣用就對了

相關文章