MySQL 关于日期为零值的处理
前言:
前面文章我们介绍过日期和时间字段的查询方法,最近遇到日期值为零的问题。原来了解过和 sql_mode 参数设置有关,但还不是特别清楚,本篇文章将探究下MySQL怎么处理日期值为零的问题。
1.问题描述
这里我们说的日期为零值是指年、月、日为零,即'0000-00-00'。显然,这是不合法的日期值,但由于设计问题或历史遗留问题,有时候数据库中有类似日期值为零的数据,默认情况下插入零值日期会报错,可以通过修改参数sql_mode模式来避免该问题。下面展示下默认情况下插入零值的情况:
2.sql_mode变更测试
关于sqlmode,原来写过一篇文章,sqlmode支持多个变量的不同组合,不同的sql_mode影响服务端支持的SQL语法以及数据校验规则。其中 NO_ZERO_IN_DATE
、NO_ZERO_DATE
这两个变量影响MySQL对日期零值的处理。上面测试中可以发现,严格模式下,当sqlmode中包含NOZERO_IN_DATE,NOZERODATE两个变量时,月和日都不为零时可以插入成功。
乍一看,NOZEROIN_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'并产生警告。
同时,官方文档中指出:NOZERODATE和NOZEROIN_DATE虽然不是严格模式的一部分,但应与严格模式结合使用,如果在未启用严格模式的情况下启用了NO_ZERODATE或NOZERO_IN_DATE则会产生警告,反之亦然,(sqlmode中包含STRICTTRANS_TABLES,一般可认为启用了严格模式)。
下面我们来测试下,严格模式下分别启用和不启用这两个变量的效果:
3.结论及建议
简单总结下,NOZERODATE模式影响'0000-00-00'日期的插入,NOZEROIN_DATE模式影响除'0000-00-00'外的月、日为零的日期的插入。另外无论何种模式,YEAR类型都允许0000插入,这两个变量影响的是DATE、DATETIME、TIMESTAMP三种字段类型中对日期部分为零的处理。
至于我们是否要启用这两种模式,这取决于业务需求。如果你的业务有插入零值日期的需求,则可以选择sqlmode中不要包含NOZERO_DATE和NO_ZEROINDATE,例如,某字段要求设置为DATE类型且不为空,默认值设为'0000-00-00'。一般情况下,NOZERODATE和NOZEROIN_DATE建议同时有或者同时没有,有插入零日期值的需求则可以去除二者,没有此类需要则可以保留二者。这里提醒下,官方文档中讲到,这两个变量在未来版本中不再作为独立变量使用,故官方不推荐使用。
总结:
写了这么多,不知道你认真看了多少,其实本篇文章讲的东西还是比较简单的。如果你遇到过此类问题,再看下本篇文章可能理解会更深刻些,没遇过此类问题的小伙伴,希望这篇文章可以让你知道MySQL对于零值日期有不同的处理。
版权声明: 本文为 InfoQ 作者【Simon】的原创文章。
原文链接:【http://xie.infoq.cn/article/0bbf4a3e129ebdd45210d0f45】。文章转载请联系作者。
评论