mysql中timestamp自動更新和初始化

標籤：

1.概述

在我們設計表的時候，考慮將行資料的建立時間和最後更新時間記錄下來是很好的實踐。尤其是可能需要做資料同步或者對資料新鮮度有要求的表。舉些應用情境，更新距上次更新超過2小時的行資料，或者是將一個月前的訂單資料歸檔等等。我們想把這個的需求丟給資料庫伺服器管理，而不是在應用程式中對每一條語句設定建立時間和最後更新時間欄位。在mysql中，這實現起來很容易。我們需要藉助於DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP

2.簡單樣本

 1 --建立測試表 2 CREATE TABLE `timestampTest` ( 3   `id` int(11) NOT NULL AUTO_INCREMENT, 4   `name` varchar(20) DEFAULT NULL, 5   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 6   `last_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 7   PRIMARY KEY (`id`) 8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 9 10 --檢測預設值，插入測試資料11 INSERT INTO timestampTest (name) VALUES (‘aa‘),(‘bb‘),(‘cc‘);12 13 --檢測自動更新，更新某條資料14 UPDATE timestampTest SET name = ‘ab‘ WHERE id = 1;

例子非常簡單，結果也很明顯，我就不加贅述了。

3.思考

• 執行update語句，並未改變列值(或者說設定值為當前值),on update current_timestamp列是否會更新？

　　　　不會，大家可以看一下執行完update後出現的提示——Rows matched: 1 Changed: 0 Warnings: 0。官方文檔的解釋是

An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have

　　　　

• CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(),LOCALTIME,LOCALTIME(),LOCALTIMESTAMP,LOCALTIMESTAMP(),NOW()的關係?

　　　　這七個是同義字關係

• Timestamp類型的預設值

　　　　我們討論預設情況（strict 模式）下mysql對timestamp類型的處理：

1. mysql不會給timestamp設定預設值，除非顯式設定default約束或者可空null。特例：mysql會給表第一個timestamp類型的欄位同時添加default current_timestamp和on update timestamp
2. 禁止mysql的特例處理有兩個辦法
1. 設定explicit_defaults_for_timestamp為enable
2. 顯式設定該欄位default或者null
3. timestamp列預設not null。沒有顯式指定nullable,那麼default null不合法
4. 其他情況均會引起不合法報錯

舉一些例子，協助理解

 1 #語句不合法，出現了兩個未顯示設定default或null的timestamp 2 CREATE TABLE `tt1` ( 3   `id` int(11) NOT NULL AUTO_INCREMENT, 4   `name` varchar(20), 5   `t1` timestamp , 6   `t2` timestamp , 7   PRIMARY KEY (`id`) 8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 9 10 #語句合法，t1欄位 not null default current_timestamp on update current_timestamp,t2可空11 CREATE TABLE `tt2` (12   `id` int(11) NOT NULL AUTO_INCREMENT,13   `name` varchar(20),14   `t1` timestamp ,15   `t2` timestamp null,16   PRIMARY KEY (`id`)17 ) ENGINE=InnoDB DEFAULT CHARSET=utf818 19 #語句不合法 t2欄位沒有設定default或null，也非表的第一個timestamp欄位20 CREATE TABLE `tt3` (21   `id` int(11) NOT NULL AUTO_INCREMENT,22   `name` varchar(20),23   `t1` timestamp null,24   `t2` timestamp ,25   PRIMARY KEY (`id`)26 ) ENGINE=InnoDB DEFAULT CHARSET=utf827 28 #語句不合法，這個看起來貌似合法，套用我們的規則，可以發現t2欄位沒有顯示指定null／default，儘管指定了not null也不行29 CREATE TABLE `tt4` (30   `id` int(11) NOT NULL AUTO_INCREMENT,31   `name` varchar(20),32   `t1` timestamp null,33   `t2` timestamp not null,34   PRIMARY KEY (`id`)35 ) ENGINE=InnoDB DEFAULT CHARSET=utf836 37 #語句不合法 t1,t2均合法，問題出在t3上，timestamp 預設not null，在沒有顯式指定null的時候，default null是不合法的38 CREATE TABLE `tt5` (39   `id` int(11) NOT NULL AUTO_INCREMENT,40   `name` varchar(20),41   `t1` timestamp not null,42   `t2` timestamp null,43   `t3` timestamp DEFAULT null,44   PRIMARY KEY (`id`)45 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

　　小技巧：可以使用show create table `tablename` 來查看mysql處理後的表定義，下面是tt2這張表的定義，驗證了我們的結論

　　

• Timestamp和datetime的異同

	timestamp	datetime
同	可自動更新和初始化，預設顯示格式相同YYYY-MM-dd HH:mm:ss
異	‘1970-01-01 00:00:01‘ UTC to ‘2038-01-19 03:14:07‘ UTC 自動時區轉化，實際儲存毫秒數，4位元組儲存	‘1000-01-01 00:00:00‘ to ‘9999-12-31 23:59:59‘ 不支援時區，8位元組儲存

4.參考

1.Automatic Initialization and Updating for TIMESTAMP and DATETIME

2.Date and Time Functions

mysql中timestamp自動更新和初始化