Mysql中sql_mode使用详解

1 sql_mode简介

sql_mode是一组mysql的语法校验规则，定义了mysql应该支持的sql语法、数据校验等。

1.1 查看当前的sql_mode

查看当前的sql_mode,三种方式:
(1)select @@sql_mode;
(2)select @@SESSION.sql_mode;
(3)select @@GLOBAL.sql_mode;
输出如下:
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_ENGINE_SUBSTITUTION

1.2 如何设置sql_mode

方式一:通过命令设置
SET sql_mode = '';
SET SESSION sql_mode = '';
SET GLOBAL sql_mode = '';

方式二:修改mysql的配置文件my.cnf
修改后需要重启mysql服务使其生效
[mysqld]
sql_mode = ANSI

1.3 sql_mode模式

1.3.1 ANSI模式

这是一种宽松模式，该模式下，会对所操作数据进行校验，如果不符合校验规则，数据会按照规则执行，并报warning警告。

该模式下，sql_mode参数为:
REAL_AS_FLOAT,
PIPES_AS_CONCAT,
ANSI_QUOTES,
IGNORE_SPACE,
ANSI

1.3.2 STRICT_TRANS_TABLES模式

这是一种严格模式，该模式下，会对所操作数据进行严格校验，如果不符合校验规则，报error错误。

该模式下，sql_mode参数为:
STRICT_TRANS_TABLES

1.3.3 TRADITIONAL模式

这是一种严格模式，该模式下，会对所操作数据进行严格校验，如果不符合校验规则，报error错误。用于事务时，会进行事物的回滚，保证了错误数据无法插入到数据库中。

该模式下，sql_mode参数为:
STRICT_TRANS_TABLES,
STRICT_ALL_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
TRADITIONAL,
NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION

1.4 常用sql_mode各参数详解

1、PIPES_AS_CONCAT
将"||"作为字符串的链接字符操作，而不是作为或运算符，
与"concat"相类似。

2、ANSI_QUOTES
不能用双引号来引用字符串，因为它会被解释为识别符。

3、IGNORE_SPACE
用于忽略mysql系统函数与之后的括号之间的空格。

4、STRICT_TRANS_TABLES和STRICT_ALL_TABLES
严格模式的一种，会对所操作数据进行严格校验。
如果不符合校验规则，报error错误。

5、NO_ZERO_IN_DATE
在严格模式下，不允许日期和月份为零。

6、NO_ZERO_DATE
在严格模式下，不允许插入0日期。

7、ERROR_FOR_DIVISION_BY_ZERO
在严格模式下，不允许在字段中插入除数为0的运算值，会直接报错。
直接计算除数为0的运算，返回null，报warning。

8、ONLY_FULL_GROUP_BY
select后面的字段中，只能出现分组列和聚合列。

9、其他
NO_AUTO_CREATE_USER（用户）
ALLOW_INVALID_DATES（日期）
HIGH_NOT_PRECEDENCE（not优先级）
NO_AUTO_VALUE_ON_ZERO（主键）
NO_BACKSLASH_ESCAPES（反斜杠）
NO_DIR_IN_CREATE（数据目录）
NO_ENGINE_SUBSTITUTION（存储引擎）
NO_UNSIGNED_SUBTRACTION（无符号数相减计算）
PAD_CHAR_TO_FULL_LENGTH（char字段存储）
TIME_TRUNCATE_FRACTIONAL（时间类型超出范围处理）

1.5 不同版本默认的sql_mode

MySQL 5.5:空

MySQL 5.6:NO_ENGINE_SUBSTITUTION【存储引擎】

MySQL 5.7:
ONLY_FULL_GROUP_BY,【select后面的字段中,只能出现分组列和聚合列】 
STRICT_TRANS_TABLES,【严格模式的一种,会对所操作数据进行严格校验】
NO_ZERO_IN_DATE, 【在严格模式下,不允许日期和月份为零】
NO_ZERO_DATE, 【在严格模式下,不允许插入0日期】
ERROR_FOR_DIVISION_BY_ZERO, 【在严格模式下，不允许在字段中插入除数为0的运算值，会直接报错】
NO_AUTO_CREATE_USER, 【用户】 
NO_ENGINE_SUBSTITUTION【存储引擎】

MySQL 8.0:
ONLY_FULL_GROUP_BY, 【select后面的字段中,只能出现分组列和聚合列】 
STRICT_TRANS_TABLES, 【严格模式的一种,会对所操作数据进行严格校验】
NO_ZERO_IN_DATE, 【在严格模式下,不允许日期和月份为零】
NO_ZERO_DATE, 【在严格模式下,不允许插入0日期】
ERROR_FOR_DIVISION_BY_ZERO, 【在严格模式下，不允许在字段中插入除数为0的运算值，会直接报错】
NO_ENGINE_SUBSTITUTION【存储引擎】

2 实际应用

1.查询当前 mysql 数据库 的 SQL_MODE
select @@SQL_MODE

如果包含 ANSI_QUOTES

2.修改my.cnf
SQL_MODE=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

保存配置文件，之后重启mysql 服务

3.然后再次验证
select @@SQL_MODE

SQL验证
select REPLACE(uuid(),“-”,“”) from DUAL