MySQL关于日期为零值的报错处理 ：ERROR StdoutPluginCollector - [AnalysisStatistics.analysisStatisticsLog-53] java.sql.SQLIntegrityConstraintViolationException: Column ‘Date’ cannot be null

前言

最近在使用dataX进行sql数据库迁移时，遇到日期值为0000-00-00然后被识别为NULL无法插入的问题，原来了解过是和 sql_mode 参数设置有关，但还不是特别清楚，本篇文章将探究下MySQL怎么处理日期值为零的问题。

问题描述

表Date字段类型为date类型，并且设置了默认值‘1970-01-01’，但在插入时依然报错 java.sql.SQLIntegrityConstraintViolationException: Column ‘Date’ cannot be null；查找资料说，日期为零值是指年、月、日为零，即’0000-00-00’且为不合法的日期值，但由于设计问题或历史遗留问题，有时候数据库中有类似日期值为零的数据，默认情况下插入零值日期会报错，可以通过修改参数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_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，一般可认为启用了严格模式）。但是JDBC在处理sql_mode时会有一些特殊情况，具体请看下文：

解决方法

方法一 永久生效：
在mysql的安装目录下，打开my.cnf文件(windows系统是my.ini文件)，新增 sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION，然后重启mysql。 此方法永久生效，但是一定要重启MySQL服务才会生效！这种方法一般不会采用！！

方法二 临时修改会话或者全局配置中的sql_mode：

--一般sql_mode默认值为：'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
SELECT @@SESSION.sql_mode;  --查询会话sql_mode
set SESSION sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; --设置会话sql_mode
SELECT @@global.sql_mode; --查询全局sql_mode
set GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';  --设置全局sql_mode
set GLOBAL sql_mode='NO_ENGINE_SUBSTITUTION'; --也可以直接设置成非严格模式，这样简单粗暴

以上方法只适合在mysql中解决，无法解决在使用JDBC连接时的情况，比如dataX中或者其他大数据组件中使用JDBC完成数据插入；
在查看了众多文章后终于找到问题的答案；

究极方案：JDBC连接时的解决：
当使用上述方法二修改sql_mode后然后在Navicat执行插入语句是能够通过的。BUT!BUT!通过Java的Jdbc执行后依然报错无法插入：ERROR StdoutPluginCollector -
[AnalysisStatistics.analysisStatisticsLog-53]
java.sql.SQLIntegrityConstraintViolationException: Column ‘Date’ cannot be null，也就是’0000-00-00’为不合法的日期值。此时已然觉得事情并非那么简单，那么难道通过Jdbc执行失败是因为Jdbc会强行设置sql_mode为严格模式？确实，原来JDBC Driver默认会设置会话SQL_MODE='STRICT_TRANS_TABLES’的原因是："enforce JDBC compliance on truncation checks"需要开启"STRICT_TRANS_TABLES"这个SQL_MODE，而在JDBC URL中存在着"jdbcCompliantTruncation"这个参数，该参数可以控制是否开启"enforce JDBC compliance on truncation checks"功能，当我们通过JDBC URL设定"jdbcCompliantTruncation=false"之后，也就不会去默认设置SQL_MODE='STRICT_TRANS_TABLES’了
于是问题解决了！

JDBC如下：

jdbc:mysql://{hostname}:{port}/{database}?jdbcCompliantTruncation=false&sessionVariables=sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
//注：sessionVariables=sql_mode=是在jdbc连接中设置会话级别参数

确保万一还可以加上&zeroDateTimeBehavior=convertToNull参数

jdbc:mysql://{hostname}:{port}/{database}?jdbcCompliantTruncation=false&sessionVariables=sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'&zeroDateTimeBehavior=convertToNull
//注：sessionVariables=sql_mode=是在jdbc连接中设置会话级别参数

嘿嘿，纠结了一天的问题终于解决啦，希望也能帮助到大家！