MySQL SQL模式

MySQL服务器可以以不同的SQL模式来进行操作，并且依赖于sql_mode系统变量的值对不同的客户端可以应用这些不同的SQL模式。DBA可以设置全局SQL模式来匹配服务器操作要求，并且每种应用程序可以设置它的会话SQL模式来满足它的要求。

SQL模式会影响MySQL支持的SQL语法和数据验证检查。这可以在不同环境中让MySQL与其它数据库一起使用变得更容易。

当使用InnoDB表时，可以考虑使用innodb_strict_mode系统变量，它可以对InnoDB表启用额外的错误检查。

设置SQL模式
在MySQL 5.7中缺省的SQL模式包含: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。ONLY_FULL_GROUP_BY和STRICT_TRANS_TABLES是在MySQL 5.7.5中加入的。NO_AUTO_CREATE_USER是在MySQL 5.7.7中加入的。ERROR_FOR_DIVISION_BY_ZERO，NO_ZERO_IN_DATE和NO_ZERO_DATE是在MySQL 5.7.8中加入的。

为了在服务器启动时设置SQL模式，可以在命令行中使用--sql-mode="modes"选项或在选项文件比如Unix中的my.cnf或Windows上的my.ini文件中使用sql-mode="modes"选项。modes是用逗号分的不同模式列表。为了显式的清除SQL模式，可以在命令行中使用--sql-mode=""选项将SQL模式设置为空字符串，或者在选项文件中使用sql-mode=""。

MySQL安装程序可以在安装过程中会配置SQL模式。例如，mysql_install_db将在基本的安装目录中创建一个命名为my.cnf的缺省选项文件。这个文件包含设置SQL模式的记录。

如果SQL模式不同于缺省SQL模式或你所期待的SQL模式，可以检查服务器在启动时所读取的选项文件。

为了在运行时改变SQL模式，可以使用set语句来设置全局或会话级的sql_mode系统变量:
set global sql_mode='modes';
set session sql_mode='modes';

设置global变量需要有super权限并且影响所有连接的客户端操作。设置session变量只影响当前客户端。每个客户端可以在任何时间改变它会话的sql_mode值来达到改变SQL模式的目的。

为了判断当前全局或session级的sql_mode值，执行以下命令:

1 row in set (0.00 sec)

1 row in set (0.00 sec)
注意在创建分区表并插入数据之后改变服务器的SQL模式会对表的行为造成重大改变并且可能导致丢失或损坏数据。所以强烈建议在创建用户定义的分区表之后不要修改SQL模式。

当复制分区表时，在主从服务器之间不同的SQL模式也会导致一些问题。所以最好的结果就是在主从服务器上使用相同的SQL模式。

最重要的SQL模式
MySQL中最重要的sql_mode值可能是这些:
.ANSI
这种SQL模式改变语法和行为使其更接近标准SQL。它是一种特定的组合模式列表。

.STRICT_TRANS_TABLES
如果一个值不能以指定的方式插入到一个事务表，终止这个语句。对于非事务表，如果这个值在一个单行记录语句或在多行记录语句第一个出现时终止语句。从MySQL 5.7.5开始缺省的SQL模式包括STRICT_TRANS_TABLES。

.TRADITIONAL
使用MySQL行为像一个传统的SQL数据库系统。简单来说这种模式对于将一个不正确值插入到一个列中时抛出一个错误来代替一个警告。它是特定组合模式中的一种。insert或update一旦发现错误会立即终止。如果您正在使用非事务性存储引擎，这可能不是您想要的，因为在错误之前所做的数据更改可能无法回滚，从而导致“部分完成”的更新。

当提到"strict mode"严格模式时，它意味着是STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的一种或两种都被启用。

完整的SQL模式列表
下面是所有支持的SQL模式列表:
.ALLOW_INVALID_DATES
不对日期执行完全检查。只对月份的范围从1到12和日期的范围从1到31执行检查。这对于Web应用程序非常方便，您可以在三个不同的字段中获得年、月和日，并且希望准确地存储用户插入的内容(不执行日期校验)。这种SQL模式应用于date和datetime列。它不应用于timestamp列，因为它总是请求一个合法的日期值。

这种SQL模式要求月分与每天的取值是合法值，并且范围分别不能超过1到12和1到31。当严格模式被禁用时，一个无效的日期值比如'2014-04-31'将被转换成'0000-00-00'并且生成一个警告。当使用严格模式时，一个无效的日期值会生成一个错误。为了允许这样的日期值，启用ALLOW_INVALID_DATES模式。

.ANSI_QUOTES
将"作为标识引用字符(像引用字符)而不是字符串引用字符。当使用这种SQL模式时仍然可以使用作为引用标识符，当ANSI_QUOTES被使用时，不能使用双引号来引用文本字符串，因为它被解析作标识符了。

.ERROR_FOR_DIVISION_BY_ZERO
ERROR_FOR_DIVISION_BY_ZERO模式影响对除以零的处理，这包含MOD(N,0)。对于数据修改操作(insert,update)，它的影响也依赖于是否启用了严格SQL模式。
-如果这种模式没有被启用，除以零会插入NULL值并且没有警告。
-如果这种模式被启用，除以零会插入NULL值并生成警告。
-如果这种模式与严格SQL模式被启用，除以堆生成一个错误，除非IGNORE也被指定。对于insert ignore和update ignore，除以零会插入NULL值并生成警告。

对于select语句，除以零会返回NULL值。启用ERROR_FOR_DIVISION_BY_ZERO不管是否启用严格SQL模式会导致生成一个警告。

在MySQL 5.7.4中，ERROR_FOR_DIVISION_BY_ZERO被丢弃，在MySQL 5.7.4到5.7.7中当显式命名时ERROR_FOR_DIVISION_BY_ZERO不会做任何事。代替的是，它的影响被包含在严格SQL模式中。在MySQL 5.7.8和以后的版本中，当显式命名时ERROR_FOR_DIVISION_BY_ZERO会有影响并且没有包含在严格SQL模式中，就像MySQL5.7.4之前的版本一样。然而缺省情况下当严格模式启用下应该与它联合使用。如果ERROR_FOR_DIVISION_BY_ZERO被启用而没有启用严格模式或者当启用严格模式而没有启用ERROR_FOR_DIVISION_BY_ZERO时会出现这个警告。

因为ERROR_FOR_DIVISION_BY_ZERO被丢弃，它将在将来的版本中被删除并作为一个单独的模式名并且它的影响被包含在严格SQL模式。

.HIGH_NOT_PRECEDENCE
NOT操作的优先级就像NOT a between b and c会被解析成NOT (a between b and c)一样。在一些旧版本的MySQL中，表达式会被解析成(NOT a) between b and c。旧版本更高优先级行为可以通过启用HIGH_NOT_PRECEDENCE模式来获得。

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

1 row in set (0.00 sec)

mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
Query OK, 0 rows affected (0.00 sec)

1 row in set (0.00 sec)
.IGNORE_SPACE
允许在函数名与(符号之间存在空格。这会造成内置函数名被作为保留关键字对待。因此标识符与函数名相同必须有引号。例如，因为有一个count()函数，因此在下面的语句中使用count作为表名就会出错:

mysql> CREATE TABLE count (i INT);
Query OK, 0 rows affected (0.13 sec)

mysql> drop table count cascade;
Query OK, 0 rows affected (0.09 sec)

mysql> SET sql_mode = 'IGNORE_SPACE';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE count (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count (i INT)' at line 1

mysql> create table count (i INT);
Query OK, 0 rows affected (0.16 sec)
IGNORE_SPACE模式应用到内置函数，而不是用户定义的函数或存储过程。它总是允许在用户定义的函数或存储过程名后有空格而不管是否启用了IGNORE_SAPCE模式。

.NO_AUTO_CREATE_USER
除非指定了身份验证信息，否则将阻止GRANT语句自动创建新用户帐户。这个语句必须使用identified by来指定非空密码或使用identified with来使用一种验证插件。

最好使用create user来创建MySQL账号，然后使用Grant语句。NO_AUTO_CREATE_USER已经被丢弃并且缺省的SQL模式包含了NO_AUTO_CREATE_USER模式。将sql_mode修改为NO_AUTO_CREATE_USER模式会生成一个警告，除了指定sql_mode为DEFAULT.NO_AUTO_CREATE_USER将会在将来的版本中被删除，到时它的影响将会一直被启用。

之前，在NO_AUTO_CREATE_USER被丢弃之前，一个不启用它的原因是它是不安全的复制。现在它可以被启用并且使用create user if not exists,drop user if exists和alter user if exists而不是grant语句来执行安全复制管理。这些语句当从服务器相对于主服务器有不同的授权时可以启用安全复制。

.NO_AUTO_VALUE_ON_ZERO
NO_AUTO_VALUE_ON_ZERO影响对AUTO_INCREMENT列的处理。正常来说通过向访列插入NULL或0来为列生成下一个序列值。NO_AUTO_VALUE_ON_ZERO会抑制0的行为因此只有插入NULL时会生成下一个序列值。如果0已经被存储到表的AUTO_INCREMENT列中这个SQL模式可能是有用的。(存储0是不建议的)例如，如果使用mysqldump来dump表并且然后再加载它，当遇到值0时MySQL正常来说会生成一个新的序列值，因此表的内容不同于被dump的内容。在加载dump文件之前启用NO_AUTO_VALUE_ON_ZERO来解决这个问题。mysqldump现在在它的输出中自动包含一个语句来启用NO_AUTO_VALUE_ON_ZERO来避免这个问题。

.NO_BACKSLASH_ESCAPES
禁用在字符串中使用反斜杠字符()作为转义字符。启用此模式后，反斜杠将成为与其他字符一样的普通字符。

.NO_DIR_IN_CREATE
创建表时，忽略所有索引目录和数据目录指令。此选项在从复制服务器上非常有用。

.NO_ENGINE_SUBSTITUTION
当一个语句比如create table或alter table指定一个存储引擎已经被禁用或没有被编译时控制缺省存储引擎的自动替换。缺省的SQL模式中包含了NO_ENGINE_SUBSTITUTION。因为存储引擎可以在运行时被附加进来，不可以存储引擎也以相同方式被对待:
当NO_ENGINE_SUBSTITUTION被禁用，对于create table的缺省存储引擎被使用并且如果期待的存储引擎不可用会出现一个警告。对于alter table，会出现一个警告并且表不能被修改。
当NO_ENGINE_SUBSTITUTION被启用时，如果期待的存储引擎不可用会出现一个警告并且表不会被创建或被修改。

.NO_FIELD_OPTIONS
在show create table输出中不打印特定MySQL列选项。这种SQL模式被mysqldump以可移植模式来使用。

.NO_KEY_OPTIONS
在show create table输出中不打印特定MySQL索引选项。这种SQL模式被mysqldump以可移植模式来使用。

.NO_TABLE_OPTIONS
在show create table输出中不打印特定MySQL表选项(比如ENGINE)。这种SQL模式被mysqldump以可移植模式来使用。

.NO_UNSIGNED_SUBTRACTION
两个整数相减，这里一种类型UNSIGNED，缺省情况下生成一个没有符号的结果。如果结果出现负数将会出现错误:

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
如果NO_UNSIGNED_SUBTRACTION模式被启用，结果将是负数:

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

1 row in set (0.00 sec)
如果使用此类操作的结果更新无符号整数列，则将该结果裁剪为该列类型的最大值，如果启用no_unsigned_subtract，则将其裁剪为0。如果严格SQL模式被启用，则会出现错误并且列会保持不变。

当no_unsigned_subtraction被启用时，就算任何操作数据是无符号的相减的结果是有符号的。例如比较表t1中的c2列与表t2中的c2列:

mysql> SET sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.20 sec)

mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

1 row in set (0.00 sec)

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0

1 row in set (0.00 sec)
这意味着bigint unsigned在所有上下文中不是100%可以使用。

.NO_ZERO_DATE
NO_ZERO_DATE模式影响服务器是否允许'0000-00-00'作为一种有效的日期。它的影响也依赖于是否启用了严格SQL模式。
-如果模式没有被启用，'0000-00-00'被允许并且插入不会产生警告。
-如果模式被启用，'0000-00-00'被允许并且插入会产生警告。
-如果模式和严格SQL模式被启用，'0000-00-00'不被允许并且插入会出现错误，除非指定IGNORE选项。对于insert ignore和update ignore来说，'0000-00-00'被允许并且插入会产生警告。

在MySQL 5.7.4中，NO_ZERO_DATE被丢弃。在MySQL 5.7.4到5.7.7中，NO_ZERO_DATE当显式指定时不会工作。代替地是它的影响已经被包含在严格SQL模式中。在MySQL 5.7.8和以后的版本中，当显式地指定NO_ZERO_DATE模式时它会工作并且它不是严格SQL模式的一部分就像MySQL5.7.4版本之前作用一样。然而，缺省情况下它应该与严格SQL模式联合使用。如果启用NO_ZERO_DATE而没有启用严格SQL模式就是出现警告或者反之亦然。

因为NO_ZERO_DATE已经被丢弃，因此它会在将来的版本中作为单独的模式名被删除并且它的影响会包含在严格SQL模式中。

.NO_ZERO_IN_DATE
NO_ZERO_IN_DATE模式影响服务器是否允许日期中的年部分为非0但月或日部分为0。(这种模式影响日期比如'2010-00-01'或'2010-01-00'，而不是'0000-00-00'。为了控制服务器是否允许'0000-00-00'，使用NO_ZERO_DATE模式)。NO_ZERO_IN_DATE模式的影响也依赖于是否启用了严格SQL模式。
-如果这种模式没有启用，有为0部分折日期被允许并且插入不产生警告。
-如果这种模式被启用，有为0部分的日期值将以'0000-00-00'格式被插入并且生成一个警告。
-如果这种模式与严格SQL模式被启用，有为0部分的日期不被允许并且插入会产生一个错误，除非你指定ignore。对于insert ignore和update ignore来说，有为0部分的日期将以'0000-00-00'格式被插入并且生成一个警告。

在MySQL 5.7.4中，NO_ZERO_IN_DATE被丢弃。在MySQL 5.7.4到5.7.7中，NO_ZERO_DATE当显式指定时不会工作。代替地是它的影响已经被包含在严格SQL模式中。在MySQL 5.7.8和以后的版本中，当显式地指定NO_ZERO_IN_DATE模式时它会工作并且它不是严格SQL模式的一部分就像MySQL5.7.4版本之前作用一样。然而，缺省情况下它应该与严格SQL模式联合使用。如果启用NO_ZERO_IN_DATE而没有启用严格SQL模式就是出现警告或者反之亦然。

因为NO_ZERO_IN_DATE已经被丢弃，因此它会在将来的版本中作为单独的模式名被删除并且它的影响会包含在严格SQL模式中。

.ONLY_FULL_GROUP_BY
拒绝那些select list、HAVING condition或ORDER BY list引用非聚合列的查询，这些列既不在GROUP BY子句中命名，也不依赖于GROUP BY列(由GROUP BY列唯一确定)。

从MySQL 5.7.5开始，缺省的SQL模式包含ONLY_FULL_GROUP_BY模式。(在5.7.5之前，MySQL没有检测到功能依赖并且缺省情况下ONLY_FULL_GROUP_BY模式没有启用。

MySQL扩展了标准SQL来允许在having子句中引用select列表中的别名表达式。在MySQL 5.7.5之前，启用ONLY_FULL_GROUP_BY模式会禁用这种扩展，因此要求having子句以非别名表达式来书写。从MySQL5.7.5开始，这种限制被取消了因此having子句可以引用别名而不用管ONLY_FULL_GROUP_BY模式是否启用了。

.PAD_CAHR_TO_FULL_LENGTH
默认情况下，在检索时从CHAR列值中裁剪尾随空格。如果PAD_CHAR_TO_FULL_LENGTH被启用，裁剪不会发生并且在检索CHAR列值时填充到它的完整长度。这种模式不应用于varchar列。检索时为其保留尾随空格。

mysql> CREATE TABLE t1 (c1 CHAR(10));
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO t1 (c1) VALUES('xy');
Query OK, 1 row affected (0.03 sec)

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

1 row in set (0.00 sec)

mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)

1 row in set (0.00 sec)
.PIPES_AS_CONCAT
将||作为字符串连接操作符(与concat()一样)而不是作为OR的同义词。

.REAL_AS_FLOAT
将REAL作为FLOAT的同义词。缺省情况下，MySQL将REAL作为DOUBLE的同义词。

.STRICT_ALL_TABLES
对所有的存储引擎启用严格SQL模式。无效的数据会被拒绝。从MySQL 5.7.4到5.7.7，STRICT_ALL_TABLES模式包含了ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE模式的影响。

.STRICT_TRANS_TABLES
对事务型存储引擎启用严格SQL模式并且在可能的情况下使用非事务型存储引擎。从MySQL 5.7.4到5.7.7，STRICT_TRANS_TABLES模式包含了ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE模式的影响。