这篇文章主要讲解了“MySQL中日期为零值的处理方法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL中日期为零值的处理方法”吧！

前言：

前面文章我们介绍过日期和时间字段的查询方法，最近遇到日期值为零的问题。原来了解过和 sql_mode 参数设置有关，但还不是特别清楚，本篇文章将探究下MySQL怎么处理日期值为零的问题。

这里我们说的日期为零值是指年、月、日为零，即’0000-00-00’。显然，这是不合法的日期值，但由于设计问题或历史遗留问题，有时候数据库中有类似日期值为零的数据，默认情况下插入零值日期会报错，可以通过修改参数sql_mode模式来避免该问题。下面展示下默认情况下插入零值的情况：

#首先创建测试表CREATETABLE`t_zerodate`(`increment_id`intunsignedNOTNULLAUTO_INCREMENTCOMMENT'自增主键',`year_col`yearDEFAULTNULLCOMMENT'年',`date_col`dateDEFAULTNULLCOMMENT'日期',`dt_col`datetimeDEFAULTNULLCOMMENT'datetime时间',`ts_col`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'timestamp时间',PRIMARYKEY(`increment_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='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>insertintot_zerodate(year_col)values(0000);QueryOK,1rowaffected(0.02sec)#月、日都不为零时可插入成功mysql>insertintot_zerodate(date_col)values('0000-00-00');ERROR1292(22007):Incorrectdatevalue:'0000-00-00'forcolumn'date_col'atrow1mysql>insertintot_zerodate(date_col)values('0000-01-00');ERROR1292(22007):Incorrectdatevalue:'0000-01-00'forcolumn'date_col'atrow1mysql>insertintot_zerodate(date_col)values('0000-00-01');ERROR1292(22007):Incorrectdatevalue:'0000-00-01'forcolumn'date_col'atrow1mysql>insertintot_zerodate(date_col)values('0000-01-01');QueryOK,1rowaffected(0.02sec)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#上文已经测试过，正常情况下月、日都不为零时可插入成功#下面测试insertignoreintomysql>setsessionsql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";QueryOK,0rowsaffected(0.00sec)mysql>truncatetablet_zerodate;QueryOK,0rowsaffected(0.07sec)mysql>insertignoreintot_zerodate(date_col)values('0000-00-00');QueryOK,1rowaffected,1warning(0.01sec)mysql>insertignoreintot_zerodate(date_col)values('2010-00-00');QueryOK,1rowaffected,1warning(0.01sec)mysql>insertignoreintot_zerodate(date_col)values('2010-01-00');QueryOK,1rowaffected,1warning(0.01sec)mysql>insertignoreintot_zerodate(date_col)values('2010-00-01');QueryOK,1rowaffected,1warning(0.01sec)mysql>selectdate_colfromt_zerodate;+------------+|date_col|+------------+|0000-00-00||0000-00-00||0000-00-00||0000-00-00|+------------+#结论：正常情况下月、日都不为零时可插入成功；对于INSERTIGNORE则允许插入，但会产生告警，并会将零值日期保存为'0000-00-00'。2.严格模式下不启用NO_ZERO_DATEmysql>setsessionsql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";QueryOK,0rowsaffected,1warning(0.00sec)mysql>truncatetablet_zerodate;QueryOK,0rowsaffected(0.08sec)mysql>insertintot_zerodate(date_col)values('0000-00-00');QueryOK,1rowaffected(0.01sec)mysql>insertintot_zerodate(date_col)values('2010-00-01');ERROR1292(22007):Incorrectdatevalue:'2010-00-01'forcolumn'date_col'atrow1mysql>insertintot_zerodate(date_col)values('2010-01-00');ERROR1292(22007):Incorrectdatevalue:'2010-01-00'forcolumn'date_col'atrow1mysql>insertintot_zerodate(date_col)values('0000-00-01');ERROR1292(22007):Incorrectdatevalue:'0000-00-01'forcolumn'date_col'atrow1mysql>insertintot_zerodate(date_col)values('0000-01-00');ERROR1292(22007):Incorrectdatevalue:'0000-01-00'forcolumn'date_col'atrow1#结论：'0000-00-00'可以正常插入，其余月、日为零的日期插入会报错；对于INSERTIGNORE则允许插入，但会产生告警，并会将零值日期保存为'0000-00-00'。3.严格模式下不启用NO_ZERO_IN_DATEmysql>setsessionsql_mode="STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";QueryOK,0rowsaffected,1warning(0.00sec)mysql>truncatetablet_zerodate;QueryOK,0rowsaffected(0.07sec)mysql>insertintot_zerodate(date_col)values('0000-00-00');ERROR1292(22007):Incorrectdatevalue:'0000-00-00'forcolumn'date_col'atrow1mysql>insertintot_zerodate(date_col)values('2010-00-00');QueryOK,1rowaffected(0.01sec)mysql>insertintot_zerodate(date_col)values('2010-00-01');QueryOK,1rowaffected(0.00sec)mysql>insertintot_zerodate(date_col)values('2010-01-00');QueryOK,1rowaffected(0.00sec)mysql>insertignoreintot_zerodate(date_col)values('0000-00-00');QueryOK,1rowaffected,1warning(0.01sec)mysql>selectdate_colfromt_zerodate;+------------+|date_col|+------------+|2010-00-00||2010-00-01||2010-01-00||0000-00-00|+------------+#结论：'0000-00-00'插入报错，其余月、日为零的日期可以正常插入；对于INSERTIGNORE则允许插入'0000-00-00'，但会产生告警。4.严格模式下NO_ZERO_DATE和NO_ZERO_IN_DATE都不启用mysql>setsessionsql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";QueryOK,0rowsaffected,1warning(0.00sec)mysql>truncatetablet_zerodate;QueryOK,0rowsaffected(0.09sec)mysql>insertintot_zerodate(date_col)values('0000-00-00');QueryOK,1rowaffected(0.01sec)mysql>insertintot_zerodate(date_col)values('2010-00-00');QueryOK,1rowaffected(0.01sec)mysql>insertintot_zerodate(date_col)values('2010-00-01');QueryOK,1rowaffected(0.01sec)mysql>insertintot_zerodate(date_col)values('2010-01-00');QueryOK,1rowaffected(0.01sec)#结论：'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中日期为零值的处理方法这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！