MySQL時間類型和模式詳情
當我在MySQL數據庫中嘗試插入一條帶有時間戳的數據時報錯：
mysql> insert into alarm_service values (6, '1970-01-01 08:00:00'); ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 08:00:00' for column 'time' at row 1 # 查看表結構 mysql> show create table alarm_service; +---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | alarm_service | CREATE TABLE `alarm_service` ( `id` int(11) NOT NULL AUTO_INCREMENT, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 | +---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
我們可以發(fā)現錯誤信息提示是時間值錯誤，但是我們這明顯是一個合法的時間點啊。
經過查詢資料，發(fā)現原因是在MySQL中，timestamp類型的合法區(qū)間是1970-01-01 00:00:01 - 2038-01-19 03:14:07 UTC，而在存儲是，會先將你插入的數據轉換為UTC時間，然后存儲起來，讀取的時候，再轉換為你的本地時間。由于我的時區(qū)為東八區(qū)，因此轉換后就變?yōu)榱?code>1970-01-01 00:00:00 UTC，成為了非法時間。
解決方案為：
- 調整時間為合法范圍
- 調整MySQL嚴格模式，允許非法時間
下面我們詳細說明相關的內容。
1、MySQL時間類型
MySQL時間類型分為三種：
- DATE：用于只包含日期不包含時間的時候，MySQL會將格式轉換為
YYYY-MM-DD，合法范圍為1000-01-01 - 9999-12-31。 - DATETIME：用于包含日期+時間的時候，格式為
YYYY-MM-DD HH:MM:SS，合法范圍為1000-01-01 00:00:00 - 9999-12-31 23:59:59。 - TIMESTAMP：用于包含日期+時間的時候，格式為
YYYY-MM-DD HH:MM:SS，合法范圍為1997-01-01 00:00:01 - 2038-01-19 03:14:07 UTC。
同時，DATETIME和TIMESTAMP還都支持一個6位微秒的數據支持，格式為YYYY-MM-DD HH:MM:SS[.fraction] ，合法范圍為.000000 - .999999。
DATETIME和TIMESTAMP還都提供自動初始化并更新為當前日期和時間的數據。
對于TIMESTAMP類型，MySQL會在存儲時將數據值轉換為UTC標準時間來存儲，讀取時再轉為當前時間。如果你的時區(qū)沒有發(fā)生改變，則該值就是你存儲的值，如果你改變了時區(qū)，讀取到的值就會發(fā)生變化。這個特性不會對DATETIME生效。
2、查看時區(qū)
mysql> show variables like '%zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CST | | time_zone | SYSTEM | +------------------+--------+
可以看到當前設置的時區(qū)是SYSTEM，即跟操作系統(tǒng)保持一致，同時系統(tǒng)的時區(qū)是CST(China Standard Time 北京標準時間)，查看系統(tǒng)時間也可以看到是東8區(qū)(+0800)：
$ date -R Tue, 23 Apr 2019 11:22:47 +0800
因此我們輸入1970-01-01 08:00:00時MySQL會糾正為1970-01-01 00:00:00，而成為一個非法值。
3、非法時間值
對于非法的時間值，針對不同的時間類型，MySQL會將其轉為合適的值：0000-00-00 或 0000-00-00 00:00:00。
比如月份為1-12月，當你嘗試插入2019-13-01 00:00:00時，就會被糾正為0000-00-00 00:00:00，因為不存在13月，為非法值。
4、嚴格模式
當我們插入非法時間值時，雖然會被糾正，但是在嚴格模式下，不會插入數據，反而會報錯：
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 08:00:00' for column 'time' at row 1
我們可以通過設置模式，來調整MySQL的行為，首先查看MySQL的模式：
mysql> show variables like '%sql_mode%'; +----------------------------+--------------------------------------------+ | Variable_name | Value | +----------------------------+--------------------------------------------+ | | sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +----------------------------+--------------------------------------------+
在這個模式下，非法時間會直接報錯，我們可以調整模式為ALLOW_INVALID_DATES：
mysql> set session sql_mode = 'ALLOW_INVALID_DATES'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%sql_mode%'; +---------------+---------------------+ | Variable_name | Value | +---------------+---------------------+ | sql_mode | ALLOW_INVALID_DATES | +---------------+---------------------+ 1 row in set (0.00 sec)
在這個模式下，不會再完備檢查日期的合法性，只會檢查月份的范圍在1-12，日期在1-31。這在處理用戶輸入的時候很合適，但是這個模式只對于DATE和DATETIME很合適，對于TIMESTAMP，依然需要一個合法的值，否則就會糾正為0000-00-00 00:00:00。
在非法值時，如果這個模式啟用，就會報錯；如果禁用，就會糾正為0000-00-00 00:00:00并產生一個警告：
mysql> insert into alarm_service values (7, '1970-01-01 08:00:00'); Query OK, 1 row affected, 1 warning (0.00 sec)
總結：
對于這種問題，有兩種解決方法：
- 調整時間為合法范圍
- 調整MySQL嚴格模式，允許非法時間
5、case匯總
ERROR 1067 (42000): Invalid default value for 'createTime'
查看原因發(fā)現設置為：
# 查看創(chuàng)建表單的語句 CREATE TABLE `dimensionsConf` ( `id` int(11) NOT NULL AUTO_INCREMENT, `createTime` datetime DEFAULT CURRENT_TIMESTAMP, ) ENGINE=InnoDB AUTO_INCREMENT=178 DEFAULT CHARSET=utf8; # 查看數據庫版本 $mysql --version mysql Ver 14.14 Distrib 5.1.30, for unknown-linux-gnu (x86_64) using EditLine wrapper
到此這篇關于MySQL時間類型和模式詳情的文章就介紹到這了,更多相關MySQL時間類型和模式內容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持本站！
版權聲明：本站文章來源標注為YINGSOO的內容版權均為本站所有，歡迎引用、轉載，請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網站，禁止在非maisonbaluchon.cn所屬的服務器上建立鏡像，否則將依法追究法律責任。本站部分內容來源于網友推薦、互聯網收集整理而來，僅供學習參考，不代表本站立場，如有內容涉嫌侵權，請聯系alex-e#qq.com處理。
關注官方微信