MySQL 5.7 模式(SQL_MODE)详细说明_mysql 5.7 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; 
 
在未了解各种模式的影响下，表分区后建议不要再变更模式，同步复制的实例也建议模式保持一致。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

======================================================================
【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'
);
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381