加入收藏 | 设为首页 | 会员中心 | 我要投稿 PHP编程网 - 黄冈站长网 (http://www.0713zz.com/)- 数据应用、建站、人体识别、智能机器人、语音技术!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

MySQL中日期为零值的解决方法

发布时间:2022-01-21 12:20:05 所属栏目:MySql教程 来源:互联网
导读:这篇文章主要讲解了MySQL中日期为零值的处理方法,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习MySQL中日期为零值的处理方法吧! 前言: 前面文章我们介绍过日期和时间字段的查询方法,最近遇到日期值为
MySQL中日期为零值的解决方法
         这篇文章主要讲解了“MySQL中日期为零值的处理方法”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“MySQL中日期为零值的处理方法”吧!
 
前言:
 
前面文章我们介绍过日期和时间字段的查询方法,最近遇到日期值为零的问题。原来了解过和 sql_mode 参数设置有关,但还不是特别清楚,本篇文章将探究下MySQL怎么处理日期值为零的问题。
 
1.问题描述
这里我们说的日期为零值是指年、月、日为零,即’0000-00-00’。显然,这是不合法的日期值,但由于设计问题或历史遗留问题,有时候数据库中有类似日期值为零的数据,默认情况下插入零值日期会报错,可以通过修改参数sql_mode模式来避免该问题。下面展示下默认情况下插入零值的情况:
 
# 首先创建测试表
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时间',
  `ts_col` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'timestamp时间',
  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 |
+----------------------------------------------------------------------------------------------------+
# 分别插入年、月、日为零值测试
# 年份为0000 插入成功
mysql> insert into t_zerodate (year_col) values (0000);
Query OK, 1 row affected (0.02 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 ('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变更测试
关于sql_mode,原来写过一篇文章,sql_mode支持多个变量的不同组合,不同的sql_mode影响服务端支持的SQL语法以及数据校验规则。其中 NO_ZERO_IN_DATE、NO_ZERO_DATE这两个变量影响MySQL对日期零值的处理。上面测试中可以发现,严格模式下,当sql_mode中包含NO_ZERO_IN_DATE,NO_ZERO_DATE两个变量时,月和日都不为零时可以插入成功。
 
乍一看,NO_ZERO_IN_DATE和NO_ZERO_DATE两个变量很相似,但作用有什么不同呢?下面我们给出这两个变量的作用并做下具体测试。
 
NO_ZERO_DATE模式影响服务端是否允许将 ‘0000-00-00’ 作为有效日期。其效果还取决于sql_mode是否启用了严格模式。
 
如果未启用此模式,’0000-00-00’则允许插入并且不会产生警告。
如果只启用此模式,’0000-00-00’则允许插入但是会产生警告。
如果启用了此模式和严格模式,’0000-00-00’则会被认定为非法,并且插入也会产生错误。除非同时带有IGNORE,对于 INSERT IGNORE和UPDATE IGNORE,’0000-00-00’则允许插入但是会产生警告。
NO_ZERO_IN_DATE模式影响服务端是否允许插入年份部分非零但月或日部分为0的日期。(例如’2010-00-01’或 ‘2010-01-00’,但不影响日期’0000-00-00’),其效果同样还取决于sql_mode是否启用了严格模式。
 
如果未启用此模式,则允许部分为零的日期插入,并且不会产生任何警告。
如果只启用此模式,则将该零值日期插入为’0000-00-00’并产生警告。
如果启用了此模式和严格模式,则除非IGNORE同时指定,否则不允许插入为零的日期。对于INSERT IGNORE和 UPDATE IGNORE,将该零值日期插入为’0000-00-00’并产生警告。
同时,官方文档中指出:NO_ZERO_DATE和NO_ZERO_IN_DATE虽然不是严格模式的一部分,但应与严格模式结合使用,如果在未启用严格模式的情况下启用了NO_ZERO_DATE或NO_ZERO_IN_DATE则会产生警告,反之亦然,(sql_mode中包含STRICT_TRANS_TABLES,一般可认为启用了严格模式)。
 
下面我们来测试下,严格模式下分别启用和不启用这两个变量的效果:
 
1.严格模式下 同时启用NO_ZERO_DATE、NO_ZERO_IN_DATE
# 上文已经测试过,正常情况下 月、日都不为零时 可插入成功
# 下面测试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 |
+------------+
# 结论:
正常情况下 月、日都不为零时 可插入成功;
对于INSERT IGNORE则允许插入,但会产生告警,并会将零值日期保存为'0000-00-00'。
2.严格模式下 不启用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
# 结论:
'0000-00-00'可以正常插入,其余月、日为零的日期插入会报错;
对于INSERT IGNORE则允许插入,但会产生告警,并会将零值日期保存为'0000-00-00'。
3.严格模式下 不启用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 |
+------------+
# 结论:
'0000-00-00'插入报错,其余月、日为零的日期可以正常插入;
对于INSERT IGNORE则允许插入'0000-00-00',但会产生告警。
4.严格模式下 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)
# 结论:
'0000-00-00'和其余月、日为零的日期都可以正常插入。
3.结论及建议
简单总结下,NO_ZERO_DATE模式影响’0000-00-00’日期的插入,NO_ZERO_IN_DATE模式影响除’0000-00-00’外的月、日为零的日期的插入。另外无论何种模式,YEAR类型都允许0000插入,这两个变量影响的是DATE、DATETIME、TIMESTAMP三种字段类型中对日期部分为零的处理。
 
至于我们是否要启用这两种模式,这取决于业务需求。如果你的业务有插入零值日期的需求,则可以选择sql_mode中不要包含NO_ZERO_DATE和NO_ZERO_IN_DATE,例如,某字段要求设置为DATE类型且不为空,默认值设为’0000-00-00’。一般情况下,NO_ZERO_DATE和NO_ZERO_IN_DATE建议同时有或者同时没有,有插入零日期值的需求则可以去除二者,没有此类需要则可以保留二者。这里提醒下,官方文档中讲到,这两个变量在未来版本中不再作为独立变量使用,故官方不推荐使用。
 
感谢各位的阅读,以上就是“MySQL中日期为零值的处理方法”的内容了,经过本文的学习后,相信大家对MySQL中日期为零值的处理方法这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。

(编辑:PHP编程网 - 黄冈站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读