mysql中TIMESTAMP設置默認時間為當前時間

在我們保存數據進入到數據庫中時多半會使用像php之類的腳本來獲取一個時間保存到mysql中，其實在mysql可以直接使用TIMESTAMP 數據類型來實現默認類型了，下面一起來看看。

很多時候，為了簡單，我們在設計數據表的時候，都需要設置時間字段為當前時間。可是MySQL 中，默認值無法使用函數，也就是你無法設置某一列，默認值是 NOW () 這樣的處理。那怎麼辦呢？

TIMESTAMP 數據類型。插入的時候，忽略該列即可。

 代碼如下 復制代碼

dt TIMESTAMP
/*等價於*/
dt TIMESTAMP  default CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP
 
create table testB (
  id   int PRIMARY KEY,
  val  varchar(10),
  dt TIMESTAMP
);
 
/*我們試著插入下數據看看*/
INSERT INTO testB(id, val) VALUES(1, 'A');
INSERT INTO testB(id, val) VALUES(2, 'B');
結果

SELECT * FROM testB;
+----+------+---------------------+
| id | val  | dt                  |
+----+------+---------------------+
|  1 | A    | 2014-08-21 14:24:20 |
|  2 | B    | 2014-08-21 14:24:21 |
+----+------+---------------------+
2 rows in set (0.00 sec)

注意:MySQL的timestamp類型時間范圍between '1970-01-01 00:00:01' and '2038-01-19 03:14:07'，超出這個范圍則值記錄為'0000-00-00 00:00:00'

該類型的一個重要特點就是保存的時間與時區密切相關，上述所說的時間范圍是UTC(Universal Time Coordinated)標准，指的是經度0度上的標准時間，我國日常生活中時區以首都北京所處的東半球第8區為基准，統一使用東8區時間(俗稱北京時間)，比UTC要早8個小時，服務器的時區設置也遵照此標准，因此對應過來timestamp的時間范圍則應校准為'1970-01-01 08:00:01' and '2038-01-19 11:14:07'，也就是說東八區的1970-1-1 08:00:01等同於UTC 1970-1-1 00:00:01。
需要特點注意，timestamp類型的時間不僅僅與寫入記錄時的時區有關，顯示時也與時區有關，例如：

 代碼如下 復制代碼 mysql> desc j1_dt;
+-------+-----------+------+-----+-------------------+-------+
| Field | Type      | Null | Key | Default           | Extra |
+-------+-----------+------+-----+-------------------+-------+
| dt    | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+-------+-----------+------+-----+-------------------+-------+
1 row in set (0.00 sec)
mysql> insert into j1_dt values ('1970-01-01 08:00:01');
Query OK, 1 row affected (0.00 sec)
mysql> select * from j1_dt;
+---------------------+
| dt                  |
+---------------------+
| 1970-01-01 08:00:01 |
+---------------------+
1 row in set (0.00 sec)
mysql> set time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from j1_dt;
+---------------------+
| dt                  |
+---------------------+
| 1970-01-01 00:00:01 |
+---------------------+
1 row in set (0.00 sec)
mysql> set time_zone='+1:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from j1_dt;
+---------------------+
| dt                  |
+---------------------+
| 1970-01-01 01:00:01 |
+---------------------+
1 row in set (0.00 sec)

如上述所示，根據時區的不同，顯示的日期也是不一樣的，這正是timestamp類型在MySQL日期類型中獨有的時區特點。
如果向timestamp類型列插入的值超出了指定范圍，則實際實際保存的值為'0000-00-00 00:00:00'，並觸發一個警告信息：

 代碼如下 復制代碼 mysql> set time_zone='+8:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from j1_dt;
+---------------------+
| dt                  |
+---------------------+
| 1970-01-01 08:00:01 |
+---------------------+
1 row in set (0.00 sec)
mysql> insert into j1_dt values ('1970-01-01 00:00:01');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'dt' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from j1_dt;
+---------------------+
| dt                  |
+---------------------+
| 1970-01-01 08:00:01 |
| 0000-00-00 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

觸發的警告信息在MySQL層面僅是個警告而並非錯誤，前端應用的try catch捕獲不到，不過，由於實際寫入的數據並非期望值，還是有可能埋下一些隱患，這些隱患一旦顯露，就有可能觸發前端應用出現異常。
　　對於timestamp類型，在實際應用中務必理解時區的概念，在設置timestamp列默認值，及實際賦值時務必明確寫入的值實際保存時的狀態，盡量避免埋入隱患。對於現有已經出錯的記錄，可以考慮通過批量UPDATE及修改表結構的方式予以處理。

關於 timestamp最小值與最大值一些測試例子

 代碼如下 復制代碼

-mysql timestamp 最小值
CREATE TABLE `test` (
  `ID` int(11) NOT NULL DEFAULT '0',
  `NAME` varchar(20) DEFAULT NULL,
  `hiredate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

insert into test values(1,'zjadolf','1970-01-01 08:01:00')--插入成功
Category Timestamp Duration Message Line Position
Statement 2010/9/27 14:57:43 0:00:00.003 1 rows affected 2 0

insert into test values(2,'zjadolf','1970-01-01 08:00:00')--
Category Timestamp Duration Message Line Position
Error 2010/9/27 14:58:35 0:00:00.000 MySQL Database Error: Incorrect datetime value: '1970-01-01 08:00:00' for column 'hiredate' at row 1 2 0

insert into test values(3,'zjadolf','1970-01-01 08:00:30')--ok 可以插入
Category Timestamp Duration Message Line Position
Statement 2010/9/27 15:04:48 0:00:00.007 1 rows affected 2 0

insert into test values(1,'zjadolf','1970-01-01 08:00:01')--ok 可以插入
Category Timestamp Duration Message Line Position
Statement 2010/9/27 15:06:39 0:00:00.006 1 rows affected 2 0

insert into test values(1,'zjadolf','1970-01-01 08:00:00')--不可以插入 報錯
Category Timestamp Duration Message Line Position
Error 2010/9/27 15:07:13 0:00:00.000 MySQL Database Error: Incorrect datetime value: '1970-01-01 08:00:00' for column 'hiredate' at row 1 2 0

看樣子1970-01-01 08:00:01應該是mysql中timestamp允許的最小值,大家使用時應該注意下
驗證下我們插入如下記錄應該報錯：

 代碼如下 復制代碼 insert into test values(1,'zjadolf','1969-01-01 08:01:02')
Category Timestamp Duration Message Line Position
Error 2010/9/27 15:09:29 0:00:00.000 MySQL Database Error: Incorrect datetime value: '1969-01-01 08:01:02' for column 'hiredate' at row 1 2 0

--果然報錯

難怪我在測試使用toad for mysql import tool導入1000000數據導到 日期為：'1970-01-01 07:57:09' 這條數據的時候報錯呢！結果小於這個日期的記錄都導不進去！悲劇啊！

查看官方解釋文檔如下：
TIMESTAMP值不能早於1970或晚於2037。

當你需要同時包含日期和時間信息的值時則使用DATETIME類型。MySQL以'YYYY-MM-DD HH:MM:SS'格式檢索和顯示DATETIME值。支持的范圍為'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。(“支持”表示盡管先前的值可能工作，但沒有保證)。

當你只需要日期值而不需要時間部分時應使用DATE類型。MySQL用'YYYY-MM-DD'格式檢索和顯示DATE值。支持的范圍是'1000-01-01'到 '9999-12-31'。

TIMESTAMP列類型的屬性不固定，取決於MySQL版本和服務器運行的SQL模式。這些屬性將在本節後面描述。

看樣子如果要支持較長時間的日期，那就需要使用datetime類型。

 代碼如下 復制代碼

insert into t4 values(1,'1900-01-01 08:01:02')
Category Timestamp Duration Message Line Position
Statement 2010/9/27 15:21:53 0:00:00.051 1 rows affected 15 0

這樣看來mysql 的timestamp 和db2 的timestamp還是不一樣的！
db2 的timestamp 類型沒有mysql 這樣的限制