MySQL的sql_mode模式简介

前言

今天同事使用数据库时报错,排查问题时发现配置文件里的sql_mode配置被人注释了,所以通过查询资料对这个配置进行了下了解。

介绍

mysql为了支持在不同的环境下运行，允许我们给它设置不同的运行模式（sql_mode）。
不同的运行模式，mysql处理我们的sql语句也不相同（mysql提供了18个运行模式，它们可任意组合使用）。

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

1. 通过设置sql mode, 可以完成不同严格程度的数据校验，有效地保障数据准备性。
2. 通过设置sql model 为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据库之间进行迁移时，则不需要对业务sql
   进行较大的修改。
3. 在不同数据库之间进行数据迁移之前，通过设置SQL Mode 可以使MySQL 上的数据更方便地迁移到目标数据库中。

下面对18种运行模式进行简单介绍

1. STRICT_ALL_TABLES

控制是否所有的mysql存储引擎均开启严格模式，默认为OFF，建议ON；
严格模式开启后，mysql会对更改数据的行为做严格的校验，比如：日期的有效性等，一般和其他模式配合使用；

2. STRICT_TRANS_TABLES 严格模式

这个也是严格模式，不同的是上面的会对所有存储引擎开启严格模式，而这个会对所有支持事务引擎开启，并尽可能对非事务引擎开启。
默认为ON，建议ON；

3. ALLOW_INVALID_DATES

控制我们能否向date、datetime列插入2020-04-31这种数据（4月怎么会有31号呢？这是个非法数据）。
当OFF时，不能插入；
当ON时，能插入；
默认为OFF，建议OFF；

4. ANSI_QUOTES

是否将双引号也当做对象名称的标识，就像` 一样。
当为OFF时，" 用来包裹字符串，不能表示表名和列名等；
当为ON时，"不能包裹字符串，但它可以包裹表名和列名等；

默认为OFF，建议OFF；在mysql中我们可以仅可以用`包裹对象标识名（当然也可以不包裹），单引号包裹字符串。

5. HIGH_NOT_PRECEDENCE

控制 NOT运算符是否具有更高的优先级。
当OFF时，select 1 between -5 and 5等同select not (1 between -5 and 5)；
当ON时，select 1 between -5 and 5等同select (not 1) between -5 and 5；
默认为OFF，建议OFF；

6. IGNORE_SPACE

控制是否忽略函数名和(之间的空格。
当OFF时，函数名和(之间不能有空格；
当ON时，函数名和(之间可以有空格；
默认OFF，建议OFF；

7. NO_AUTO_VALUE_ON_ZERO

控制当把0插入到自增列时是否触发自增。
当为OFF时，将0或null插入到自增列都会触发自增；
当为ON时，将0插入到自增列不会触发自增，因为0会被当做正常数据对待，直接插入进去（插入null依然会触发自增）；
默认为OFF，建议OFF；

8. NO_BACKSLASH_ESCAPES

控制是否将反斜杠识别为转义字符。
当为OFF时，反斜杠是转义字符；
当为ON时，反斜杠就是普通字符，不会对其后的字符转义，此时mysql中也就没有了转移字符；
默认为OFF，建议OFF；

9. NO_DIR_IN_CREATE

控制是否忽略create table时后面的 DATA DIRECTORY 和 INDEX DIRECTORY 声明。

DATA DIRECTORY 和 INDEX DIRECTORY 声明是当mysql的datedir存储空间不够时允许用户为新建的表指定新的存储地址，如：
CREATE TABLE t_1 engine=innodb DATA DIRECTORY=“/data/lottery/” INDEX DIRECTORY=“/data/lottery/”

当为OFF时，DATA DIRECTORY 和 INDEX DIRECTORY 继续有效；
当为ON时，DATA DIRECTORY 和 INDEX DIRECTORY 会被忽略；

默认为OFF，建议OFF；

10. NO_ENGINE_SUBSTITUTION

控制是否自动将用户建表时声明的不可用的存储引擎替换成可用的存储引擎。
比如：当我们建表时声明使用某一个存储引擎，但是由于服务器问题，这个引擎暂时不可用，那么此时mysql应该怎么做呢？

当为OFF时，mysql会使用可用的默认殷勤替代；
当为ON时，mysql会直接报错；

默认ON，建议ON；

11. NO_UNSIGNED_SUBTRACTION

控制当我们对unsigned int 等做减法时，是否将结果转为对应的非unsigned修饰的类型。
当为OFF时，unsigned int与int相减结果仍为unsigned int；
当为ON时，unsigned int与int相减结果为int；
默认OFF，建议OFF；

12. NO_ZERO_DATE

控制是否判别 ‘0000-01-01’(年份为0)为合法的日期，配合StrictMode作用。
当为OFF时， '0000-01-01’是合法日期；
当为ON时， '0000-01-01’不是合法日期；
默认为OFF，建议ON；

13. NO_ZERO_IN_DATE

和NO_ZERO_DATE相似，不同的是前者控制的是年份，后者控制的月份和天（如：2022-13-32）。

默认为OFF，建议ON；

建议设置为：set sql_mode=“STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE”

14. ONLY_FULL_GROUP_BY

控制mysql中 select … group by 语句的写法。

原因是这样的，在sql92标准的时候，规定如果使用了group by子句，那么select子句的列一定被包含在group by 子句中，或者使用sum等聚合函数包裹。
然而，到了sql99的时候，规定select 子句的列也可以不被包含在group by 子句中，如：select t_id,t_name,t_age from test group by t_id是可以的，因为 t_id 是主键。

15. PAD_CHAR_TO_FULL_LENGTH

控制读取char(M)类型的列值时，当实际值的长度不到M时，是否在默认添加空格以补齐M的长度。
例如，我们向char(10)中插入字符串’abc ‘（abc+3个空格），那么我们读取时：
当为OFF时，读取到的是’abc’，后面没有空格，因为mysql会自动去掉末尾的空格。
当为ON时，读取到的是’abc '（abc+7个空格），mysql会自动补齐到长度10;
默认OFF，建议OFF。另外，这是一个即将被弃用的模式。

16. PIPES_AS_CONCAT

控制是否将 ‘||’ 作为 string之间的链接符号，就像 concat(string1,string2) 函数一样。
当为OFF时，|| 是 or 的同义词，不过mysql建议使用 or 而不是 || ；
当为ON时，|| 和concat函数类似；
默认OFF，建议OFF；

17. REAL_AS_FLOAT

控制是否将 real 当做float的同义词，
当为OFF时，real是double的同义词；
当为ON时，real是float的同义词；
默认OFF，建议OFF；

18. TIME_TRUNCATE_FRACTIONAL

控制当时间的毫秒精度超出时，是进行四设五入，还是直接舍弃多余的位数。
举个例子，当将’14:52:12.15’插入到time(1)的时候，是插入’14:52:12.2’还是插入’14:52:12.1’
当为OFF时，进行四设五入，插入14:52:12.2；
当为ON时，舍弃多余的位数，插入14:52:12.1；
默认为OFF，建议OFF。