MySQL主要的SQL_Mode值详解
- ANSI
更改语法和行为， 使其更符合标准SQL。
- STRICT_TRANS_TABLES
- TRADITIONAL
sql mode常用值
- ONLY_FULL_GROUP_BY
但这有个条件：如果查询是主键列或是唯一索引且非空列，分组列根据主键列或者唯一索引且空（null）则sql 分组查询有效
- NO_AUTO_VALUE_ON_ZERO
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。
- STRICT_TRANS_TABLES
为事务存储引擎启用严格模式， 也可能为非事务存储引擎启用严格模式。
严格模式控制MySQL如何处理非法或丢失的输入值。 有几种原因可以使一个值为非法。 例如， 数据类型错 误， 不适合列， 或超出范围。 当新插入的行不包含某列的没有显示定义DEFAULT子句的值，则该值被丢失。 对于事务表， 当启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式时， 如果语句中有非法或丢失值， 则会出现错误。 语句被放弃并回滚。
- NO_ZERO_IN_DATE
在严格模式下，不允许日期和月份为零
- NO_ZERO_DATE
设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。
- ERROR_FOR_DIVISION_BY_ZERO
在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL
- NO_AUTO_CREATE_USER
禁止GRANT创建密码为空的用户
- NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
- PIPES_AS_CONCAT
将”||”视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
举例：
|
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
|
# 创建一个测试表CREATE TABLE `employee` (`eid` int(11) NOT NULL,`ename` varchar(64) DEFAULT NULL,`sex` int(11) DEFAULT NULL,PRIMARY KEY (`eid`)) ENGINE=InnoDB;# 插入几条数据insert into employee (eid,ename,sex) values (1,'keme',18),(2,'xixi',22),(3,'yj',18),(4,'kk',18),(5,'yy',18),(6,'xx',35);# 设置当前会话的sql_mode为如下mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';mysql> select eid,ename,count(*) from employee group by ename;+-----+-------+----------+| eid | ename | count(*) |+-----+-------+----------+| 1 | keme | 1 || 4 | kk | 1 || 2 | xixi | 1 || 6 | xx | 1 || 3 | yj | 1 || 5 | yy | 1 |+-----+-------+----------+# 重新设置当前的sql_mode 为如下mysql> set @@sql_mode='ONLY_FULL_GROUP_BY';mysql> select eid,ename,count(*) from employee group by ename;ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'beta.employee.eid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_bymysql> select eid,ename ,count(*) from employee group by eid;+-----+-------+----------+| eid | ename | count(*) |+-----+-------+----------+| 1 | keme | 1 || 2 | xixi | 1 || 3 | yj | 1 || 4 | kk | 1 || 5 | yy | 1 || 6 | xx | 1 |+-----+-------+----------+6 rows in set (0.00 sec)mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';mysql> insert into employee values(7,'ke','male');ERROR 1366 (HY000): Incorrect integer value: 'male' for column 'sex' at row 1mysql> 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 |+--------------------------------------------------------------------------------+#改成ANSI模式就可以插入成功了，只不过识别成了0mysql> insert into employee values(7,'ke','male');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from employee where eid=7;+-----+-------+------+| eid | ename | sex |+-----+-------+------+| 7 | ke | 0 |+-----+-------+------+1 row in set (0.00 sec)mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select @@sql_mode;+--------------------------------------------+| @@sql_mode |+--------------------------------------------+| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |+--------------------------------------------+1 row in set (0.00 sec)mysql> insert into employee values (8,'ww',17/0);Query OK, 1 row affected (0.00 sec)mysql> select * from employee where eid=8;+-----+-------+------+| eid | ename | sex |+-----+-------+------+| 8 | ww | NULL |+-----+-------+------+mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO';Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select @@sql_mode;+-----------------------------------------------------------------------+| @@sql_mode |+-----------------------------------------------------------------------+| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |+-----------------------------------------------------------------------+1 row in set (0.00 sec)mysql> insert into employee values (9,'ee',18/0);ERROR 1365 (22012): Division by 0mysql> alter table employee modify ename varchar(5);mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';mysql> insert into employee values (9,'qweradsf',11);ERROR 1406 (22001): Data too long for column 'ename' at row 1mysql> set @@sql_mode='ANSI';mysql> insert into employee values (9,'qweradsf',11);mysql> select * from employee where eid=9;+-----+-------+------+| eid | ename | sex |+-----+-------+------+| 9 | qwera | 11 |+-----+-------+------+mysql> set @@sql_mode='TRADITIONAL';Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select @@sql_mode;TRADITIONAL模式有如下值：|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 | |
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
2022-01-11 MySQL调优篇 | 索引知识解读（2）