MySQL的sql_mode设置导致报错1292

在MySQL8.0的一个PXC集群中，默认的sql_mode设置如下：

select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

        

          
        
        
        

          

          AI 代码解读
        
      
      

此时某些insert操作会报1292错：
[ERR] 1292 - Incorrect datetime value
字面意思很明显，是数据格式的问题，默认情况下MySQL都设置了严厉模式，不同于Oracle默认的相对宽松的模式，8.0版本的这些严厉模式包括建表时不允许没有主键，插入日期型数据不能为0或其他非法格式等等，这些模式的设置会导致在数据迁移、兼容性测试时报错，要么修改应用限制以适配数据库，要么修改数据库配置以适配已有的应用。
我们选择去掉date相关的2个限制NO_ZERO_IN_DATE和NO_ZERO_DATE，也去掉STRICT_TRANS_TABLES，以下临时修改数据库全局配置：

set global sql_mode = 'ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

        

          
        
        
        

          

          AI 代码解读
        
      
      

这样后续的会话连接就不错报错了，需要注意的是，在PXC集群需要修改所有的数据库节点，如果只修改一个节点，会因为无法实时同步数据而继续报错。
一劳永逸的方法就是修改数据库配置文件/etc/my.cnf，sql_mode设置如下，重启数据库生效：

sql_mode='ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

        

          
        
        
        

          

          AI 代码解读