MySQL主要的SQL_Mode值详解

  • ANSI

更改语法和行为， 使其更符合标准SQL。

  • STRICT_TRANS_TABLES
  • TRADITIONAL

使MySQL的行为象“传统”SQL数据库系统。 该模式的简单描述是当在列中插入不正确的值时“给 出 错误 而不是警告” 等同STRICT_TRANS_TABLES、 STRICT_ALL_TABLES、NO_ZERO_IN_DATE、 NO_ZERO_DATE、 ERROR_FOR_DIVISION_BY_ZERO、 NO_AUTO_CREATE_USER。

sql mode常用值

  •  ONLY_FULL_GROUP_BY

对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在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_by
mysql> 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 1
 
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 |
+--------------------------------------------------------------------------------+
 
#改成ANSI模式就可以插入成功了，只不过识别成了0
mysql> 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 0
 
mysql> 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 1
 
mysql> 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 |

　　

posted on 2025-01-11 16:43  数据与人文  阅读(94)  评论(0)    收藏  举报