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,也可以设置会话级别的。

 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)

    mysql的sql_mode设置mysql的sql_mode设置

    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:这个模式反人类,大致意思就是启用来这个模式,不能使用双引号来引用字符创,因为双引号也被认为是一种标识字符创,可以使用单引号。

    mysql的sql_mode设置mysql的sql_mode设置

    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这个模式启用。在严格模式下将会发生错误。

    mysql的sql_mode设置mysql的sql_mode设置

    启用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_ZERONO_ZERO_DATE, and NO_ZERO_IN_DATE 模式。
  • STRICT_TRANS_TABLES:启动严格模式未事务存储引擎,STRICT_TRANS_TABLES将包含 ERROR_FOR_DIVISION_BY_ZERONO_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

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

 

上一篇:[Aaronyang] 写给自己的WPF4.5 笔记15[AyArc诞生-WPF版本绚丽的环状图,Ay制作,AyWindow强势预览]


下一篇:mysql的sql_mode设置