[背景]
之前项目中, 项目组计划将现场的 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 出现少值或无效值该如何处理:
把 '' 传给 int, 严格模式下非法, 若启用非严格模式则变成 0, 产生一个 warning
Out Of Range, 变成插入最大边界值
非 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' 非法, 与是否设置后面的严格模式有关.
如果设置了严格模式, 则 NO_ZERO_DATE 自然满足. 但如果是 INSERT IGNORE 或 UPDATE IGNORE,'0000-00-00'依然允许且只显示 warning
如果在非严格模式下, 设置了 NO_ZERO_DATE, 效果与上面一样,'0000-00-00'允许但显示 warning; 如果没有设置 NO_ZERO_DATE,no warning, 当做完全合法的值.
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.
来源: http://www.bubuko.com/infodetail-3028367.html