MySQL SQL_MODE选项介绍

刺骨的言语ヽ痛彻心扉 2022-02-16 20:59 216阅读 0赞

偶尔会碰到因为SQL\_MODE参数不一样，导致同样的SQL运行得到不一样的结果，或是执行报错。  
MySQL在不同版本中有不同的SQL\_MODE默认值，以下查看SQL\_MODE参数设置：  
select @@session.sql\_mode;  
select @@global.sql\_mode;

以下介绍一下SQL\_MODE选项。  
1.STRICT\_TRANS\_TABLES/STRICT\_ALL\_TABLES:被称为严格模式，针对Innodb和Myisam表，有不同的作用，具体讲解见前面博文《SQL\_MODE中的STRICT\_TRANS\_TABLES和STRICT\_ALL\_TABLES》；  
2.ALLOW\_INVALID\_DATES:对日期数据不进行严格的检查，只检查月份是否在1-12，日期是否在1-31，但只对date和datetime有效，而timestamp总是要求合法的时间输入。同时，ALLOW\_INVALID\_DATES不只是要求月份和日期在1-12和1-31，对于在范围内但不合理的时间值，例如 ‘2004-04-31’会转化为’0000-00-00’，并且产生warning；  
3.ANSI\_QUOTES:双引号不能用来引用字符串，而被双引号引用的字符串会被解释为标识符，例如

mysql> set @@session.sql_mode='';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select "sam" from test;
    +-----+
    | sam |
    +-----+
    | sam |
    | sam |
    | sam |
    +-----+
    3 rows in set (0.00 sec)
    
    mysql> set @@session.sql_mode='ansi_quotes';
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> select "sam" from test;
    ERROR 1054 (42S22): Unknown column 'sam' in 'field list'

4.ERROR\_FOR\_DIVISION\_BY\_ZERO:在insert或update时出现除以0或者以0取模(mod(N,0))，根据是否设置为严格模式，有以下三种情况：  
4.1 当不设置ERROR\_FOR\_DIVISION\_BY\_ZERO，除以0或以0取模的结果为null，不产生告警；  
4.2 当设置ERROR\_FOR\_DIVISION\_BY\_ZERO，除以0或以0取模的结果为null，并产生告警；  
4.3 当设置ERROR\_FOR\_DIVISION\_BY\_ZERO和严格模式，除以0或以0取模会产生报错，除非insert和update配合ignore适用；  
对于select查询，除以0或以0取模在任何情况下都会返回null，如果设置ERROR\_FOR\_DIVISION\_BY\_ZERO，则会产生warnings；  
ERROR\_FOR\_DIVISION\_BY\_ZERO并不包含在严格模式里，但这个选项即将被废弃，在未来的版本会合并到严格模式里。

5.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即采用旧版本的解释方式；

6.IGNORE\_SPACE:允许函数名和()之间有空格，只是对于内置函数而言，对于用户定义的函数，总是允许和()之间有空格，例如：

mysql> CREATE TABLE count (i INT);
    ERROR 1064 (42000): You have an error in your SQL syntax
    
    mysql> CREATE TABLE `count` (i INT);
    Query OK, 0 rows affected (0.00 sec)

7.NO\_AUTO\_CREATE\_USER:禁止grant语句创建密码为空的用户；

8.NO\_AUTO\_VALUE\_ON\_ZERO:对于自增键，插入0时，原值插入，而不使用自增键值；

9.NO\_BACKSLASH\_ESCAPES:反斜杠\\作为普通字符，而非转义字符；

10.NO\_DIR\_IN\_CREATE:建表时忽略 INDEX DIRECTORY 和 DATA DIRECTORY 选项，在从库复制时有用；

11.NO\_ENGINE\_SUBSTITUTION:默认开启，当不开启时，对于create table指定了不可用的引擎，会使用默认的引擎并产生warnings，对于alter table指定了不可用的引擎，会产生warnings并执行失败；当开启时，create table和alter table指定了不可用的引擎时，产生报错；

12.NO\_UNSIGNED\_SUBTRACTION:两个unsigned类型相减得到signed类型数据；

13.NO\_ZERO\_DATE:不允许插入零日期，如 ‘0000-00-00’ ；

14.ONLY\_FULL\_GROUP\_BY:对于group by聚合操作，如果在select中出现的列没有在group by中出现，那么这种SQL是不合法的;

15.PAD\_CHAR\_TO\_FULL\_LENGTH:对char类型不进行空洞数据阶段，空洞数据是指，char类型自动填充的Ox20的数据

16.PIPES\_AS\_CONCAT:||视为字符串的连接操作符，跟Oracle一样；

17.REAL\_AS\_FLOAT:REAL视为FLOAT,而非double

18.ANSI:组合选项，等于 REAL\_AS\_FLOAT, PIPES\_AS\_CONCAT, ANSI\_QUOTES, IGNORE\_SPACE, 和 (MySQL 5.7.5) ONLY\_FULL\_GROUP\_BY;

19.DB2:相当于PIPES\_AS\_CONCAT, ANSI\_QUOTES, IGNORE\_SPACE, NO\_KEY\_OPTIONS,NO\_TABLE\_OPTIONS, NO\_FIELD\_OPTIONS;

20.MAXDB:相当于 PIPES\_AS\_CONCAT, ANSI\_QUOTES, IGNORE\_SPACE, NO\_KEY\_OPTIONS,NO\_TABLE\_OPTIONS, NO\_FIELD\_OPTIONS, NO\_AUTO\_CREATE\_USER;

21.MSSQL:相当于 PIPES\_AS\_CONCAT, ANSI\_QUOTES, IGNORE\_SPACE, NO\_KEY\_OPTIONS,NO\_TABLE\_OPTIONS, NO\_FIELD\_OPTIONS;

22.Oracle:相当于 PIPES\_AS\_CONCAT, ANSI\_QUOTES, IGNORE\_SPACE, NO\_KEY\_OPTIONS,NO\_TABLE\_OPTIONS, NO\_FIELD\_OPTIONS, NO\_AUTO\_CREATE\_USER;

23.POSTGRESQL:相当于 PIPES\_AS\_CONCAT, ANSI\_QUOTES, IGNORE\_SPACE, NO\_KEY\_OPTIONS,NO\_TABLE\_OPTIONS, NO\_FIELD\_OPTIONS;

24.TRADITIONAL:相当于 STRICT\_TRANS\_TABLES, STRICT\_ALL\_TABLES, NO\_ZERO\_IN\_DATE, NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO, NO\_AUTO\_CREATE\_USER, and NO\_ENGINE\_SUBSTITUTION