5.1.10 服务器 SQL 模式

MySQL 服务器可以在不同的 SQL 模式下运行，并且可以根据sql_mode系统变量的值将这些模式不同地应用于不同的 Client 端。 DBA 可以设置全局 SQL 模式以匹配站点服务器操作要求，并且每个应用程序都可以将其会话 SQL 模式设置为自己的要求。

模式会影响 MySQL 支持的 SQL 语法以及它执行的数据验证检查。这使得在不同环境中使用 MySQL 以及将 MySQL 与其他数据库服务器一起使用更加容易。

• 设置 SQL 模式

• 最重要的 SQL 模式

• SQL 模式的完整列 table

• 组合 SQL 模式

• 严格的 SQL 模式

• IGNORE 关键字和严格 SQL 模式的比较

• MySQL 5.7 中的 SQL 模式更改

有关在 MySQL 中经常询问有关服务器 SQL 模式的问题的答案，请参见第 A.3 节“ MySQL 5.7 FAQ：服务器 SQL 模式”。

使用InnoDBtable 时，还应考虑innodb_strict_mode系统变量。它为InnoDBtable 启用了其他错误检查。

设置 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。

这些模式已添加到 MySQL 5.7 的默认 SQL 模式中：ONLY_FULL_GROUP_BY和STRICT_TRANS_TABLES模式已添加到 MySQL 5.7.5 中。 NO_AUTO_CREATE_USER模式是在 MySQL 5.7.7 中添加的。在 MySQL 5.7.8 中添加了ERROR_FOR_DIVISION_BY_ZERO，NO_ZERO_DATE和NO_ZERO_IN_DATE模式。有关对默认 SQL 模式值的这些更改的其他讨论，请参见MySQL 5.7 中的 SQL 模式更改。

要在服务器启动时设置 SQL 模式，请在命令行上使用--sql-mode="modes"选项，或在my.cnf(Unixos)或my.ini(Windows)等选项文件中使用sql-mode="modes"。 * modes *是用逗号分隔的不同模式的列 table。要显式清除 SQL 模式，请在命令行上使用--sql-mode=""或在选项文件中使用sql-mode=""将其设置为空字符串。

要在运行时更改 SQL 模式，请使用SET语句设置全局或会话sql_mode系统变量：

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

设置GLOBAL变量需要SUPER特权，并且会影响此后连接的所有 Client 端的操作。设置SESSION变量仅影响当前 Client 端。每个 Client 端都可以随时更改其会话sql_mode的值。

要确定当前的全局或会话sql_mode设置，请选择其值：

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

最重要的 SQL 模式

最重要的sql_mode值可能是：

• ANSI

此模式更改语法和行为以更符合标准 SQL。它是本节末尾列出的特殊combination modes之一。

• STRICT_TRANS_TABLES

如果不能按照给定值插入事务 table 中，请中止该语句。对于非事务 table，如果该值出现在单行语句或多行语句的第一行中，则中止该语句。本节稍后将提供更多详细信息。

从 MySQL 5.7.5 开始，默认的 SQL 模式包括STRICT_TRANS_TABLES。

• TRADITIONAL

使 MySQLtable 现得像“传统” SQL 数据库系统。当在列中插入不正确的值时，此模式的简单描述是“给出错误而不是警告”。它是本节末尾列出的特殊combination modes之一。

当本手册提及“严格模式”时，table 示启用了STRICT_TRANS_TABLES或STRICT_ALL_TABLES或同时启用了STRICT_TRANS_TABLES或STRICT_ALL_TABLES的模式。

SQL 模式的完整列 table

以下列 table 描述了所有受支持的 SQL 模式：

• ALLOW_INVALID_DATES

不要对日期进行全面检查。仅检查月份是否在 1 到 12 的范围内以及日期在 1 到 31 的范围内。这对于在三个不同字段中获取年，月和日并准确存储用户内容的 Web 应用程序可能很有用。已插入，没有日期验证。此模式适用于DATE和DATETIME列。它不适用于TIMESTAMP列，后者总是需要一个有效的日期。

在禁用ALLOW_INVALID_DATES的情况下，服务器要求合法的月和日值，而不仅仅是分别在 1 到 12 和 1 到 31 的范围内。禁用严格模式后，无效日期(例如'2004-04-31')将转换为'0000-00-00'并生成警告。启用严格模式后，无效日期会产生错误。要允许这样的日期，请启用ALLOW_INVALID_DATES。

• ANSI_QUOTES

将"视为标识符引号字符(如则启用此模式时引用标识符.启用ANSI_QUOTES时，您不能使用双引号引起 Literals 字符串的引用，因为它们被解释为标识符。

• ERROR_FOR_DIVISION_BY_ZERO

ERROR_FOR_DIVISION_BY_ZERO模式影响除以零的处理，其中包括MOD(N,0)。对于数据更改操作(INSERT，UPDATE)，其效果还取决于是否启用了严格的 SQL 模式。

• 如果未启用此模式，则零除将插入NULL并且不产生警告。

  • 如果启用此模式，则零除将插入NULL并产生警告。

  • 如果启用了此模式和严格模式，则除非也给出了IGNORE，否则除以零会产生错误。对于INSERT IGNORE和UPDATE IGNORE，除以零将插入NULL并产生警告。

对于SELECT，除以零将返回NULL。启用ERROR_FOR_DIVISION_BY_ZERO也会产生警告，无论是否启用严格模式。

ERROR_FOR_DIVISION_BY_ZERO已弃用。 ERROR_FOR_DIVISION_BY_ZERO不是严格模式的一部分，但应与严格模式结合使用，并且默认情况下已启用。如果启用ERROR_FOR_DIVISION_BY_ZERO而不同时启用严格模式，则会发生警告，反之亦然。有关其他讨论，请参见MySQL 5.7 中的 SQL 模式更改。

由于不推荐使用ERROR_FOR_DIVISION_BY_ZERO，因此它将在将来的 MySQL 版本中作为单独的模式名称删除，并且其影响包括在严格 SQL 模式的影响中。

• HIGH_NOT_PRECEDENCE

NOT运算符的优先级使得诸如NOT a BETWEEN b AND c之类的 table 达式被解析为NOT (a BETWEEN b AND c)。在某些旧版本的 MySQL 中，该 table 达式被解析为(NOT a) BETWEEN b AND c。通过启用HIGH_NOT_PRECEDENCE SQL 模式可以获得旧的更高优先级行为。

mysql> SET sql_mode = '';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
        -> 0
mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
        -> 1

• IGNORE_SPACE

函数名称和(字符之间允许有空格。这导致内置函数名称被视为保留字。结果，必须如第 9.2 节“架构对象名称”中所述，引用与函数名称相同的标识符。例如，由于存在COUNT()函数，因此在以下语句中使用count作为 table 名会导致错误：

mysql> CREATE TABLE count (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax

table 名应加引号：

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

IGNORE_SPACE SQL 模式适用于内置函数，不适用于用户定义的函数或存储的函数。无论是否启用了IGNORE_SPACE，都始终允许在 UDF 或存储的函数名称之后包含空格。

有关IGNORE_SPACE的进一步讨论，请参见第 9.2.5 节“函数名称的解析和解析”。

• NO_AUTO_CREATE_USER

除非指定身份验证信息，否则防止GRANT语句自动创建新的用户帐户。该语句必须使用IDENTIFIED BY指定非空密码，或使用IDENTIFIED WITH指定身份验证插件。

最好使用CREATE USER而不是GRANT创建 MySQL 帐户。 NO_AUTO_CREATE_USER已过时，默认的 SQL 模式包括NO_AUTO_CREATE_USER。更改NO_AUTO_CREATE_USER模式状态的sql_mode分配会产生警告，但将sql_mode设置为DEFAULT的分配除外。 NO_AUTO_CREATE_USER将在将来的 MySQL 版本中删除，届时将始终启用它的效果(GRANT将不会创建帐户)。

以前，在不建议使用NO_AUTO_CREATE_USER之前，不启用它的一个原因是复制不安全。现在可以启用它，并使用CREATE USER IF NOT EXISTS，DROP USER IF EXISTS和ALTER USER IF EXISTS而不是GRANT执行复制安全的用户 Management。当从站的授权与主站的授权不同时，这些语句可实现安全复制。参见第 13.7.1.2 节“ CREATE USER 语句”，第 13.7.1.3 节“ DROP USER 语句”和第 13.7.1.1 节“ ALTER USER 语句”。

• NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO影响对AUTO_INCREMENT列的处理。通常，您通过在列中插入NULL或0来生成该列的下一个序列号。 NO_AUTO_VALUE_ON_ZERO抑制了0的这种行为，因此只有NULL生成下一个序列号。

如果0已存储在 table 的AUTO_INCREMENT列中，则此模式很有用。 (顺便说一句，建议不要存储0.)例如，如果用mysqldump转储 table，然后重新加载它，则 MySQL 在遇到0值时通常会生成新的序列号，从而导致 table 的内容不同于被抛弃的那个。重新加载转储文件之前启用NO_AUTO_VALUE_ON_ZERO可解决此问题。因此，mysqldump自动在其输出中包含启用NO_AUTO_VALUE_ON_ZERO的语句。

• NO_BACKSLASH_ESCAPES

禁止在字符串和标识符中使用反斜杠字符(\)作为转义字符。启用此模式后，反斜杠将像其他字符一样成为普通字符。

• NO_DIR_IN_CREATE

创建 table 时，请忽略所有INDEX DIRECTORY和DATA DIRECTORY指令。此选项在从属复制服务器上很有用。

• NO_ENGINE_SUBSTITUTION

当诸如CREATE TABLE或ALTER TABLE之类的语句指定被禁用或未在其中编译的存储引擎时，控制默认存储引擎的自动替换。

默认情况下，启用NO_ENGINE_SUBSTITUTION。

由于存储引擎可以在运行时插入，因此无法使用的引擎将以相同的方式处理：

在禁用NO_ENGINE_SUBSTITUTION的情况下，对于CREATE TABLE使用默认引擎，如果所需的引擎不可用，则会发生警告。对于ALTER TABLE，发生警告，并且该 table 未更改。

启用NO_ENGINE_SUBSTITUTION时，如果所需的引擎不可用，则会发生错误，并且不会创建或更改 table。

• NO_FIELD_OPTIONS

不要在显示创建 table的输出中打印特定于 MySQL 的列选项。 mysqldump在可移植模式下使用此模式。

• NO_KEY_OPTIONS

不要在显示创建 table的输出中打印特定于 MySQL 的索引选项。 mysqldump在可移植模式下使用此模式。

• NO_TABLE_OPTIONS

不要在显示创建 table的输出中打印特定于 MySQL 的 table 选项(例如ENGINE)。 mysqldump在可移植模式下使用此模式。

• NO_UNSIGNED_SUBTRACTION

整数值之间的减法(其中一个为UNSIGNED类型)默认情况下会产生无符号结果。如果结果否则为负，将导致错误：

mysql> SET sql_mode = '';
Query OK, 0 rows affected (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 SQL 模式，则结果为负：

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+

如果将此类操作的结果用于更新UNSIGNED整数列，则将结果裁剪为该列类型的最大值，如果启用了NO_UNSIGNED_SUBTRACTION，则裁剪为 0.启用严格 SQL 模式后，将发生错误，并且列将保持不变。

启用NO_UNSIGNED_SUBTRACTION时，即使任何操作数都为无符号，也将对减法结果进行签名。例如，将 tablet1中的c2列的类型与 tablet2中的c2列的类型进行比较：

mysql> SET sql_mode='';
mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);
mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;
mysql> DESCRIBE t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| c2    | bigint(21) unsigned | NO   |     | 0       |       |
+-------+---------------------+------+-----+---------+-------+

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;
mysql> DESCRIBE t2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c2    | bigint(21) | NO   |     | 0       |       |
+-------+------------+------+-----+---------+-------+

这意味着BIGINT UNSIGNED并非在所有情况下均 100％可用。参见第 12.10 节“Broadcast 函数和运算符”。

• NO_ZERO_DATE

NO_ZERO_DATE模式会影响服务器是否允许'0000-00-00'作为有效日期。其效果还取决于是否启用了严格的 SQL 模式。

• 如果未启用此模式，则允许'0000-00-00'并且插入不会产生警告。

  • 如果启用此模式，则允许'0000-00-00'，并且插入会产生警告。

  • 如果启用了此模式和严格模式，则除非提供IGNORE，否则不允许'0000-00-00'并且插入会产生错误。对于INSERT IGNORE和UPDATE IGNORE，允许'0000-00-00'，并且插入会产生警告。

NO_ZERO_DATE已弃用。 NO_ZERO_DATE不是严格模式的一部分，但应与严格模式结合使用，并且默认情况下已启用。如果启用NO_ZERO_DATE而不同时启用严格模式，则会发生警告，反之亦然。有关其他讨论，请参见MySQL 5.7 中的 SQL 模式更改。

由于不推荐使用NO_ZERO_DATE，因此它将在将来的 MySQL 版本中作为单独的模式名称删除，并且其影响包括在严格 SQL 模式的影响中。

• NO_ZERO_IN_DATE

NO_ZERO_IN_DATE模式影响服务器是否允许年份部分为非零但月份或日期部分为 0 的日期。(此模式影响日期，例如'2010-00-01'或'2010-01-00'，但不影响'0000-00-00'.要控制服务器是否允许'0000-00-00'，请使用NO_ZERO_DATE模式。)NO_ZERO_IN_DATE的效果还取决于是否启用了严格的 SQL 模式。

• 如果未启用此模式，则允许零部分的日期，并且插入不会产生任何警告。

  • 如果启用此模式，则将零部分的日期作为'0000-00-00'插入并产生警告。

  • 如果启用了此模式和严格模式，则不允许零部分的日期，并且插入也会产生错误，除非也给出了IGNORE。对于INSERT IGNORE和UPDATE IGNORE，将零部分的日期作为'0000-00-00'插入并产生警告。

NO_ZERO_IN_DATE已弃用。 NO_ZERO_IN_DATE不是严格模式的一部分，但应与严格模式结合使用，并且默认情况下已启用。如果启用NO_ZERO_IN_DATE而不同时启用严格模式，则会发生警告，反之亦然。有关其他讨论，请参见MySQL 5.7 中的 SQL 模式更改。

由于不推荐使用NO_ZERO_IN_DATE，因此它将在将来的 MySQL 版本中作为单独的模式名称删除，并且其影响包括在严格 SQL 模式的影响中。

• ONLY_FULL_GROUP_BY

拒绝查询，选择列 table，HAVING条件或ORDER BY列 table 引用的非聚集列既不在GROUP BY子句中命名，也不在功能上依赖于(唯一地确定)GROUP BY列。

从 MySQL 5.7.5 开始，默认的 SQL 模式包括ONLY_FULL_GROUP_BY。 (在 5.7.5 之前，MySQL 不检测功能依赖关系，默认情况下未启用ONLY_FULL_GROUP_BY。有关 5.7.5 之前的行为的说明，请参见MySQL 5.6 参考手册。)

MySQL 对标准 SQL 的扩展允许HAVING子句中的引用引用选择列 table 中的别名 table 达式。在 MySQL 5.7.5 之前，启用ONLY_FULL_GROUP_BY会禁用此扩展，因此要求HAVING子句使用无别名 table 达式编写。从 MySQL 5.7.5 开始，取消了此限制，因此HAVING子句可以引用别名，而不管是否启用了ONLY_FULL_GROUP_BY。

有关其他讨论和示例，请参见第 12.20.3 节“ MySQL BY GROUP BY 的处理”。

• PAD_CHAR_TO_FULL_LENGTH

默认情况下，检索时会根据CHAR列的值修剪尾随空格。如果启用了PAD_CHAR_TO_FULL_LENGTH，则不会进行修整，并且将检索到的CHAR值填充到其全长。此模式不适用于VARCHAR列，在检索时保留其尾随空格。

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

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

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

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------+-----------------+
| c1   | CHAR_LENGTH(c1) |
+------+-----------------+
| xy   |               2 |
+------+-----------------+
1 row in set (0.00 sec)

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

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------------+-----------------+
| c1         | CHAR_LENGTH(c1) |
+------------+-----------------+
| xy         |              10 |
+------------+-----------------+
1 row in set (0.00 sec)

• PIPES_AS_CONCAT

将||视为字符串连接运算符(与CONCAT()相同)，而不是OR的同义词。

• REAL_AS_FLOAT

将REAL视为FLOAT的同义词。默认情况下，MySQL 将REAL视为DOUBLE的同义词。

• STRICT_ALL_TABLES

为所有存储引擎启用严格的 SQL 模式。无效的数据值将被拒绝。有关详细信息，请参见严格的 SQL 模式。

从 MySQL 5.7.4 到 5.7.7，STRICT_ALL_TABLES包括ERROR_FOR_DIVISION_BY_ZERO，NO_ZERO_DATE和NO_ZERO_IN_DATE模式的效果。有关其他讨论，请参见MySQL 5.7 中的 SQL 模式更改。

• STRICT_TRANS_TABLES

为事务性存储引擎以及可能的情况下为非事务性存储引擎启用严格的 SQL 模式。有关详细信息，请参见严格的 SQL 模式。

从 MySQL 5.7.4 到 5.7.7，STRICT_TRANS_TABLES包括ERROR_FOR_DIVISION_BY_ZERO，NO_ZERO_DATE和NO_ZERO_IN_DATE模式的效果。有关其他讨论，请参见MySQL 5.7 中的 SQL 模式更改。

组合 SQL 模式

提供以下特殊模式，作为上述列 table 中模式值组合的简写。

• ANSI

等效于REAL_AS_FLOAT，PIPES_AS_CONCAT，ANSI_QUOTES，IGNORE_SPACE和(自 MySQL 5.7.5 起)ONLY_FULL_GROUP_BY。

ANSI模式还会导致服务器返回查询错误，其中无法将具有外部引用S(outer_ref)的集合函数* S *聚合到已解决外部引用的外部查询中。这是一个查询：

SELECT * FROM t1 WHERE t1.a IN (SELECT MAX(t1.b) FROM t2 WHERE ...);

在此，MAX(t1.b)无法在外部查询中聚合，因为它出现在该查询的WHERE子句中。在这种情况下，标准 SQL 需要一个错误。如果未启用ANSI模式，则服务器在此类查询中以与S(const)相同的方式对待S(outer_ref)。

See 第 1.8 节“ MySQL 标准符合性”.

• DB2

等效于PIPES_AS_CONCAT，ANSI_QUOTES，IGNORE_SPACE，NO_KEY_OPTIONS，NO_TABLE_OPTIONS，NO_FIELD_OPTIONS。

• MAXDB

等效于PIPES_AS_CONCAT，ANSI_QUOTES，IGNORE_SPACE，NO_KEY_OPTIONS，NO_TABLE_OPTIONS，NO_FIELD_OPTIONS，NO_AUTO_CREATE_USER。

• MSSQL

等效于PIPES_AS_CONCAT，ANSI_QUOTES，IGNORE_SPACE，NO_KEY_OPTIONS，NO_TABLE_OPTIONS，NO_FIELD_OPTIONS。

• MYSQL323

等效于MYSQL323，HIGH_NOT_PRECEDENCE。这意味着HIGH_NOT_PRECEDENCE以及MYSQL323特有的显示创建 table行为：

• TIMESTAMP列显示不包含DEFAULT或ON UPDATE属性。

  • 字符串列显示不包括字符集和排序规则属性。对于CHAR和VARCHAR列，如果排序规则为二进制，则BINARY附加到列类型。

  • ENGINE=engine_nametable 选项显示为TYPE=engine_name。

  • 对于MEMORY个 table，存储引擎显示为HEAP。

• MYSQL40

等效于MYSQL40，HIGH_NOT_PRECEDENCE。这意味着HIGH_NOT_PRECEDENCE以及MYSQL40特有的一些行为。这些与MYSQL323相同，除了显示创建 table不显示HEAP作为MEMORYtable 的存储引擎。

• ORACLE

等效于PIPES_AS_CONCAT，ANSI_QUOTES，IGNORE_SPACE，NO_KEY_OPTIONS，NO_TABLE_OPTIONS，NO_FIELD_OPTIONS，NO_AUTO_CREATE_USER。

• POSTGRESQL

等效于PIPES_AS_CONCAT，ANSI_QUOTES，IGNORE_SPACE，NO_KEY_OPTIONS，NO_TABLE_OPTIONS，NO_FIELD_OPTIONS。

• TRADITIONAL

在 MySQL 5.7.4 之前和 MySQL 5.7.8 及更高版本中，TRADITIONAL等效于STRICT_TRANS_TABLES，STRICT_ALL_TABLES，NO_ZERO_IN_DATE，NO_ZERO_DATE，ERROR_FOR_DIVISION_BY_ZERO，NO_AUTO_CREATE_USER和NO_ENGINE_SUBSTITUTION。

从 MySQL 5.7.4 至 5.7.7，TRADITIONAL等效于STRICT_TRANS_TABLES，STRICT_ALL_TABLES，NO_AUTO_CREATE_USER和NO_ENGINE_SUBSTITUTION。 NO_ZERO_IN_DATE，NO_ZERO_DATE和ERROR_FOR_DIVISION_BY_ZERO模式未命名，因为在这些版本中，它们的作用包含在严格 SQL 模式(STRICT_ALL_TABLES或STRICT_TRANS_TABLES)的作用中。因此，TRADITIONAL的效果在所有 MySQL 5.7 版本中都是相同的(并且与 MySQL 5.6 中的相同)。有关其他讨论，请参见MySQL 5.7 中的 SQL 模式更改。

严格的 SQL 模式

严格模式控制 MySQL 如何处理数据更改语句(例如INSERT或UPDATE)中的无效或缺失值。值可能由于多种原因而无效。例如，它可能具有错误的列数据类型，或者可能超出范围。当要插入的新行不包含非NULL列(其定义中没有明确的DEFAULT子句)的值时，缺少值。 (对于NULL列，如果缺少值则插入NULL.)严格模式还会影响 DDL 语句，例如CREATE TABLE。

如果严格模式无效，则 MySQL 会为无效或缺失的值插入调整后的值，并产生警告(请参见第 13.7.5.40 节“ SHOW 警告声明”)。在严格模式下，可以使用INSERT IGNORE或UPDATE IGNORE产生此行为。

对于诸如SELECT之类的不更改数据的语句，无效值会在严格模式下生成警告，而不是错误。

对于试图创建超过最大密钥长度的密钥，严格模式会产生错误。如果未启用严格模式，则会导致警告并把密钥截断为最大密钥长度。

严格模式不影响是否检查外键约束。 foreign_key_checks可用于此目的。 (请参阅第 5.1.7 节“服务器系统变量”。)

如果启用了STRICT_ALL_TABLES或STRICT_TRANS_TABLES，则严格的 SQL 模式有效，尽管这些模式的效果有所不同：

• 对于事务 table，启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES时，数据更改语句中的值无效或缺失会发生错误。该语句被中止并回滚。

• 对于非事务处理 table，如果在要插入或更新的第一行中出现错误值，则两种模式的行为都相同：语句中止且 table 保持不变。如果该语句插入或修改了多行，并且错误值出现在第二行或更高行中，则结果取决于启用了哪种严格模式：

• 对于STRICT_ALL_TABLES，MySQL 返回错误并忽略其余行。但是，由于已插入或更新了较早的行，因此结果是部分更新。为避免这种情况，请使用单行语句，该语句可以在不更改 table 的情况下中止。

  • 对于STRICT_TRANS_TABLES，MySQL 将无效值转换为该列的最接近有效值，并插入调整后的值。如果缺少值，MySQL 将为列数据类型插入隐式默认值。无论哪种情况，MySQL 都会生成警告而不是错误，并 continue 处理该语句。隐式默认值在第 11.6 节“数据类型默认值”中描述。

严格模式会影响按零，零日期和日期零的除法处理，如下所示：

• 严格模式影响除以零的处理，其中包括MOD(N,0)：

对于数据更改操作(INSERT，UPDATE)：

• 如果未启用严格模式，则零除将插入NULL并且不产生警告。

  • 如果启用了严格模式，则除非也给出IGNORE，否则除以零会产生错误。对于INSERT IGNORE和UPDATE IGNORE，除以零将插入NULL并产生警告。

对于SELECT，除以零将返回NULL。启用严格模式也会导致发出警告。

• 严格模式会影响服务器是否允许'0000-00-00'作为有效日期：

• 如果未启用严格模式，则允许'0000-00-00'并且插入不会产生警告。

  • 如果启用了严格模式，则除非给出IGNORE，否则不允许'0000-00-00'，并且插入操作会产生错误。对于INSERT IGNORE和UPDATE IGNORE，允许'0000-00-00'，并且插入会产生警告。

• 严格模式会影响服务器是否允许日期，其中年份部分为非零，而月份或日期部分为 0(诸如'2010-00-01'或'2010-01-00'之类的日期)：

• 如果未启用严格模式，则允许零部分的日期，并且插入不会产生警告。

  • 如果启用了严格模式，则除非包含IGNORE，否则不允许使用零份日期，并且插入会产生错误。对于INSERT IGNORE和UPDATE IGNORE，将零部分的日期作为'0000-00-00'插入(对于IGNORE视为有效)，并产生警告。

有关IGNORE的严格模式的更多信息，请参见IGNORE 关键字和严格 SQL 模式的比较。

在 MySQL 5.7.4 之前和 MySQL 5.7.8 及更高版本中，严格模式会影响与ERROR_FOR_DIVISION_BY_ZERO，NO_ZERO_DATE和NO_ZERO_IN_DATE模式结合使用零，零日期和日期零的除法处理。从 MySQL 5.7.4 到 5.7.7，显式命名时ERROR_FOR_DIVISION_BY_ZERO，NO_ZERO_DATE和NO_ZERO_IN_DATE模式不起作用，并且它们的效果包括在严格模式的效果中。有关其他讨论，请参见MySQL 5.7 中的 SQL 模式更改。

IGNORE 关键字和严格 SQL 模式的比较

本节比较了IGNORE关键字(将错误降级为警告)和严格的 SQL 模式(将警告升级为错误)对语句执行的影响。它描述了它们影响哪些语句以及它们适用于哪些错误。

下 table 总结了默认情况下是产生错误还是警告时语句行为的比较。默认情况下会产生错误的一个示例是将NULL插入NOT NULL列。默认情况下会发出警告的一个示例是将错误数据类型的值插入列中(例如，将字符串'abc'插入整数列中)。

Operational Mode	当语句默认为错误时	当“语句默认值”为“警告”时
没有IGNORE或严格的 SQL 模式	Error	Warning
与IGNORE	Warning	警告(与不使用IGNORE或严格的 SQL 模式相同)
使用严格的 SQL 模式	错误(与不使用IGNORE或严格的 SQL 模式相同)	Error
使用IGNORE和严格的 SQL 模式	Warning	Warning

从 table 中得出的一个结论是，当IGNORE关键字和严格 SQL 模式都有效时，IGNORE优先。这意味着，尽管IGNORE和严格的 SQL 模式可以被认为对错误处理有相反的影响，但是它们一起使用时不会取消。

IGNORE 对语句执行的影响

MySQL 中的一些语句支持可选的IGNORE关键字。此关键字使服务器降级某些类型的错误并生成警告。对于多行语句，IGNORE导致该语句跳至下一行而不是中止。

例如，如果 tablet具有主键列i，则尝试将i的相同值插入多行通常会产生重复键错误：

mysql> INSERT INTO t (i) VALUES(1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

对于IGNORE，仍然不会插入包含重复键的行，但是会出现警告而不是错误：

mysql> INSERT IGNORE INTO t (i) VALUES(1),(1);
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

这些语句支持IGNORE关键字：

• 创建 table...选择：IGNORE不适用于该语句的CREATE TABLE或SELECT部分，而是插入到SELECT所产生的行 table 中。与唯一键值上的现有行重复的行将被丢弃。

• DELETE：IGNORE导致 MySQL 在删除行的过程中忽略错误。

• INSERT：使用IGNORE，将丢弃在唯一键值上复制现有行的行。设置为会导致数据转换错误的值的行将设置为最接近的有效值。

对于未找到与给定值匹配的分区的分区 table，IGNORE导致对于包含不匹配值的行，插入操作将以静默方式失败。

• LOAD DATA，LOAD XML：使用IGNORE，将删除在唯一键值上复制现有行的行。

• UPDATE：使用IGNORE，不会更新在唯一键值上发生重复键冲突的行。更新为会导致数据转换错误的值的行将更新为最接近的有效值。

IGNORE关键字适用于以下错误：

ER_BAD_NULL_ERROR
ER_DUP_ENTRY
ER_DUP_ENTRY_WITH_KEY_NAME
ER_DUP_KEY
ER_NO_PARTITION_FOR_GIVEN_VALUE
ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT
ER_NO_REFERENCED_ROW_2
ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
ER_ROW_IS_REFERENCED_2
ER_SUBQUERY_NO_1_ROW
ER_VIEW_CHECK_FAILED

严格 SQL 模式对语句执行的影响

MySQL 服务器可以在不同的 SQL 模式下运行，并且可以根据sql_mode系统变量的值将这些模式不同地应用于不同的 Client 端。在“严格” SQL 模式下，服务器将某些警告升级为错误。

例如，在非严格 SQL 模式下，将字符串'abc'插入整数列会导致将值转换为 0 并显示警告：

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

mysql> INSERT INTO t (i) VALUES('abc');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'abc' for column 'i' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

在严格的 SQL 模式下，无效值会被拒绝，并显示以下错误：

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

mysql> INSERT INTO t (i) VALUES('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'i' at row 1

有关sql_mode系统变量的可能设置的更多信息，请参见第 5.1.10 节“服务器 SQL 模式”。

在某些值可能超出范围或将无效行插入 table 中或从 table 中删除的情况下，严格 SQL 模式适用于以下语句：

• ALTER TABLE

• CREATE TABLE

• 创建 table...选择

• DELETE(单 table 和多 table)

• INSERT

• LOAD DATA

• LOAD XML

• SELECT SLEEP()

• UPDATE(单 table 和多 table)

在存储的程序中，如果在严格模式有效时定义了程序，则刚刚列出的类型的单个语句将在严格 SQL 模式下执行。

严格的 SQL 模式适用于以下错误，代 table 一类错误，其中 Importing 值无效或缺失。如果该值的列的数据类型错误或可能超出范围，则该值无效。如果要插入的新行不包含其定义中没有明确的DEFAULT子句的NOT NULL列的值，则缺少值。

ER_BAD_NULL_ERROR
ER_CUT_VALUE_GROUP_CONCAT
ER_DATA_TOO_LONG
ER_DATETIME_FUNCTION_OVERFLOW
ER_DIVISION_BY_ZERO
ER_INVALID_ARGUMENT_FOR_LOGARITHM
ER_NO_DEFAULT_FOR_FIELD
ER_NO_DEFAULT_FOR_VIEW_FIELD
ER_TOO_LONG_KEY
ER_TRUNCATED_WRONG_VALUE
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
ER_WARN_DATA_OUT_OF_RANGE
ER_WARN_NULL_TO_NOTNULL
ER_WARN_TOO_FEW_RECORDS
ER_WRONG_ARGUMENTS
ER_WRONG_VALUE_FOR_TYPE
WARN_DATA_TRUNCATED

MySQL 5.7 中的 SQL 模式更改

在 MySQL 5.7.22 中，以下 SQL 模式已弃用，并将在 MySQL 的将来版本中删除：DB2，MAXDB，MSSQL，MYSQL323，MYSQL40，ORACLE，POSTGRESQL，NO_FIELD_OPTIONS，NO_KEY_OPTIONS，NO_TABLE_OPTIONS。

在 MySQL 5.7 中，默认情况下启用ONLY_FULL_GROUP_BY SQL 模式，因为GROUP BY处理已经变得更加复杂以包括对功能依赖项的检测。但是，如果您发现启用ONLY_FULL_GROUP_BY导致对现有应用程序的查询被拒绝，则以下任一操作都应恢复操作：

• 如果可以修改有问题的查询，请这样做，以使未聚合的列在功能上依赖于GROUP BY列，或者通过使用ANY_VALUE()引用未聚合的列。

• 如果无法修改有问题的查询(例如，如果它是由第三方应用程序生成的)，则在服务器启动时将sql_mode系统变量设置为不启用ONLY_FULL_GROUP_BY。

在 MySQL 5.7 中，不建议使用ERROR_FOR_DIVISION_BY_ZERO，NO_ZERO_DATE和NO_ZERO_IN_DATE SQL 模式。长期计划是将这三种模式包含在严格的 SQL 模式中，并在将来的 MySQL 版本中将它们作为显式模式删除。为了使 MySQL 5.7 与 MySQL 5.6 严格模式兼容，并为修改受影响的应用程序提供额外的时间，适用以下行为：

• ERROR_FOR_DIVISION_BY_ZERO，NO_ZERO_DATE和NO_ZERO_IN_DATE并非严格 SQL 模式的一部分，但应与严格模式一起使用。提醒一下，如果在未启用严格模式的情况下启用了它们，则会发生警告，反之亦然。

• 默认情况下启用ERROR_FOR_DIVISION_BY_ZERO，NO_ZERO_DATE和NO_ZERO_IN_DATE。

通过上述更改，默认情况下仍会启用更严格的数据检查，但是可以在当前需要或必须这样做的环境中禁用各个模式。