MySQL 5.7 模式(SQL_MODE)详细说明

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,
 
5.7.4 废弃：ERROR_FOR_DIVISION_BY_ZERO
5.7.5 默认：ONLY_FULL_GROUP_BY , STRICT_TRANS_TABLES
5.7.7 默认：NO_AUTO_CREATE_USER
5.7.8 默认：ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE
 
 
命令行启动服务设置： --sql-mode="modes"
配置文件中的设置： sql-mode="modes" 
 
对于多个的模式用逗号隔开。清除模式则设置为空字符：
--sql-mode="" 
sql-mode="" 
 
在服务运行时改变模式，有全局和会话级别：
SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';
 
全局模式在线设置需要超级权限(SUPER)，新的连接才会生效；会话级别模式每个客户端都可设置。
 
查看当前模式：
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
SELECT @@sql_mode; 
 
在未了解各种模式的影响下，表分区后建议不要再变更模式，同步复制的实例也建议模式保持一致。

======================================================================
【ANSI】
等价于 REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE. ONLY_FULL_GROUP_BY(MySQL 5.7.5)
======================================================================
REAL_AS_FLOAT ：REAL 为 FLOAT 的同义词(默认情况, REAL 为 DOUBLE 的同义词).
PIPES_AS_CONCAT ： 管道符(||) 作为连接符.(默认使用函数 CONCAT 连接字符)
ANSI_QUOTES ：标准引号, 双引号不作为字符串引号，作为关键字标识符引号
IGNORE_SPACE ：对于内置函数与其他字符间的空格，忽略空格
 
------------------------------
【ANSI_QUOTES】
------------------------------
create table max(id int); #报错 (相同)
create table max (id int); #不报错 (相同)
create table `max`(id int); #不报错 (相同)
create table 'max'(id int); #报错 (相同)
 
create table "max"(id int);  #报错 sql_mode='';
create table "max"(id int);  #不报错 sql_mode='ANSI_QUOTES';
 
SELECT "AAA"; #不报错 sql_mode='';
SELECT "AAA"; #报错  sql_mode='ANSI_QUOTES';
------------------------------
【IGNORE_SPACE】
------------------------------
create table max(id int); #报错 (相同)
 
create table max (id int);  #不报错 sql_mode='';
create table max (id int);  #报错 sql_mode='IGNORE_SPACE';
 
------------------------------
【PIPES_AS_CONCAT】
------------------------------
SET sql_mode='PIPES_AS_CONCAT';
 
select 'aa' || 'bb';
select CONCAT('aa' , 'bb');
 
======================================================================
【STRICT_TRANS_TABLES】
对事务型表操作，插入表时如果第一行数据不符合约束则终止执行并回滚。
======================================================================
create table test(value int(1));
SET sql_mode=''; #默认只要第一个值
 
insert into test(value) values('a'),(1),(2147483647); #不报错
insert into test(value) values(1),('a'),(2147483647); #不报错
select * from test;
+------------+
| value      |
+------------+
|          1 |
|          0 |
| 2147483647 |
+------------+
 
#后面删除表不再说明！
drop table test; 
create table test(value int(1));
 
SET sql_mode='STRICT_TRANS_TABLES'; #每个值都判断
 
insert into test(value) values('a'),(1),(2147483647); #报错，第一行'a'错误。
insert into test(value) values(1),('a'),(2147483647); #报错，第二行'a'错误。
 
======================================================================
【TRADITIONAL】
======================================================================
MySQL 5.7.4 以前版本 和 MySQL 5.7.8 及以上版本：
STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_AUTO_CREATE_USER
, NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE, NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO.
 
MySQL 5.7.4 至 5.7.7 版本：
STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION. 
(STRICT_ALL_TABLES / STRICT_TRANS_TABLES 包含 NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO)
 
------------------------------
【STRICT_ALL_TABLES】
包括： ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE
------------------------------
 
------------------------------
【NO_AUTO_CREATE_USER】禁止使用 GRANT 创建密码为空的用户。
------------------------------
标准写法：
create user user01@'localhost' identified by 'user01'; 
grant all on test.* to user01@'localhost';  
flush privileges;  
 
 
SET sql_mode='';
 
#不报错（无需要设置密码）
grant all on test.* to user01@'localhost';  
 
 
SET sql_mode='NO_AUTO_CREATE_USER';
 
#报错 ERROR 1133 (42000): Can't find any matching row in the user table
grant all on test.* to user02@'localhost';  
 
#正确，需要设置密码
grant all on test.* to user02@'localhost' identified by 'user02'; 
 
------------------------------
【NO_ENGINE_SUBSTITUTION】
默认情况创建或修改表的存储引擎不支持时，自动转为默认的INNODB;
使用该模式后，存储引擎不支持时则报错。
------------------------------
#查看所有存储引擎
show engines;
 
SET sql_mode='';
create table test(id int) ENGINE=FEDERATED;
select table_name,engine from information_schema.tables where table_schema='test' and table_name='test';
+------------+--------+
| table_name | engine |
+------------+--------+
| test       | InnoDB |
+------------+--------+
 
SET sql_mode='NO_ENGINE_SUBSTITUTION';
create table test(id int) ENGINE=FEDERATED;
直接报错：ERROR 1286 (42000): Unknown storage engine 'FEDERATED'
 
------------------------------
【NO_ZERO_IN_DATE】日期格式(月日)是否支持'00'.
------------------------------
create table test(value date);
SET sql_mode='';
insert into test(value) values('2018-00-00'); #结果为 '2018-00-00'
 
SET sql_mode='NO_ZERO_IN_DATE';
insert into test(value) values('2017-00-00'); #不符合,转为 '0000-00-00'
 
 
------------------------------
【NO_ZERO_DATE】 不允许插入 '0000-00-00' 日期
------------------------------
create table test(value date);
 
SET sql_mode='';
insert into test(value) values('0000-00-00'); #无警告 warning
 
SET sql_mode='STRICT_TRANS_TABLES';
insert into test(value) values('0000-00-00'); #无警告 warning
 
SET sql_mode='NO_ZERO_DATE';
insert into test(value) values('0000-00-00'); #有警告 warning
 
SET sql_mode='NO_ZERO_DATE,STRICT_TRANS_TABLES';
insert into test(value) values('0000-00-00'); #报错终止
 
------------------------------
【ERROR_FOR_DIVISION_BY_ZERO】 0为除数时报错
------------------------------
create table test(value int);
 
SET sql_mode='';  
select 10/0;  #无警告 warning
insert into test(value) values(10/0);   #无警告 warning
 
SET sql_mode='STRICT_TRANS_TABLES'; 
select 10/0;   #无警告 warning
insert into test(value) values(10/0);  #无警告 warning
 
SET sql_mode='ERROR_FOR_DIVISION_BY_ZERO'; 
select 10/0;  #有警告 warning
insert into test(value) values(10/0);  #有警告 warning
 
SET sql_mode='ERROR_FOR_DIVISION_BY_ZERO,STRICT_TRANS_TABLES';
select 10/0; #有警告 warning
insert into test(value) values(10/0); #报错：ERROR 1365 (22012): Division by 0
 
======================================================================
【ALLOW_INVALID_DATES】
检查日期格式合法性(DATE 或 DATETIME, 非 TIMESTAMP )
======================================================================
--    drop table test;
create table test(createdate date,name varchar(10));
 
SET sql_mode='';
select @@sql_mode;
 
#月日超出了范围,则变为: 0000-00-00
insert into test values ('2018-02-32','a');
insert into test values ('2018-13-01','b');
select * from test;
+------------+------+
| createdate | name |
+------------+------+
| 0000-00-00 | a    |
| 0000-00-00 | b    |
+------------+------+
 
SET sql_mode='ALLOW_INVALID_DATES';
select @@sql_mode;
 
insert into test values ('2018-02-32','d');
insert into test values ('2018-13-01','e');
select * from test;
+------------+------+
| createdate | name |
+------------+------+
| 0000-00-00 | a    |
| 0000-00-00 | b    |
| 0000-00-00 | d    |
| 0000-00-00 | e    |
+------------+------+
 
# ALLOW_INVALID_DATES 似乎没有起作用,启用严格模式 STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES
#启用后插入不合法日期，提示错误并终止。即严格模式下不允许插入非法值。
 
SET sql_mode='ALLOW_INVALID_DATES,STRICT_TRANS_TABLES';
insert into test values ('2018-02-32','f');
ERROR 1292 (22007): Incorrect date value: '2018-02-32' for column 'createdate' at row
 
SET sql_mode='STRICT_TRANS_TABLES';
insert into test values ('2018-02-32','g');
ERROR 1292 (22007): Incorrect date value: '2018-02-32' for column 'createdate' at row 1
 
#其他正常插入
insert into test values ('0000-00-00','h');
 
======================================================================
【NO_AUTO_VALUE_ON_ZERO】
运行序列中插入 "0",如果 id 不约束唯一, 可插入多个。插入 NULL 值默认都会自增。
======================================================================
--    drop table test;
create table test(id int not null auto_increment,name varchar(10),key(id));
 
SET sql_mode='';
 
insert into test values (1,'a');
insert into test values (0,'b');
insert into test values (null,'c');
select * from test;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
 
# 运行序列中插入 "0",如果 id 不约束唯一, 可插入多个
SET sql_mode='NO_AUTO_VALUE_ON_ZERO';
 
insert into test values (0,'d');
insert into test values (null,'e');
select * from test;
+----+------+
| id | name |
+----+------+
|  0 | d    |
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | e    |
+----+------+
 
======================================================================
【HIGH_NOT_PRECEDENCE】
未使用模式 HIGH_NOT_PRECEDENCE, "not id between 2 and 4" 相当于 "not (id between 2 and 4)"
使用模式   HIGH_NOT_PRECEDENCE, "not id between 2 and 4" 相当于 "(not id) between 2 and 4".
======================================================================
--    drop table test;
create table test(id int);
insert into test values (1),(2),(3),(4),(5);
 
SET sql_mode='';
 
SELECT NOT 1 BETWEEN -5 AND 5; #结果为0
select * from test where not id between 2 and 4;   #结果为：1、5
select * from test where not (id between 2 and 4); #结果为：1、5
select * from test where (not id) between 2 and 4; #结果为空
 
SET sql_mode='HIGH_NOT_PRECEDENCE';
 
SELECT NOT 1 BETWEEN -5 AND 5; #结果为1
select * from test where not id between 2 and 4;   #结果为空
select * from test where not (id between 2 and 4); #结果为：1、5
select * from test where (not id) between 2 and 4; #结果为空
 
======================================================================
【NO_BACKSLASH_ESCAPES】反斜杠"\"为普通字符而不是转义字符。
======================================================================
SET sql_mode='';
select '\\';
 
SET sql_mode='NO_BACKSLASH_ESCAPES';
select '\\';
 
======================================================================
【NO_UNSIGNED_SUBTRACTION】
UNSIGNED 类型如果得到一个负值，则报错。(尽量不要 UNSIGNED)
======================================================================
SET sql_mode='';
select cast(0 as unsigned) - 1;
错误：ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
 
#若想得到负值而不报错, 启用该模式
SET sql_mode='NO_UNSIGNED_SUBTRACTION';
select cast(0 as unsigned) - 1;
+-------------------------+
| cast(0 as unsigned) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+
 
 
======================================================================
【ONLY_FULL_GROUP_BY】
聚合语句安装标准写法，如 oracle sqlserver 一样。
======================================================================
--    drop table test;
create table test(name varchar(10),value int);
insert into test values ('a',45),('a',42),('b',85),('c',65),('c',39);
 
#默认情况是可能会写出无意义或错误的聚合语句：
SET sql_mode='';
select * from test group by name;
select value,sum(value) from test group by name;
 
#使用该模式后，写法标准
SET sql_mode='ONLY_FULL_GROUP_BY';
select name,sum(value) from test group by name;
 
======================================================================
【PAD_CHAR_TO_FULL_LENGTH】
对于 char、nchar 类型，默认以空字符填充，查询时自动去掉空字符。
启用该模式后，查询时空字符保留。
======================================================================
--    drop table test;
create table test(c1 char(10),c2 nchar(10));
insert into test values ('aaa','bbb');
 
SET sql_mode='';
select length(c1),length(c2),char_length(c1),char_length(c2) from test;
+------------+------------+-----------------+-----------------+
| length(c1) | length(c2) | char_length(c1) | char_length(c2) |
+------------+------------+-----------------+-----------------+
|          3 |          3 |               3 |               3 |
+------------+------------+-----------------+-----------------+
 
SET sql_mode='PAD_CHAR_TO_FULL_LENGTH';
select length(c1),length(c2),char_length(c1),char_length(c2) from test;
+------------+------------+-----------------+-----------------+
| length(c1) | length(c2) | char_length(c1) | char_length(c2) |
+------------+------------+-----------------+-----------------+
|         10 |         10 |              10 |              10 |
+------------+------------+-----------------+-----------------+
 
 
======================================================================
【NO_DIR_IN_CREATE】
创建表分区时，忽略命令 INDEX DIRECTORY 和 DATA DIRECTORY。用于副本示例中的选项。
======================================================================
 
SET sql_mode='';
 
create table test(id int)
partition by range(id)(
    partition p0 values less than (5),
    partition p1 values less than maxvalue
);
 
 
SET sql_mode='NO_DIR_IN_CREATE';
 
create table test(id int)
ENGINE=MyISAM
partition by range(id)(
    partition p0 values less than (5)
        DATA DIRECTORY = 'E:/AAA/P0/data'  
        INDEX DIRECTORY = 'E:/AAA/P0/idx', 
    partition p1 values less than maxvalue
        DATA DIRECTORY = 'E:/AAA/P1/data'  
        INDEX DIRECTORY = 'E:/AAA/P1/idx'
);