参考官方文档:
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,也可以设置会话级别的。
global sql_mode session sql_mode"modes"; #会话级别的设置只影响当前的客户端,
mysql支持三种级别的sql mode
- ANSI: 宽松模式,对插入数据进行校验,如果不符合定义类型或长度,对数据类型调整或截断保存,报warning警告。
- STRICT_TRANS_TABLES:
a value could be inserted given a transactional , abort the statement. a nontransactional , abort the statement the value occurs a row statement the first row a multiplerow 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 1root@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: 1root@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字段不起作用。:: tb4(c1 (), c2 ( rows affected ( :: tb4 row affected ( :: c1 c2 aa bb row ( :: session sql_mode rows affected, warning ( :: c1 c2 aa bb row (
-
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
, andNO_ZERO_IN_DATE
模式。 -
STRICT_TRANS_TABLES
:启动严格模式未事务存储引擎,STRICT_TRANS_TABLES
将包含ERROR_FOR_DIVISION_BY_ZERO
,NO_ZERO_DATE
, andNO_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
有关严格模式的一些说明,官方文档给了比较详细的说明。