快捷導(dǎo)航

MySQL時(shí)間類型和模式詳情

更新時(shí)間：2021年09月26日 10:45:44 作者：趙帥強(qiáng)

這篇文章主要介紹MySQL時(shí)間類型和模式 MySQL會(huì)在存儲(chǔ)時(shí)將數(shù)據(jù)值轉(zhuǎn)換為UTC標(biāo)準(zhǔn)時(shí)間來存儲(chǔ)，讀取時(shí)再轉(zhuǎn)為當(dāng)前時(shí)間。如果你的時(shí)區(qū)沒有發(fā)生改變，則該值就是你存儲(chǔ)的值，如果你改變了時(shí)區(qū)，讀取到的值就會(huì)發(fā)生變化。這個(gè)特性不會(huì)對(duì)DATETIME生效,需要的朋友可以參考一下

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

# 查看表結(jié)構(gòu)
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ā)現(xiàn)錯(cuò)誤信息提示是時(shí)間值錯(cuò)誤，但是我們這明顯是一個(gè)合法的時(shí)間點(diǎn)啊。

經(jīng)過查詢資料，發(fā)現(xiàn)原因是在MySQL中，timestamp類型的合法區(qū)間是1970-01-01 00:00:01 - 2038-01-19 03:14:07 UTC，而在存儲(chǔ)是，會(huì)先將你插入的數(shù)據(jù)轉(zhuǎn)換為UTC時(shí)間，然后存儲(chǔ)起來，讀取的時(shí)候，再轉(zhuǎn)換為你的本地時(shí)間。由于我的時(shí)區(qū)為東八區(qū)，因此轉(zhuǎn)換后就變?yōu)榱?code>1970-01-01 00:00:00 UTC，成為了非法時(shí)間。

解決方案為：

調(diào)整時(shí)間為合法范圍
調(diào)整MySQL嚴(yán)格模式，允許非法時(shí)間

下面我們?cè)敿?xì)說明相關(guān)的內(nèi)容。

1、MySQL時(shí)間類型

MySQL時(shí)間類型分為三種：

DATE：用于只包含日期不包含時(shí)間的時(shí)候，MySQL會(huì)將格式轉(zhuǎn)換為YYYY-MM-DD，合法范圍為1000-01-01 - 9999-12-31。
DATETIME：用于包含日期+時(shí)間的時(shí)候，格式為YYYY-MM-DD HH:MM:SS，合法范圍為1000-01-01 00:00:00 - 9999-12-31 23:59:59。
TIMESTAMP：用于包含日期+時(shí)間的時(shí)候，格式為YYYY-MM-DD HH:MM:SS，合法范圍為1997-01-01 00:00:01 - 2038-01-19 03:14:07 UTC。

同時(shí)，DATETIME和TIMESTAMP還都支持一個(gè)6位微秒的數(shù)據(jù)支持，格式為YYYY-MM-DD HH:MM:SS[.fraction] ，合法范圍為.000000 - .999999。

DATETIME和TIMESTAMP還都提供自動(dòng)初始化并更新為當(dāng)前日期和時(shí)間的數(shù)據(jù)。

對(duì)于TIMESTAMP類型，MySQL會(huì)在存儲(chǔ)時(shí)將數(shù)據(jù)值轉(zhuǎn)換為UTC標(biāo)準(zhǔn)時(shí)間來存儲(chǔ)，讀取時(shí)再轉(zhuǎn)為當(dāng)前時(shí)間。如果你的時(shí)區(qū)沒有發(fā)生改變，則該值就是你存儲(chǔ)的值，如果你改變了時(shí)區(qū)，讀取到的值就會(huì)發(fā)生變化。這個(gè)特性不會(huì)對(duì)DATETIME生效。

2、查看時(shí)區(qū)

mysql> show variables like '%zone%';                                       
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

可以看到當(dāng)前設(shè)置的時(shí)區(qū)是SYSTEM，即跟操作系統(tǒng)保持一致，同時(shí)系統(tǒng)的時(shí)區(qū)是CST(China Standard Time 北京標(biāo)準(zhǔn)時(shí)間)，查看系統(tǒng)時(shí)間也可以看到是東8區(qū)(+0800)：

$ date -R
Tue, 23 Apr 2019 11:22:47 +0800

因此我們輸入1970-01-01 08:00:00時(shí)MySQL會(huì)糾正為1970-01-01 00:00:00，而成為一個(gè)非法值。

3、非法時(shí)間值

對(duì)于非法的時(shí)間值，針對(duì)不同的時(shí)間類型，MySQL會(huì)將其轉(zhuǎn)為合適的值：0000-00-00 或 0000-00-00 00:00:00。

比如月份為1-12月，當(dāng)你嘗試插入2019-13-01 00:00:00時(shí)，就會(huì)被糾正為0000-00-00 00:00:00，因?yàn)椴淮嬖?3月，為非法值。

4、嚴(yán)格模式

當(dāng)我們插入非法時(shí)間值時(shí)，雖然會(huì)被糾正，但是在嚴(yán)格模式下，不會(huì)插入數(shù)據(jù)，反而會(huì)報(bào)錯(cuò)：

ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 08:00:00' for column 'time' at row 1

我們可以通過設(shè)置模式，來調(diào)整MySQL的行為，首先查看MySQL的模式：

mysql> show variables like '%sql_mode%';            
+----------------------------+--------------------------------------------+
| Variable_name              | Value                                      |
+----------------------------+--------------------------------------------+                               |
| sql_mode                   | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+----------------------------+--------------------------------------------+

在這個(gè)模式下，非法時(shí)間會(huì)直接報(bào)錯(cuò)，我們可以調(diào)整模式為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)

在這個(gè)模式下，不會(huì)再完備檢查日期的合法性，只會(huì)檢查月份的范圍在1-12，日期在1-31。這在處理用戶輸入的時(shí)候很合適，但是這個(gè)模式只對(duì)于DATE和DATETIME很合適，對(duì)于TIMESTAMP，依然需要一個(gè)合法的值，否則就會(huì)糾正為0000-00-00 00:00:00。

在非法值時(shí)，如果這個(gè)模式啟用，就會(huì)報(bào)錯(cuò)；如果禁用，就會(huì)糾正為0000-00-00 00:00:00并產(chǎn)生一個(gè)警告：

mysql> insert into alarm_service values (7, '1970-01-01 08:00:00'); 
Query OK, 1 row affected, 1 warning (0.00 sec)

總結(jié)：

對(duì)于這種問題，有兩種解決方法：

調(diào)整時(shí)間為合法范圍
調(diào)整MySQL嚴(yán)格模式，允許非法時(shí)間

5、case匯總

ERROR 1067 (42000): Invalid default value for 'createTime'

查看原因發(fā)現(xiàn)設(shè)置為：

# 查看創(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;

# 查看數(shù)據(jù)庫(kù)版本
$mysql --version
mysql  Ver 14.14 Distrib 5.1.30, for unknown-linux-gnu (x86_64) using  EditLine wrapper

到此這篇關(guān)于MySQL時(shí)間類型和模式詳情的文章就介紹到這了,更多相關(guān)MySQL時(shí)間類型和模式內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: