mysql的sql_mode设置

参考官方文档：

mysql可以为不同的客户端设置不同的sql_mode,并且每个应用能够设置他自己的会话级别的sql_mode。sql_mode会影响sql语法以及mysql显示数据的正确性。

When working with InnoDB tables, consider also the innodb_strict_mode system variable. It enables additional error checks for InnoDB tables.

官方文档建议：当使用innodb存储引擎表时，考虑使用innodb_strict_mode模式的sql_mode,它能增量额外的错误检测功能。

设置sql mode

mysql5.7默认的sql mode值如下：

root@(none) 05:48:26>show variables like "sql_mode";
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | 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 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

root@(none) 05:48:34>

在服务启动之前在配置文件中设置sql mode：

sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

或者在启动mysql的时候使用--sql-mode="value"来指定sql mode的值。

可以在命令行设置全局的sql mode，也可以设置会话级别的。

set global sql_mode="modes";        #全局的设置影响连接的每一个客户端，
set session sql_mode="modes";       #会话级别的设置只影响当前的客户端，

（需要说明的是，在命令行的设置重启服务后会失效）

mysql支持三种级别的sql mode

ANSI：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。

STRICT_TRANS_TABLES:

If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a
 single-row statement or the first row of a multiple-row statement. 
#对于事务表，给的值不能插入则终止该语句。对于非事务表，若是单行语句或者多行语句的第一行则中断（）

TRADITIONAL(traditional): mysql对插入的数据不会报警告而是直接给出错误。当使用的是事务表，发生错误时会直接回滚；若是非事务表则会造成数据的不一致的情况（错误前的数据已经插入）

mysql支持的所有模式

ALLOW_INVALID_DATES: 字母意思允许不合法的数据。不对日期做全面的检查，仅仅检查月份是否在1~12之间，天数是否在1~31之间；这种模式可能是有用的对web应用来说去获取年，月，日在三个不同的字段并且准确存储用户的输入数值，没有验证数据的合法性。这种模式对date和datetime类型有作用，但是对timestamp类型不起作用，timestamp总是要合法的数据。当ALLOW_INVALID_DATES启用时，服务端要求年和月时合法的。如果strict模式禁用，不合法的数据如"2004-04-31"被存储为"0000-00-00"并且审查警告；若严格模式启用则会生成错误。（最后这一句我确定没有翻译错，但是测试的时候，数据时原样插入的，没有转换为0000-00-00）

root@testdb 06:19:08>create table tb2(l1 date);
Query OK, 0 rows affected (0.22 sec)
root@testdb 06:23:23>set session sql_mode="ALLOW_INVALID_DATES";
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@testdb 06:23:41>insert into tb2 values("2012-02-30");
Query OK, 1 row affected (0.00 sec)

root@testdb 06:23:45>select * from tb2;
+------------+
| l1         |
+------------+
| 2012-02-30 |
+------------+
1 row in set (0.00 sec)

root@testdb 06:23:56>insert into tb2 values("2012-04-31");
Query OK, 1 row affected (0.09 sec)

root@testdb 06:24:44>select * from tb2;
+------------+
| l1         |
+------------+
| 2012-02-30 |
| 2012-04-31 |
+------------+
2 rows in set (0.00 sec)

root@testdb 06:24:46>set session sql_mode="TRADITIONAL";
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@testdb 06:26:26>insert into tb2 values("2012-06-31");
ERROR 1292 (22007): Incorrect date value: '2012-06-31' for column 'l1' at row 1
root@testdb 06:26:38>

测试结果

ANSI_QUOTES：这个模式反人类，大致意思就是启用来这个模式，不能使用双引号来引用字符创，因为双引号也被认为是一种标识字符创，可以使用单引号。

root@testdb 06:35:17>create table tb2(name varchar(20));
Query OK, 0 rows affected (0.04 sec)

root@testdb 06:35:54>set session sql_mode="ANSI_QUOTES";
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@testdb 06:36:04>insert into tb2 values("puting");           #双引号不能使用
ERROR 1054 (42S22): Unknown column 'puting' in 'field list'
root@testdb 06:36:24>insert into tb2 values('puting');           #使用单引号引用字符串
Query OK, 1 row affected (0.03 sec)

root@testdb 06:36:35>insert into tb2 values('"');                #把双引号作为字符串插入
Query OK, 1 row affected (0.01 sec)

root@testdb 06:36:59>select * from tb2;
+--------+
| name   |
+--------+
| puting |
| "      |
+--------+
2 rows in set (0.00 sec)

root@testdb 06:37:06>

测试结果

ERROR_FOR_DIVISION_BY_ZERO：如果这个模式未启用，那么零除操作将会插入空值并且不会产生警告；如果这个模式启用，零除操作插入空值并产生警告；如果这个模式和严格模式都启用，零除从操作将会产生一个错误，除非使用来ignore来忽略错误。例如insert ignore和update ignore，这样的话零除操作将插入空并发出警告。ERROR_FOR_DIVISION_BY_ZERO不是严格模式的一部分，应该和严格模式一起启用，默认是启用的。
HIGH_NOT_PRECEDENCE: (高not优先级):在如下一个表达式中not a between b and c,在当前设置下被解析为not (a between b and c)，在旧的行为中同一个表达式被解析为(not a) between b and c;启用了HIGH_NOT_PRECEDENCE这个模式，就是启用来旧的优先级设置，表达式将被解析为后一种行为。
IGNORE_SPACE：用于忽略mysql系统函数名与之后括号之间的空格。https://www.cnblogs.com/JiangLe/p/6892831.html
NO_AUTO_CREATE_USER：禁止grant语句自动创建用户，除非认证信息被指定，语句必须包含一个非空的密码使用identified by或使用认证插件identified with.
NO_AUTO_VALUE_ON_ZERO: 这个值影响自增列，正常情况下可以通过插入“0”值或者null空值来生成自增序列的下一个值。NO_AUTO_VALUE_ON_ZERO模式会抑制这种行为，仅仅当插入null值才会生成下一个序列的自增值。这个模式是非常有用的当0值已经在表的自增序列中存储时。例如，如果你dump这个表并且重新reload它，mysql可能会生成新的值当导入数值为0的值时，这样就会导致数据的不一致，启用这个模式在导入dumo出的文件之前可以解决这个不一致的问题。mysqldump会自动包含了这个模式。
NO_BACKSLASH_ESCAPES：（禁用反斜线转义）这个模式启用，反斜线将会变成一个普通的字符串。
NO_DIR_IN_CREATE：在创建表时忽略所有的index directory和data directory选项。
NO_ENGINE_SUBSTITUTION: 此模式指定当执行create语句或者alter语句指定的存储引擎没有启用或者没有编译时，控制默认默认存储引擎的自动切换。默认是启用的。当NO_ENGINE_SUBSTITUTION被禁用，当create表时的默认存储引擎不可使用则产生警告信息，对于alter语句产生警告并且表不会被alter。当NO_ENGINE_SUBSTITUTION启用，会生成错误并且表不会被创建或alter如果期望的存储引擎不可用。
NO_FIELD_OPTIONS：这个模式将会在5.7.22中删除。
NO_KEY_OPTIONS， NO_TABLE_OPTIONS:同上。

NO_UNSIGNED_SUBTRACTION: 在整型数值之间的减法，一个值得类型是unsigned的，那么默认结果也是unsigned的。若是结果是一个负数，则会产生一个错误。

root@testdb 10:07:04>select cast(0 as unsigned) -1 ;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
root@testdb 10:15:14>

若启用了这个模式，结果将是负数。

root@testdb 10:15:14>set session sql_mode="NO_UNSIGNED_SUBTRACTION";
Query OK, 0 rows affected (0.00 sec)

root@testdb 10:16:19>select cast(0 as unsigned) -1 ;
+------------------------+
| cast(0 as unsigned) -1 |
+------------------------+
|                     -1 |
+------------------------+
1 row in set (0.00 sec)

root@testdb 10:16:21>

如果这个结果被用于更新一个unsigned整型的字段，结果将会被裁减为最大值或者裁减为0，如果NO_UNSIGNED_SUBTRACTION这个模式启用。在严格模式下将会发生错误。

启用NO_UNSIGNED_SUBTRACTION模式：
root@testdb 10:06:26>set session sql_mode="NO_FIELD_OPTIONS";
Query OK, 0 rows affected (0.01 sec)
root@testdb 10:16:21>create table tb3(c1 int unsigned);
Query OK, 0 rows affected (0.04 sec)

root@testdb 10:21:22>insert into tb3 select cast(0 as unsigned) -1;
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 1

root@testdb 10:21:44>select * from tb3;
+------+
| c1   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

root@testdb 10:21:54>

#默认已经启用了严格模式，在新的连接中插入此值。
root@(none) 10:22:07>use testdb;
Database changed
root@testdb 10:22:12>insert into tb3 select cast(0 as unsigned) -1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
root@testdb 10:22:19>

测试结果

NO_ZERO_DATE:这个模式影响着插入的'0000-00-00'值是否作为合法的数值，这个结果和是否启用严格模式有关。如果这个模式禁用，则'0000-00-00'被允许并且插入没有警告，如果这个模式启用，则'0000-00-00'被运行并且插入产生一个警告信息；如果这个模式和严格模式被启用，则'0000-00-00'不被允许并且插入产生错误，除非ignore被使用。NO_ZERO_DATE不是严格模式的一部分，应该和严格模式一起被使用。因为NO_ZERO_DATE将会被放弃在将来的mysql中，它的影响将会被包含进严格模式中。
NO_ZERO_IN_DATE：这个模式影响着日期中的月份和天数是否可以为0（注意年份是非0的），这个模式也取决于严格模式是否被启用。如果这个模式未启用，date中的零部分被允许并且插入没有警告。如果这个模式启用，dates中的零部分插入被作为“0000-00-00”并且产生一个警告。如果这个模式和严格模式被启用，则dates中的零部分不被允许并且插入产生错误，除非ignore也被使用。这个模式也不是严格模式的一部分，应该和严格模式一起被使用。
ONLY_FULL_GROUP_BY：这个模式对查询的影响有点大。mysql5.7默认启用这个模式，这个模式是指在mysql的select查询不能出现除group by语句字段之外的其余字段。

　　不存在于group_by中的字段需要使用，可以使用聚合函数。

　　有如下表中数据：

root@testdb 11:12:36>select * from emp;
+------+----------+--------+--------+
| id   | ename    | sal    | deptno |
+------+----------+--------+--------+
| 1001 | emp_1001 | 100.00 |     10 |
| 1002 | emp_1002 | 200.00 |     10 |
| 1003 | emp_1003 | 300.00 |     20 |
| 1004 | emp_1004 | 400.00 |     20 |
| 1005 | emp_1005 | 500.00 |     30 |
| 1006 | emp_1006 | 600.00 |     30 |
+------+----------+--------+--------+
6 rows in set (0.00 sec)
#在mysql5.6中我们的如下查询是正确的
root@testdb 11:12:26>select id, ename, sal,deptno from emp group by deptno;
+------+----------+--------+--------+
| id   | ename    | sal    | deptno |
+------+----------+--------+--------+
| 1001 | emp_1001 | 100.00 |     10 |
| 1003 | emp_1003 | 300.00 |     20 |
| 1005 | emp_1005 | 500.00 |     30 |
+------+----------+--------+--------+
3 rows in set (0.05 sec)

但是在mysql5.7中就会报错，因为在select的查询字段中出现来group by之外的字段旧报错了
root@testdb 11:11:09>select id, ename, sal,deptno from emp group by deptno;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'testdb.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

PAD_CHAR_TO_FULL_LENGTH：默认情况下，会自动修剪char列的空格。但是若这个模式启用，不会修剪空格，会自动补全。这个模式对varchar字段不起作用。

#创建表
root@testdb 11:36:51>create table tb4(c1 char(6), c2 varchar(6));
Query OK, 0 rows affected (0.09 sec)
#插入数据
root@testdb 11:37:33>insert into tb4 values("aa","bb");
Query OK, 1 row affected (0.00 sec)

root@testdb 11:38:07>select * from tb4;
+------+------+
| c1   | c2   |
+------+------+
| aa   | bb   |
+------+------+
1 row in set (0.00 sec)

#查看长度
root@testdb 11:41:25>select char_length(c1), char_length(c2) from tb4;
+-----------------+-----------------+
| char_length(c1) | char_length(c2) |
+-----------------+-----------------+
|               2 |               2 |
+-----------------+-----------------+
1 row in set (0.00 sec)

#修改sql_mode的数值
root@testdb 11:38:13>set session sql_mode="PAD_CHAR_TO_FULL_LENGTH";
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@testdb 11:38:56>select * from tb4;         #可以看到aa列的值的宽度
+--------+------+
| c1     | c2   |
+--------+------+
| aa     | bb   |
+--------+------+
1 row in set (0.00 sec)

#看到这里c1的长度为6
root@testdb 11:40:52>select char_length(c1), char_length(c2) from tb4;
+-----------------+-----------------+
| char_length(c1) | char_length(c2) |
+-----------------+-----------------+
|               6 |               2 |
+-----------------+-----------------+
1 row in set (0.00 sec)

PIPES_AS_CONCAT：将 “||”作为并运算符（像concat一样），而不是逻辑运算符or.
REAL_AS_FLOAT: 将real作为float的同义词。默认的，mysql将real作为double的同义词。
STRICT_ALL_TABLES：启动严格模式未所有的存储引擎，不合法的数据将会被拒绝。STRICT_ALL_TABLES模式将包含： ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE 模式。
STRICT_TRANS_TABLES：启动严格模式未事务存储引擎，STRICT_TRANS_TABLES将包含 ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE 模式的影响。

组合sql mode

以下组合模式未几种sql_mode组合的简写。

组合sql mode不再说明，可以查看官网：就是一个组合的简写相当于几个sql mode的配置。官网地址：https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes

有关严格模式的一些说明，官方文档给了比较详细的说明。

posted @ 2019-04-28 13:45 夜间独行的浪子阅读(2268) 评论(0) 编辑收藏举报

刷新页面返回顶部

mysql的sql_mode设置

设置sql mode

mysql支持三种级别的sql mode

mysql支持的所有模式

组合sql mode

公告