MySQL学习笔记之sql_mode
一、什么是服务器SQL模式：
MySQL服务器可以在不同的SQL模式下运行，并且可以根据sql_mode系统变量的值将这些模式应用于不同的客户端，
DBA可以设置全局SQL模式以匹配站点服务器操作要求，并且每个应用程序可以根据自己的要求设置session SQL模式。
SQL模式会影响MySQL支持的SQL语法以及它执行的数据验证检查，这使得在不同环境中使用MySQL以及将MySQL与其他数据库服务一起使用更加容易。
在使用InnoDB表时，还要考虑innodb_strict_mode系统变量，它启用了对InnoDB表的其他错误检查。
二、SQL模式查看和设置：
查看当前SQL模式：
select @@global.sql_mode;
select @@session.sql_mode;
select @@sql_mode; #等同于select @@session.sql_mode;
设置SQL模式：
set global sql_mode = "modes";
set session sql_mode = "modes";
永久保存设置SQL模式：
在MySQL配置文件（Linux：my.cnf，Windows：my.ini）中的[mysqld]最后一行添加：sql_mode="modes"，重启MySQL服务
三、SQL模式说明：
MySQL8.0默认的SQL模式有：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
| SQL模式 | 说明 |
|---|---|
| ALLOW_INVALID_DATES | 不完全检查日期，只检查月份是否在112之间，日期是否在131之间。此模式适用于date和datetime列，不适用于需要验证有效日期的列（timestamp）。禁用此模式后，服务器要求月和日的值是合法的，而不仅仅是112和131。非严格模式下，无效日期（如"2020-04-30"）将转换为"0000-00-00"，并生成警告。严格模式下，无效日期将报错，若要允许这样的无效日期，需要启用此模式 |
| ANSI_QUOTES | 该模式下将"看成了标识引号符（类似于 符号），而不是字符串引号符。启用此模式后，仍可使用符号作为标识引号符，但是不能使用双引号来引用文本字符串了，因为双引号被解释成标识引号符。 |
| ERROR_FOR_DIVISION_BY_ZERO | 严格模式下，在insert或update操作过程中，如果除以零(包括使用函数mod(M,0))将会报除以零错误，非严格模式下则会产生警告。如果未启用该模式除以零将插入null，并不会产生警告，启用该模式除以零将插入null，并产生警告。如果同时启用了该模式和严格模式，除零将会产生错误，若使用了insert ignore和update ignore将插入null并产生警告。对于select操作，除零将返回null并产生警告（无论是否启用严格模式）。不推荐使用该模式，该模式不是严格模式的一部分，但应与严格模式结合使用，默认是启用该模式的。如果在未启用严格模式是启用了该模式将会发出警告。 |
| high_not_precedence | 在启用该模式时，表达式 select not a between b and c 被解析成 select (not a) between b and c，未启用该模式时被解析成 select not (a between b and c) |
| ignore_space | 该模式允许函数名和()之间有空格，将导致内置函数名被视为保留字。例如，MySQL中有count()函数，在创建数据表count时：create table count (id int);将会报错：ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count (i int)' at line 1。这时表名必须使用``引起来：create table count (i int);该模式适用于内置函数，而不适用于用户自定义函数。 |
| no_auto_value_on_zero | 在该模式下，auto_incremnet列只能通过插入null来生成下一个序列号，未启用该模式可以通过插入null或0来生成下一个序列号。如果表中auto_increment列已经有0了，该模式将非常有用（不建议存储0）。例如，如果您使用mysqldump转储表，然后重新加载它，那么当遇到0值时，MySQL通常会生成新的序列号，从而导致表的内容与转储的表不同。在重新加载转储文件之前，该模式可以解决此问题。因此，mysqldump会在其输出中自动包含一个启用该模式的语句。 |
| no_backslash_escapes | 启用该模式，反斜杠()将被视为普通字符，而非转义字符 |
| no_dir_in_create | 创建表时，忽略所有索引目录和数据目录指令。此选项在副本服务器上很有用。 |
| no_engine_subtitution | 当create table 或 alter table 等语句指定的存储引擎已禁用或未编译时，将自动替换默认存储引擎。默认该模式是启用的。启用该模式时，如果所需的存储引擎不可用，则会发生错误，并且不会创建或更新表。禁用该模式时，create table时会使用默认存储引擎，若默认存储引擎不可用，则会报警告，alter table时会报警告，并且不会更改表。 |
| no_unsigned_subtraction | 禁用此模式时，整数之间的进行减法运算时（其中一个为无符号类型），默认生成的结果是无符号类型的。如果结果为负数，将会报错。如果此类操作的结果用于更新无符号整数列，则结果将被剪裁为列类型的最大值，若启用了该模式，则将结果裁剪成0，如果启用了严格模式，将报错，列不会发生改变，进行减法运算时，结果仅为signed（无论减号两边是有符号类型的还是unsigned或者signed）。 |
| no_zero_date | 未启用该模式时，'0000-00-00'则允许并且插入不会产生警告。如果启用此模式，'0000-00-00' 则允许并插入将产生警告。若果启用了严格模式和该模式，插入'0000-00-00'将会报错，若此时使用了insert ignore和update ignore '0000-00-00'将会被插入，但会产生警告。该模式不推荐使用，它不是严格模式的一部分，但应与严格模式结合使用，并且默认情况下处于启用状态。如果该模式在未启用严格模式的情况下启用了，将会产生警告。 |
| no_zero_in_date | 在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。 |
| only_full_group_by | 在此模式下，如果select列表中出现了group by未使用到的字段（使用聚合函数除外），将会报错。 |
| pad_char_to_full_length | 启用该模式时，检索char类型的字符时将不会自动去掉字符后面的空格。禁用该模式时，将自动去掉char类型字符后面的空格。该模式对varchar类型的字符不起作用。 |
| pipes_as_concat | 将 |
| peal_as_float | 默认情况下，MySQL将peal视为double的同义词，启用该模式时，将peal视为float。 |
| strict_all_tables | 为所有存储引擎启用严格模式，拒绝无效数据。 |
| strict_trans_tables | 为事务存储引擎启用严格的SQL模式，并在可能的情况下为非Transaction存储引擎启用严格SQL模式。 |
| time_truncate_fractional | 当插入带小数的time，date或timestamp类型的值时，MySQL默认是四舍五入的，启用该模式时，直接截取要保留的小数位。 |
| ansi | 相当于peal_as_float, pipes_as_concat,ansi_quotes, ignore_space,和 onli_full_group_by。 |
| traditional | 相当于STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, 和 NO_ENGINE_SUBSTITUTION |
| 四、测试sql_mode对SQL语句执行的影响 |
1. allow_invalid_dates:
mysql> create table t1(c1 date,c2 timestamp,c3 datetime);
Query OK, 0 rows affected (0.03 sec)
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1(c1,c2,c3) values('2020-04-31','2020-04-31','2020-04-31');
Query OK, 1 row affected, 3 warnings (0.00 sec)
mysql> select * from t1;
+------------+---------------------+---------------------+
| c1 | c2 | c3 |
+------------+---------------------+---------------------+
| 0000-00-00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t1(c1,c2,c3) values('2020-04-31','2020-04-31','2020-04-31');
ERROR 1292 (22007): Incorrect date value: '2020-04-31' for column 'c1' at row 1
mysql> set sql_mode='allow_invalid_dates';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1(c1,c2,c3) values('2020-04-31','2020-04-31','2020-04-31');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t1;
+------------+---------------------+---------------------+
| c1 | c2 | c3 |
+------------+---------------------+---------------------+
| 0000-00-00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 2020-04-31 | 0000-00-00 00:00:00 | 2020-04-31 00:00:00 |
+------------+---------------------+---------------------+
2 rows in set (0.00 sec)
2. ansi_quotes：
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select "a";
+---+
| a |
+---+
| a |
+---+
1 row in set (0.00 sec)
mysql> set sql_mode='ansi_quotes';
Query OK, 0 rows affected (0.00 sec)
mysql> select "a";
ERROR 1054 (42S22): Unknown column 'a' in 'field list'
3. error_for_division_zero：
mysql> create table t2(c1 int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t2 values(7/0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+------+
| c1 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> set sql_mode='error_for_division_by_zero';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t2 values(7/0);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t2;
+------+
| c1 |
+------+
| NULL |
| NULL |
+------+
2 rows in set (0.00 sec)
mysql> select 7/0;
+------+
| 7/0 |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)
mysql> set sql_mode='strict_all_tables,error_for_division_by_zero';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select 7/0;
+------+
| 7/0 |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)
mysql> insert into t2 values(7/0);
ERROR 1365 (22012): Division by 0
mysql> insert ignore t2 values(7/0);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t2;
+------+
| c1 |
+------+
| NULL |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)
4. high_not_precedence：
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select not 1 between -5 and 5;
+------------------------+
| not 1 between -5 and 5 |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)
mysql> set sql_mode='high_not_precedence';
Query OK, 0 rows affected (0.00 sec)
mysql> select not 1 between -5 and 5;
+------------------------+
| not 1 between -5 and 5 |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)
5. ignore_space：
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> create table count (id int);
Query OK, 0 rows affected (0.02 sec)
mysql> set sql_mode='ignore_space';
Query OK, 0 rows affected (0.00 sec)
mysql> create table max (id int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'max (id int)' at line 1
mysql> create table `max` (id int);
Query OK, 0 rows affected (0.03 sec)
6. no_auto_value_on_zero：
mysql> create table t3(id int primary key auto_increment,name varchar(15));
Query OK, 0 rows affected (0.03 sec)
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t3(id,name) values(null,'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
+----+------+
1 row in set (0.00 sec)
mysql> insert into t3(id,name) values(null,'aaa');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | aaa |
+----+------+
2 rows in set (0.00 sec)
mysql> insert into t3(id,name) values(0,'aaa');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | aaa |
| 3 | aaa |
+----+------+
3 rows in set (0.00 sec)
mysql> insert into t3(id,name) values(0,'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | aaa |
| 3 | aaa |
| 4 | aaa |
+----+------+
4 rows in set (0.00 sec)
mysql> set sql_mode='no_auto_value_on_zero';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t3(id,name) values(null,'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | aaa |
| 3 | aaa |
| 4 | aaa |
| 5 | aaa |
+----+------+
5 rows in set (0.00 sec)
mysql> insert into t3(id,name) values(0,'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 0 | aaa |
| 1 | aaa |
| 2 | aaa |
| 3 | aaa |
| 4 | aaa |
| 5 | aaa |
+----+------+
6 rows in set (0.00 sec)
mysql> insert into t3(id,name) values(0,'aaa');
ERROR 1062 (23000): Duplicate entry '0' for key 't3.PRIMARY'
7. no_backslash_escapes：
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select '\a';
+---+
| a |
+---+
| a |
+---+
1 row in set (0.00 sec)
mysql> set sql_mode='no_backslash_escapes';
Query OK, 0 rows affected (0.00 sec)
mysql> select '\a';
+----+
| \a |
+----+
| \a |
+----+
1 row in set (0.00 sec)
8. no_zero_date：
mysql> create table t6(c1 date);
Query OK, 0 rows affected (0.03 sec)
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t6 values('0000-00-00');
Query OK, 1 row affected (0.01 sec)
mysql> set sql_mode='no_zero_date';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t6 values('0000-00-00');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> set sql_mode='strict_all_tables,no_zero_date';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t6 values('0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'c1' at row 1
mysql> insert ignore into t6 values('0000-00-00');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t6;
+------------+
| c1 |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
+------------+
3 rows in set (0.00 sec)
9. no_zero_in_date：
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t6 values('2020-00-00');
Query OK, 1 row affected (0.00 sec)
mysql> set sql_mode='no_zero_in_date';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t6 values('2020-00-00');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> set sql_mode='strict_all_tables,no_zero_in_date';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t6 values('2020-00-00');
ERROR 1292 (22007): Incorrect date value: '2020-00-00' for column 'c1' at row 1
mysql> select * from t6;
+------------+
| c1 |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
| 2020-00-00 |
| 0000-00-00 |
+------------+
5 rows in set (0.00 sec)
10. only_full_group_by：
mysql> select * from t1 group by c1;
+------------+---------------------+---------------------+
| c1 | c2 | c3 |
+------------+---------------------+---------------------+
| 0000-00-00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+------------+---------------------+---------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> set sql_mode='only_full_group_by';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 group by c1;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'testdb1.t1.c2' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select c1 from t1 group by c1;
+------------+
| c1 |
+------------+
| 0000-00-00 |
+------------+
1 row in set, 1 warning (0.00 sec)
mysql> select count(*) from t1 group by c1;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set, 1 warning (0.00 sec)
11. pad_char_to_full_length：
mysql> create table t7(c1 char(10),c2 varchar(10));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t7(c1,c2) values('a ','b ');
Query OK, 1 row affected (0.00 sec)
mysql> select length(c1),c1,length(c2),c2 from t7;
+------------+------+------------+---------+
| length(c1) | c1 | length(c2) | c2 |
+------------+------+------------+---------+
| 1 | a | 7 | b |
+------------+------+------------+---------+
1 row in set (0.00 sec)
mysql> set sql_mode='pad_char_to_full_length';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select length(c1),c1,length(c2),c2 from t7;
+------------+------------+------------+---------+
| length(c1) | c1 | length(c2) | c2 |
+------------+------------+------------+---------+
| 10 | a | 7 | b |
+------------+------------+------------+---------+
1 row in set (0.00 sec)
mysql> insert into t7(c1,c2) values('c','d');
Query OK, 1 row affected (0.00 sec)
mysql> select length(c1),c1,length(c2),c2 from t7;
+------------+------------+------------+---------+
| length(c1) | c1 | length(c2) | c2 |
+------------+------------+------------+---------+
| 10 | a | 7 | b |
| 10 | c | 1 | d |
+------------+------------+------------+---------+
2 rows in set (0.00 sec)
12. pipes_as_concat：
mysql> set sql_mode='pipes_as_concat';
mysql> select 1 || 2;
+--------+
| 1 || 2 |
+--------+
| 12 |
+--------+
1 row in set (0.00 sec)
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select 1 || 2;
+--------+
| 1 || 2 |
+--------+
| 1 |
+--------+
1 row in set, 1 warning (0.00 sec)
13. time_truncate_fractional：
MySQL8+好像不支持
mysql> set sql_model='TIME_TRUNCATE_FRACTIONAL';
ERROR 1193 (HY000): Unknown system variable 'sql_model'
14. ansi：
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> set sql_mode='ansi';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+--------------------------------------------------------------------------------+
| @@sql_mode |
+--------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
15. fractional:
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> set sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+----------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
