《Mysql學(xué)習(xí)MySQL timestamp的類型與時(shí)區(qū)實(shí)例詳解》要點(diǎn)：
本文介紹了Mysql學(xué)習(xí)MySQL timestamp的類型與時(shí)區(qū)實(shí)例詳解，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。
?MySQL timestamp的類型與時(shí)區(qū)MYSQL應(yīng)用
MySQL的timestamp類型時(shí)間范圍between '1970-01-01 00:00:01' and '2038-01-19 03:14:07',超出這個(gè)范圍則值記錄為'0000-00-00 00:00:00',該類型的一個(gè)重要特點(diǎn)就是保存的時(shí)間與時(shí)區(qū)密切相關(guān),上述所說(shuō)的時(shí)間范圍是UTC(Universal Time Coordinated)標(biāo)準(zhǔn),指的是經(jīng)度0度上的標(biāo)準(zhǔn)時(shí)間,我國(guó)日常生活中時(shí)區(qū)以首都北京所處的東半球第8區(qū)為基準(zhǔn),統(tǒng)一使用東8區(qū)時(shí)間(俗稱北京時(shí)間),比UTC要早8個(gè)小時(shí),服務(wù)器的時(shí)區(qū)設(shè)置也遵照此標(biāo)準(zhǔn),因此對(duì)應(yīng)過(guò)來(lái)timestamp的時(shí)間范圍則應(yīng)校準(zhǔn)為'1970-01-01 08:00:01' and '2038-01-19 11:14:07',也就是說(shuō)東八區(qū)的1970-1-1 08:00:01等同于UTC 1970-1-1 00:00:01.
MYSQL應(yīng)用
需要特點(diǎn)注意,timestamp類型的時(shí)間不僅僅與寫入記錄時(shí)的時(shí)區(qū)有關(guān),顯示時(shí)也與時(shí)區(qū)有關(guān),例如：
MYSQL應(yīng)用
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)
如上述所示,根據(jù)時(shí)區(qū)的不同,顯示的日期也是不一樣的,這正是timestamp類型在MySQL日期類型中獨(dú)有的時(shí)區(qū)特點(diǎn).
MYSQL應(yīng)用
如果向timestamp類型列插入的值超出了指定范圍,則實(shí)際實(shí)際保存的值為'0000-00-00 00:00:00',并觸發(fā)一個(gè)警告信息：
MYSQL應(yīng)用
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)
觸發(fā)的警告信息在MySQL層面僅是個(gè)警告而并非錯(cuò)誤,前端應(yīng)用的try catch捕獲不到,不過(guò),由于實(shí)際寫入的數(shù)據(jù)并非期望值,還是有可能埋下一些隱患,這些隱患一旦顯露,就有可能觸發(fā)前端應(yīng)用出現(xiàn)異常.
MYSQL應(yīng)用
對(duì)于timestamp類型,在實(shí)際應(yīng)用中務(wù)必理解時(shí)區(qū)的概念,在設(shè)置timestamp列默認(rèn)值,及實(shí)際賦值時(shí)務(wù)必明確寫入的值實(shí)際保存時(shí)的狀態(tài),盡量避免埋入隱患.對(duì)于現(xiàn)有已經(jīng)出錯(cuò)的記錄,可以考慮通過(guò)批量UPDATE及修改表結(jié)構(gòu)的方式予以處理.MYSQL應(yīng)用
感謝閱讀,希望能贊助到大家,謝謝大家對(duì)本站的支持!MYSQL應(yīng)用
歡迎參與《Mysql學(xué)習(xí)MySQL timestamp的類型與時(shí)區(qū)實(shí)例詳解》討論，分享您的想法，維易PHP學(xué)院為您提供專業(yè)教程。
轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/12327.html
