這篇文章主要講解了“MySQL中日期為零值的處理方法”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“MySQL中日期為零值的處理方法”吧！

在潞城等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場(chǎng)前瞻性、產(chǎn)品創(chuàng)新能力，以專(zhuān)注、極致的服務(wù)理念，為客戶提供做網(wǎng)站、成都網(wǎng)站設(shè)計(jì) 網(wǎng)站設(shè)計(jì)制作按需定制網(wǎng)站,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),成都品牌網(wǎng)站建設(shè),成都全網(wǎng)營(yíng)銷(xiāo)推廣,成都外貿(mào)網(wǎng)站建設(shè)公司,潞城網(wǎng)站建設(shè)費(fèi)用合理。

前言：

前面文章我們介紹過(guò)日期和時(shí)間字段的查詢(xún)方法，最近遇到日期值為零的問(wèn)題。原來(lái)了解過(guò)和 sql_mode 參數(shù)設(shè)置有關(guān)，但還不是特別清楚，本篇文章將探究下MySQL怎么處理日期值為零的問(wèn)題。

1.問(wèn)題描述

這里我們說(shuō)的日期為零值是指年、月、日為零，即’0000-00-00’。顯然，這是不合法的日期值，但由于設(shè)計(jì)問(wèn)題或歷史遺留問(wèn)題，有時(shí)候數(shù)據(jù)庫(kù)中有類(lèi)似日期值為零的數(shù)據(jù)，默認(rèn)情況下插入零值日期會(huì)報(bào)錯(cuò)，可以通過(guò)修改參數(shù)sql_mode模式來(lái)避免該問(wèn)題。下面展示下默認(rèn)情況下插入零值的情況：

# 首先創(chuàng)建測(cè)試表
CREATE TABLE `t_zerodate` (
  `increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  `year_col` year DEFAULT NULL COMMENT '年',
  `date_col` date DEFAULT NULL COMMENT '日期',
  `dt_col` datetime DEFAULT NULL COMMENT 'datetime時(shí)間',
  `ts_col` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'timestamp時(shí)間',
  PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='zerodate';
# 查看sql_mode模式
mysql> select @@sql_mode;
+----------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                         |
+----------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------+
# 分別插入年、月、日為零值測(cè)試
# 年份為0000 插入成功
mysql> insert into t_zerodate (year_col) values (0000);
Query OK, 1 row affected (0.02 sec)
# 月、日都不為零時(shí) 可插入成功
mysql> insert into t_zerodate (date_col) values ('0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date_col' at row 1
mysql> insert into t_zerodate (date_col) values ('0000-01-00'); 
ERROR 1292 (22007): Incorrect date value: '0000-01-00' for column 'date_col' at row 1
mysql> insert into t_zerodate (date_col) values ('0000-00-01');
ERROR 1292 (22007): Incorrect date value: '0000-00-01' for column 'date_col' at row 1
mysql> insert into t_zerodate (date_col) values ('0000-01-01'); 
Query OK, 1 row affected (0.02 sec)

2.sql_mode變更測(cè)試

關(guān)于sql_mode，原來(lái)寫(xiě)過(guò)一篇文章，sql_mode支持多個(gè)變量的不同組合，不同的sql_mode影響服務(wù)端支持的SQL語(yǔ)法以及數(shù)據(jù)校驗(yàn)規(guī)則。其中 NO_ZERO_IN_DATE、NO_ZERO_DATE這兩個(gè)變量影響MySQL對(duì)日期零值的處理。上面測(cè)試中可以發(fā)現(xiàn)，嚴(yán)格模式下，當(dāng)sql_mode中包含NO_ZERO_IN_DATE,NO_ZERO_DATE兩個(gè)變量時(shí)，月和日都不為零時(shí)可以插入成功。

乍一看，NO_ZERO_IN_DATE和NO_ZERO_DATE兩個(gè)變量很相似，但作用有什么不同呢？下面我們給出這兩個(gè)變量的作用并做下具體測(cè)試。

NO_ZERO_DATE模式影響服務(wù)端是否允許將 ‘0000-00-00’ 作為有效日期。其效果還取決于sql_mode是否啟用了嚴(yán)格模式。

• 如果未啟用此模式，’0000-00-00’則允許插入并且不會(huì)產(chǎn)生警告。

• 如果只啟用此模式，’0000-00-00’則允許插入但是會(huì)產(chǎn)生警告。

• 如果啟用了此模式和嚴(yán)格模式，’0000-00-00’則會(huì)被認(rèn)定為非法，并且插入也會(huì)產(chǎn)生錯(cuò)誤。除非同時(shí)帶有IGNORE，對(duì)于 INSERT IGNORE和UPDATE IGNORE，’0000-00-00’則允許插入但是會(huì)產(chǎn)生警告。

NO_ZERO_IN_DATE模式影響服務(wù)端是否允許插入年份部分非零但月或日部分為0的日期。（例如’2010-00-01’或 ‘2010-01-00’，但不影響日期’0000-00-00’），其效果同樣還取決于sql_mode是否啟用了嚴(yán)格模式。

• 如果未啟用此模式，則允許部分為零的日期插入，并且不會(huì)產(chǎn)生任何警告。

• 如果只啟用此模式，則將該零值日期插入為’0000-00-00’并產(chǎn)生警告。

• 如果啟用了此模式和嚴(yán)格模式，則除非IGNORE同時(shí)指定，否則不允許插入為零的日期。對(duì)于INSERT IGNORE和 UPDATE IGNORE，將該零值日期插入為’0000-00-00’并產(chǎn)生警告。

同時(shí)，官方文檔中指出：NO_ZERO_DATE和NO_ZERO_IN_DATE雖然不是嚴(yán)格模式的一部分，但應(yīng)與嚴(yán)格模式結(jié)合使用，如果在未啟用嚴(yán)格模式的情況下啟用了NO_ZERO_DATE或NO_ZERO_IN_DATE則會(huì)產(chǎn)生警告，反之亦然，（sql_mode中包含STRICT_TRANS_TABLES，一般可認(rèn)為啟用了嚴(yán)格模式）。

下面我們來(lái)測(cè)試下，嚴(yán)格模式下分別啟用和不啟用這兩個(gè)變量的效果：

1.嚴(yán)格模式下 同時(shí)啟用NO_ZERO_DATE、NO_ZERO_IN_DATE
# 上文已經(jīng)測(cè)試過(guò)，正常情況下 月、日都不為零時(shí) 可插入成功
# 下面測(cè)試insert ignore into
mysql> set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table t_zerodate;
Query OK, 0 rows affected (0.07 sec)
mysql> insert ignore into t_zerodate (date_col) values ('0000-00-00');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert ignore into t_zerodate (date_col) values ('2010-00-00');    
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert ignore into t_zerodate (date_col) values ('2010-01-00'); 
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert ignore into t_zerodate (date_col) values ('2010-00-01');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select date_col from t_zerodate;
+------------+
| date_col   |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
+------------+
# 結(jié)論：
正常情況下 月、日都不為零時(shí) 可插入成功；
對(duì)于INSERT IGNORE則允許插入，但會(huì)產(chǎn)生告警，并會(huì)將零值日期保存為'0000-00-00'。
2.嚴(yán)格模式下 不啟用NO_ZERO_DATE
mysql> set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> truncate table t_zerodate;
Query OK, 0 rows affected (0.08 sec)
mysql> insert into t_zerodate (date_col) values ('0000-00-00');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_zerodate (date_col) values ('2010-00-01');
ERROR 1292 (22007): Incorrect date value: '2010-00-01' for column 'date_col' at row 1
mysql> insert into t_zerodate (date_col) values ('2010-01-00'); 
ERROR 1292 (22007): Incorrect date value: '2010-01-00' for column 'date_col' at row 1
mysql> insert into t_zerodate (date_col) values ('0000-00-01');
ERROR 1292 (22007): Incorrect date value: '0000-00-01' for column 'date_col' at row 1
mysql> insert into t_zerodate (date_col) values ('0000-01-00');
ERROR 1292 (22007): Incorrect date value: '0000-01-00' for column 'date_col' at row 1
# 結(jié)論：
'0000-00-00'可以正常插入，其余月、日為零的日期插入會(huì)報(bào)錯(cuò)；
對(duì)于INSERT IGNORE則允許插入，但會(huì)產(chǎn)生告警，并會(huì)將零值日期保存為'0000-00-00'。
3.嚴(yán)格模式下 不啟用NO_ZERO_IN_DATE
mysql> set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> truncate table t_zerodate;
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t_zerodate (date_col) values ('0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date_col' at row 1
mysql> insert into t_zerodate (date_col) values ('2010-00-00');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_zerodate (date_col) values ('2010-00-01');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_zerodate (date_col) values ('2010-01-00');
Query OK, 1 row affected (0.00 sec)
mysql> insert ignore into t_zerodate (date_col) values ('0000-00-00');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select date_col from t_zerodate;
+------------+
| date_col   |
+------------+
| 2010-00-00 |
| 2010-00-01 |
| 2010-01-00 |
| 0000-00-00 |
+------------+
# 結(jié)論：
'0000-00-00'插入報(bào)錯(cuò)，其余月、日為零的日期可以正常插入；
對(duì)于INSERT IGNORE則允許插入'0000-00-00'，但會(huì)產(chǎn)生告警。
4.嚴(yán)格模式下 NO_ZERO_DATE和NO_ZERO_IN_DATE都不啟用
mysql> set session sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> truncate table t_zerodate;
Query OK, 0 rows affected (0.09 sec)
mysql> insert into t_zerodate (date_col) values ('0000-00-00');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_zerodate (date_col) values ('2010-00-00');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_zerodate (date_col) values ('2010-00-01');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_zerodate (date_col) values ('2010-01-00');
Query OK, 1 row affected (0.01 sec)
# 結(jié)論：
'0000-00-00'和其余月、日為零的日期都可以正常插入。

3.結(jié)論及建議

簡(jiǎn)單總結(jié)下，NO_ZERO_DATE模式影響’0000-00-00’日期的插入，NO_ZERO_IN_DATE模式影響除’0000-00-00’外的月、日為零的日期的插入。另外無(wú)論何種模式，YEAR類(lèi)型都允許0000插入，這兩個(gè)變量影響的是DATE、DATETIME、TIMESTAMP三種字段類(lèi)型中對(duì)日期部分為零的處理。

至于我們是否要啟用這兩種模式，這取決于業(yè)務(wù)需求。如果你的業(yè)務(wù)有插入零值日期的需求，則可以選擇sql_mode中不要包含NO_ZERO_DATE和NO_ZERO_IN_DATE，例如，某字段要求設(shè)置為DATE類(lèi)型且不為空，默認(rèn)值設(shè)為’0000-00-00’。一般情況下，NO_ZERO_DATE和NO_ZERO_IN_DATE建議同時(shí)有或者同時(shí)沒(méi)有，有插入零日期值的需求則可以去除二者，沒(méi)有此類(lèi)需要?jiǎng)t可以保留二者。這里提醒下，官方文檔中講到，這兩個(gè)變量在未來(lái)版本中不再作為獨(dú)立變量使用，故官方不推薦使用。

感謝各位的閱讀，以上就是“MySQL中日期為零值的處理方法”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)MySQL中日期為零值的處理方法這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！

分享標(biāo)題：MySQL中日期為零值的處理方法
網(wǎng)頁(yè)鏈接：http://jinyejixie.com/article2/pgicic.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、服務(wù)器托管、外貿(mào)建站、網(wǎng)站導(dǎo)航、移動(dòng)網(wǎng)站建設(shè)、網(wǎng)站策劃

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)