前言

最近，有开发同事联系我反馈一个问题，说开发环境出现了数据丢失的情况，想让DBA帮忙排查一下是不是数据库的问题。我心想大概率是程序bug，不太可能是数据库的问题。

不过还是要排查一下才会心安，毕竟对于一个DBA而言，数据丢失无疑是最令人紧张的一件事情。

问题排查

开始进行排查之前，我先确认了如下问题：

（1）DB实例状态是不是正常的？               
结果：实例状态正常。

（2）开发环境是否还存在？相关表是否存在？            
结果：存在。

（3）应用用户的权限是否正常？               
结果：应用用户拥有所有权限。

（4）业务访问是报什么错？              
 结果：业务侧是访问某些页面报错。

（5）出现问题前系统是否有做什么变更？               
结果：当晚DDL变更有涉及到该业务表的操作，变更内容为修改字段长度，类似alter table xxx modify column xxx char(x)。

（6）该业务表是否有主键？应用日志是否清晰？            
结果：该表有主键，日志未很清晰打印出报错信息。

问题到这里开始有了些许眉目，接下去开始排查sql_mode配置、查询相应的完整行记录给开发确认，最终确认是DDL变更导致字段被截断，最后通过备份进行恢复，问题最终得到解决。

案例复现

为什么修改字段长度对导致数据被截断？MySQL难道不会不会做数据校验吗？相信很多朋友都会有此疑问，让我们把问题复现出来看看。

（1）场景1

（2）场景2

我们期望出现的是场景1，直接报错“数据被截断”。

场景2是执行成功，但导致“数据部分丢失”。

MySQL是没有进行数据校验吗？其实MySQL都有对数据进行校验的，只是在场景2中，因为sql_mode配置有问题，没有设置STRICT_TRANS_TABLES，导致MySQL没有阻止该操作执行，从而导致“数据丢失”惨案。

sql_mode详解

sql model 常用来解决下面几类问题

(1) 通过设置sql mode, 可以完成不同严格程度的数据校验，有效地保障数据准备性。

(2) 通过设置sql model 为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据库之间进行迁移时，则不需要对业务sql 进行较大的修改。

(3) 在不同数据库之间进行数据迁移之前，通过设置SQL Mode 可以使MySQL 上的数据更方便地迁移到目标数据库中。既然sql_mode是一个非常关键的配置，接下来就带来该配置项的详细解析。

sql_mode最重要的选项：

（1）ANSI：该选项决定SQL语法支持，设置为ANSI，会更加遵守标准SQL语法。

（2）STRICT_TRANS_TABLES：该选项决定数据校验；对于事务性存储引擎，当出现非法值时，该事务会失败并回滚；对于非事务性存储引擎，如果非法值出现在第一行，那么该事务会失败，如果非法值出现在中间，那么会调整非法值，并抛出告警。

（3）TRADITIONAL：该选项决定与传统关系型数据库表现一致；对于一些非正常操作，直接报错失败而不是告警提示。

sql_mode还包括以下选项：

（4）ALLOW_INVALID_DATES：该选项决定不进行严格的日期校验；它只校验月份范围是否为1-12、日期范围是否为1-31，不校验具体日期是否有效，比如2020-04-31这个非法日期是允许的。

（5）ANSI_QUOTES：该选项决定引用字符；它允许双引号”作为引用字符，和反引号`一样。

（6）ERROR_FOR_DIVISION_BY_ZERO：该选项决定被零整除的返回值；如果不启用，那么被零整除的返回值为null且不告警；如果启用但在非严格模式下，那么被零整除的返回值为null且产生告警；如果启用且在严格模式下，那么被零整除会直接报错。

（7）HIGH_NOT_PRECEDENCE：该选项决定not操作的优先级；启用后，NOT a BETWEEN b AND c被解析为NOT (a BETWEEN b AND c)；在一些旧版本中，NOT a BETWEEN b AND c被解析为(NOT a) BETWEEN b AND c。

（8）IGNORE_SPACE：该选项决定忽略函数名和括号之间的空格；启用后，比如count (*)也不会报错。

（9）NO_AUTO_CREATE_USER：该选项决定grant语句不会自动创建用户；已过时，grant语句也不会创建用户。

（10）NO_AUTO_VALUE_ON_ZERO：该选项决定自增列的生成；一般来说，向自增列插入0或null，系统会自动生成下一个自增值插入；启用后，向自增列插入0会保留原值0，插入null才会自动生成下一个自增值插入

（11）NO_BACKSLASH_ESCAPES：该选项决定反斜杠的作用；启用后，反斜杠不再作为转义字符，而是用作普通字符。

（12）NO_DIR_IN_CREATE：该选项决定忽略创建表时，所有的INDEX DIRECTORY和DATA DIRECTORY指令；该选项只在从库生效。

（13）NO_ENGINE_SUBSTITUTION：该选项决定创建表时，如果指定一个不存在/不支持的存储引擎，那么会自动转换为默认存储引擎。

（14）NO_FIELD_OPTIONS：已过时。

（15）NO_KEY_OPTIONS：已过时。

（16）NO_TABLE_OPTIONS：已过时。

（17）NO_UNSIGNED_SUBTRACTION：一般情况下，整数之间的减法(其中一个为无符号)，结果为无符号，如果结果为负数则报错；启用该选项后，负数则可以正常处理。

（18）NO_ZERO_DATE：该选项决定‘0000-00-00’是否可以插入；如果不启用，那么‘0000-00-00’可以插入且不告警；如果启用但在非严格模式下，那么‘0000-00-00’可以插入但会产生告警；如果启用且在非严格模式下，那么‘0000-00-00’不能插入会直接报错。

（19）NO_ZERO_IN_DATE：该选项决定月份和日期是否可以为00；如果不启用，那么月份和日期可以为00且不告警；如果启用但在非严格模式下，那么月份和日期可以为00但会产生告警；如果启用且在非严格模式下，那么月份和日期不能为00会直接报错。

（20）ONLY_FULL_GROUP_BY：该选项决定select/having/order by后面的非聚合字段，必须出现在group by字句中。

（21）PAD_CHAR_TO_FULL_LENGTH：一般情况下，查询char类型的字段，后面的空洞数据会被裁剪；启用该选项后，查询char类型的字段，后面的空洞数据不会被裁剪。

（22）PIPES_AS_CONCAT：该选项决定将||符号当作字符串的连接操作符，而不是当作OR同义词。

（23）REAL_AS_FLOAT：该选项决定将REAL当作FLOAT同义词，而不是DOUBLE同义词。

（24）STRICT_ALL_TABLES：该选项决定数据校验；对于事务性存储引擎，当出现非法值时，该事务会失败并回滚；对于非事务性存储引擎，如果非法值出现在第一行，那么该事务会失败，如果非法值出现在中间，那么前面操作会成功、后面操作会直接报错，出现事务部分成功部分失败的情况。

总结

这个案例提醒我们，sql_mode是一个非常关键的配置，千万不可随便设置和修改。

关于sql_mode如何设置，个人建议如下：

（1）对于5.5/5.6版本，sql_mode建议参照5.7默认值进行设置；

（2）对于5.7版本，sql_mode保持默认值即可；

（3）对于8.0版本，sql_mode也保持默认值即可。

sql_mode的设置，可以让MySQL非常灵活地运行在各种不同模式下，但与此同时也带来各种各样的风险；在MySQL广泛应用于各类重要系统的情况下，建议是要对sql_mode进行严格审核设置，同时对开发代码进行规范化管理；其实这一点，也可以从官方默认值看出来，随着MySQL版本的迭代，sql_mode的设置也是越来越严格。