MySQL sql_mode的使用详解

目录

• 前言

• sql_mode详解

• 最重要的选项

• 全部选项

• 总结

前言

相信看过上一篇文章《MySQL案例：一个数据丢失 * 》的童鞋，都应该意识到，sql_mode是一个非常关键的配置，接下来就带来该配置项的详细解析。

sql_mode详解

sql_mode，会直接影响SQL语法支持和数据校验，它包含非常多的选项，其中5.7版本的默认值是

“ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,；ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

，一般不建议进行修改。

最重要的选项

sql_mode最重要的选项，包括以下3个：

（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的设置也是越来越严格。

来源：https://cloud.tencent.com/developer/inventory/2101/article/1759754