快捷導(dǎo)航

MySQL時間類型和模式詳情

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

這篇文章主要介紹MySQL時間類型和模式 MySQL會在存儲時將數(shù)據(jù)值轉(zhuǎn)換為UTC標(biāo)準(zhǔn)時間來存儲，讀取時再轉(zhuǎn)為當(dāng)前時間。如果你的時區(qū)沒有發(fā)生改變，則該值就是你存儲的值，如果你改變了時區(qū)，讀取到的值就會發(fā)生變化。這個特性不會對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)錯誤信息提示是時間值錯誤，但是我們這明顯是一個合法的時間點啊。

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

解決方案為：

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

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

1、MySQL時間類型

MySQL時間類型分為三種：

DATE：用于只包含日期不包含時間的時候，MySQL會將格式轉(zhuǎn)換為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位微秒的數(shù)據(jù)支持，格式為YYYY-MM-DD HH:MM:SS[.fraction] ，合法范圍為.000000 - .999999。

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

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

2、查看時區(qū)

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

可以看到當(dāng)前設(shè)置的時區(qū)是SYSTEM，即跟操作系統(tǒng)保持一致，同時系統(tǒng)的時區(qū)是CST(China Standard Time 北京標(biāo)準(zhǔn)時間)，查看系統(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會將其轉(zhuǎn)為合適的值：0000-00-00 或 0000-00-00 00:00:00。

比如月份為1-12月，當(dāng)你嘗試插入2019-13-01 00:00:00時，就會被糾正為0000-00-00 00:00:00，因為不存在13月，為非法值。

4、嚴(yán)格模式

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

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 |
+----------------------------+--------------------------------------------+

在這個模式下，非法時間會直接報錯，我們可以調(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)

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

在非法值時，如果這個模式啟用，就會報錯；如果禁用，就會糾正為0000-00-00 00:00:00并產(chǎn)生一個警告：

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

總結(jié)：

對于這種問題，有兩種解決方法：

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

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ù)庫版本
$mysql --version
mysql  Ver 14.14 Distrib 5.1.30, for unknown-linux-gnu (x86_64) using  EditLine wrapper

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

您可能感興趣的文章: