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

以上就是mysql sql_mode的使用详解的详细内容，更多关于mysql sql_mode的使用的资料请关注其它相关文章！