【MySQL】sql_mode引起的一个问题和总结

【背景】

　　之前项目中，项目组计划将现场的MySQL5.5升级到5.7，以提升主从同步性能、使用半同步复制，以及解决一些现场问题等。安排测试组进行验证，测试同事反馈实验室环境中发现有入库失败，我查看了error_log日志，发现有不少如下报错。

[Err] 1364 - Field `xx_field` doesn't have a default value

 

【排查与分析】

　　业务版本前后都是一样的，好端端的mysql怎么突然就部分表写入失败呢？根据上面的日志很快猜到是 sql_mode 问题： NOT NULL 列没有默认值但代码里也没给值，在非严格模式下，int列默认为0，string列默认为''了，所以不成问题；但在严格模式下，是直接返回失败的。

       那么看看吧，果然如此。

mysql> show variables like "sql_mode";
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+

　　但测试同事反馈并没有更改过该参数。查阅资料，发现：MySQL5.6.6 以后版本默认就是NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES，5.5默认为 '' 。

　　

【解决】　　

　　严格模式是合理的，更能保证系统的健壮性，所以解决方案并不是set global variables去除STRICT_TRANS_TABLES，而是业务侧修改完善，加上默认值。

 

【总结】

　　凡事讲究举一反三，PDCA。

• 本次出现问题的STRICT_TRANS_TABLES模式。 

　　严格模式，进行数据的严格校验，错误数据不能插入，报error错误。 

　　单独指 INSERT、UPDATE出现少值或无效值该如何处理： 

1. 把 '' 传给int，严格模式下非法，若启用非严格模式则变成0，产生一个warning
2. Out Of Range，变成插入最大边界值
3. 非null字段没有默认值，插入记录时该字段缺失时报错。A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition

 

• sql_mode取值很多，官方有打包组合，ANSI、TRADITIONAL，当然，每项也可以单独设置。注：大小写不敏感，都可以。

1. sql_mode='ANSI'

ANSI模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。

更改语法和行为，使其更符合标准SQL，相当于REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE。

2.sql_mode='TRADITIONAL'

TRADITIONAL 模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。

更像传统SQL数据库系统，该模式的简单描述是当在列中插入不正确的值时“给出错误而不是警告”。

相当于 STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION。 

• 其他常用

set session sql_mode = 'no_auto_create_user';

MySQL5.7及之前版本可设置，MySQL8之后为默认设置，先create user才能grant。 

 

set session sql_mode = 'no_zero_in_date';

set session sql_mode = 'no_zero_date';

no_zero_date认为日期 '0000-00-00' 非法，与是否设置后面的严格模式有关。

1. 如果设置了严格模式，则 NO_ZERO_DATE 自然满足。但如果是 INSERT IGNORE 或 UPDATE IGNORE，'0000-00-00'依然允许且只显示warning
2. 如果在非严格模式下，设置了NO_ZERO_DATE，效果与上面一样，'0000-00-00'允许但显示warning；如果没有设置NO_ZERO_DATE，no warning，当做完全合法的值。
3. NO_ZERO_IN_DATE情况与上面类似，不同的是控制日期和天，是否可为 0 ，即 2010-01-00 是否合法。

 

set session sql_mode = 'no_engine_substitution';

使用 ALTER TABLE或CREATE TABLE 指定 ENGINE 时， 需要的存储引擎被禁用或未编译，该如何处理。

启用NO_ENGINE_SUBSTITUTION时，此时直接抛出错误；

不设置此值时，CREATE用默认的存储引擎替代，ATLER不进行更改，并抛出一个 warning。