sql mode的作用是影响语法以及数据的有效性。

5.7默认的sql_mode有 ONLY_FULL_GROUP_BY(5.7.5新加)，STRICT_TRANS_TABLES(5.7.5新加)，NO_ZERO_IN_DATE（5.7.8新加）， NO_ZERO_DATE（5.7.8新加）， ERROR_FOR_DIVISION_BY_ZERO（5.7.8新加），NO_AUTO_CREATE_USER（5.7.7新加）NO_ENGINE_SUBSTITUTION

可以在配置文件中加sql_mode=”….”或者在启动是使用—sql_mode选项来使用sql_mode。sql_mode既是全局变量也是会话变量，会话级的sql_mode只能影响当前客户端。可以在运行时修改该变量：

  1. SET GLOBAL sql_mode = 'modes';
  2. SET SESSION sql_mode = 'modes';

sql_mode列表

1/ALLOW_INVALID_DATES
这个模式下不会检查所有的日期项，只会检查月份是否在1-12，日是否在1-31之间。这个模式会对DATE以及DATETIME类型的字段生效，对TIMESTAMP无效。

mysql> select @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| ALLOW_INVALID_DATES |
+---------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE T3(col1 date);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into T3 values('2021-07-32');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from T3;
+------------+
| col1       |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

当启用这个模式时，可以插入错误的日期’2021-07-32’，产生一个警告，实际上插入的值是’0000-00-00’。

2/ANSI_QUOTES
将双引号而不是单引号视为标识符引号字符，这个模式下，不能使用双引号去引用字符串，因为他们被解释成标识符。

3/ERROR_FOR_DIVISION_BY_ZERO这个模式已经废弃了。

对于insert以及update语句：
如果设置了这个模式，除以0将会插入NULL并且返回警告
如果没有设置这个模式，除以0将会插入NULL，但不会返回警告
如果这个模式以及严格模式都开启了，除以0将会报错。但如果IGNORE模式开启了，会插入NULL，并返回警告
对于select语句：
如果开启这个模式，将返回NULL，并返回一个警告，不管严格模式是否开启。

4/HIGH_NOT_PRECEDENCE
关于not的优先级。not a between b and c这个语句被解析成not （a between b and c）在一些老版本中这个语句被解析成（NOT a ）between b and c 

mysql> SET sql_mode = '';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
        -> 0
mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
        -> 1

5/IGNORE_SPACE
允许在函数名与(之间有空格。

6/NO_AUTO_CREATE_USER
这个模式下，使用grant语句授权时，除非指定非空密码(identified by )，否则不允许自动创建用户。

7/NO_ATUO_VALUE_ZERO
通常情况下，通过在列中插入NULL或者0来为列生成下一个序列号。如果指定了这个模式，只有NULL会生成下一个序列号。这个模式通常在自增列可以为0时使用。

8/NO_BACKSLASH_ESCAPE
启用这个模式时，\将不会作为逃逸字符。

9/NO_DIR_IN_CREATE

10/NO_ENGINE_SUBSTITUTION
当使用CREATE TABLE或者ALTER TABLE语句指定存储引擎时，如果这个存储引擎被禁用了，或者没有编译，如果这个模式开启了，那么会使用默认的存储引擎代替。

11/NO_FILED_OPTIONS
当使用SHOW CREATE TABLE语句时，不会显示指定的列的信息。这个模式在mysqldump的简洁模式中使用，在5.7.22废弃了。
12/NO_KEY_OPTIONS
当使用SHOW CREATE TABLE语句时，不会显示指定的索引的信息。这个模式在mysqldump的简洁模式中使用，在5.7.22废弃了。
13/NO_TABLE_OPTIONS
当使用SHOW CREATE TABLE语句时，不会显示指定的表(例如引擎)的信息。这个模式在mysqldump的简洁模式中使用，在5.7.22废弃了。

14/NO_UNSIGNED_SUBTRACTION
对于两个整数的无符号的减法，如果产生了负值，则会报错。如果设置了该模式，那么结果会是负值。

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+

15/NO_ZERO_DATE
这个模式的作用是判断’0000-00-00’是否是一个有效日期。
如果这个模式没有启用，’0000-00-00’是可以插入且没有警告的
如果这个模式启用了，’0000-00-00’可以插入但是有警告
如果这个模式跟严格模式都开启了，’0000-00-00’是不允许的，除非IGNORE也给出了，否则插入就会报错。
16/NO_ZERO_IN_DATE
这个模式的作用是判断年份是非零的，月份或者日期有零的情况下是否是有效日期。例如：2021-00-01

17/ONLY_FULL_GROUP_BY
当有group by时，不允许非聚合列出现在having子句，order by子句中

18/PAD_CHAR_TO_FULL_LENGTH
当这个模式启用时，检索char的字段时，不会修剪填充的空格。

mysql> CREATE TABLE t1 (c1 CHAR(10));
Query OK, 0 rows affected (0.37 sec)

mysql> INSERT INTO t1 (c1) VALUES('xy');
Query OK, 1 row affected (0.01 sec)

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------+-----------------+
| c1   | CHAR_LENGTH(c1) |
+------+-----------------+
| xy   |               2 |
+------+-----------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------------+-----------------+
| c1         | CHAR_LENGTH(c1) |
+------------+-----------------+
| xy         |              10 |
+------------+-----------------+
1 row in set (0.00 sec)

19/PIPES_AS_CONCAT
把||当作拼接符号，作用等同于concat()函数。

20/REAL_AS_FLOAT
把real当作float的同义词，默认情况下，real是double的同义词

21/STRICT_ALL_TABLES
对于所有的存储引擎都开启严格模式。

22/STRICT_TRANS_TABLES
对事务性存储引擎开启严格模式。

Strict sql mode

mysql对于INSERT跟UPDATE使用严格模式来控制无效的值。但对于select来说，严格模式中出现非法值是不会报错的，但是会给出警告。如果启用了STRICT_ALL_TABLES或者STRICT_TRANS_TABLES，那么严格模式就启用了。
对于事务型的表，如果出现了违反sql_mode的语句， 将会报错且回退。
对于非事务型的表，如果插入或者更改时第一行就违反了sql_mode，那么跟事务型的表同样报错，且不修改。如果是第二行以及以后的行违反了sql_mode，那么根据sql_mode的类型来处理：
如果是STRICT_ALL_TABLES，第一行会改变，剩下的不会改变，且报错。
如果是STRICT_TRANS_TABLES，将会把无效值转换成一个最接近的有效值并插入。

严格模式对于除数为0的处理：
对于insert或者update：当严格模式没有打开时，除数为0会插入一个null，并不会报错。如果打开了严格模式，会报错，如果ignore insert或者ignore update打开了，会插入一个null。
对于select 除数为0会返回null，与警告。

严格模式对于日期’0000-00-00’/‘2021-00-01’的处理：
如果严格模式没有打开，这个值允许插入。
如果严格模式打开了，插入会报错。如果INSERT IGNORE或者UPDATE IGNORE，允许插入这个值。

关于IGNORE与STRICT sql mode

ignore 是将erorrs降级为warnings，而Strict sql mode是将warnings 升级为errors。