MySQL資料庫中的timestamp型別與時區

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及修改表結構的方式予以處理。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7607759/viewspace-695482/，如需轉載，請註明出處，否則將追究法律責任。