MySQL5.7服务器 SQL 模式

  官网地址：MySQL :: MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes

欢迎关注留言，我是收集整理小能手，工具翻译，仅供参考，笔芯笔芯.

MySQL 5.7 参考手册  /  ...  /  服务器 SQL 模式

5.1.10 服务器 SQL 模式

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

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

• 设置SQL模式

• 最重要的 SQL 模式

• SQL 模式的完整列表

• 组合 SQL 模式

• 严格SQL模式

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

• MySQL 5.7 中的 SQL 模式更改

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

使用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。

这些模式被添加到 MySQL 5.7 中的默认 SQL 模式中： MySQL 5.7.5 中添加了ONLY_FULL_GROUP_BY和 模式。STRICT_TRANS_TABLES该 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 模式，请 在命令行或 选项文件（例如（Unix 操作系统）或(Windows)）中使用该选项。 是用逗号分隔的不同模式的列表。要显式清除 SQL 模式，请在命令行或选项文件中 使用将其设置为空字符串 。--sql-mode="modes"sql-mode="modes"my.cnfmy.inimodes--sql-mode=""sql-mode=""

笔记

MySQL安装程序可能会在安装过程中配置SQL模式。如果 SQL 模式与默认值或您期望的不同，请检查服务器在启动时读取的选项文件中的设置。

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

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

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

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

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

重要的

SQL模式和用户定义的分区。  创建数据并将其插入分区表后更改服务器 SQL 模式可能会导致此类表的行为发生重大变化，并可能导致数据丢失或损坏。强烈建议您在创建使用用户定义分区的表后不要更改 SQL 模式。

复制分区表时，源和副本上不同的 SQL 模式也可能导致问题。为了获得最佳结果，您应该始终在源和副本上使用相同的服务器 SQL 模式。

有关更多信息，请参阅 第 22.6 节“分区的约束和限制”。

最重要的 SQL 模式

最重要的sql_mode 值可能是这些：

• ANSI

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

• STRICT_TRANS_TABLES

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

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

• TRADITIONAL

  使 MySQL 表现得像一个“传统” SQL 数据库系统。这种模式的简单描述是 当向列中插入不正确的值时“给出错误而不是警告” 。它是本节末尾列出的 特殊组合模式之一。

  笔记

  TRADITIONAL启用模式 后，一旦发生错误，INSERT或 就会 中止。UPDATE如果您使用的是非事务性存储引擎，这可能不是您想要的，因为错误之前所做的数据更改可能不会回滚，从而导致“部分完成”更新。

当本手册提到“严格模式”STRICT_TRANS_TABLES时，它是指启用其中一个或两个或 启用的模式 STRICT_ALL_TABLES。

SQL 模式的完整列表

以下列表描述了所有支持的 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 ，您不能使用双引号来引用文字字符串，因为它们被解释为标识符。

• ERROR_FOR_DIVISION_BY_ZERO

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

  • 如果未启用此模式，则除以零会插入 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被解析为NOT (a BETWEEN b AND c)。在某些旧版本的 MySQL 中，表达式被解析为(NOT a) BETWEEN b AND c. 旧的更高优先级行为可以通过启用 SQL 模式来获得 HIGH_NOT_PRECEDENCE。

  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作为表名会导致错误：

  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)

  SQLIGNORE_SPACE模式适用于内置函数，不适用于可加载函数或存储函数。IGNORE_SPACE无论是否启用 ，始终允许在可加载函数或存储函数名称后有空格 。

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

• 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将在 MySQL 的未来版本中删除，并且其效果将始终启用（并且 GRANT不再创建帐户）。

  以前，before NO_AUTO_CREATE_USER已被弃用，不启用它的原因之一是它不是复制安全的。CREATE USER IF NOT EXISTS现在可以使用、DROP USER IF EXISTS和ALTER USER IF EXISTS而不是 来 启用它并执行复制安全的用户管理GRANT。当副本可能具有与源上的授权不同的授权时，这些语句可以实现安全复制。请参见第 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或 来 生成该列的下一个序列号。 抑制此行为，以便仅生成下一个序列号。 0NO_AUTO_VALUE_ON_ZERO0NULL

  0如果已存储在表的列中 ，则此模式会很有用AUTO_INCREMENT 。（顺便说一句，存储0不是推荐的做法。）例如，如果使用mysqldump转储表，然后重新加载它，MySQL 通常会在遇到这些0值时生成新的序列号，从而导致表的内容与之前的表不同那被抛弃了。NO_AUTO_VALUE_ON_ZERO 在重新加载转储文件之前启用 可以解决此问题。因此，mysqldump自动在其输出中包含一条启用 NO_AUTO_VALUE_ON_ZERO.

• NO_BACKSLASH_ESCAPES

  启用此模式将禁用反斜杠字符 ( \) 作为字符串和标识符中的转义字符。启用此模式后，反斜杠将成为像其他字符一样的普通字符，并且 LIKE表达式的默认转义序列将更改，以便不使用转义字符。

• NO_DIR_IN_CREATE

  创建表时，忽略 allINDEX DIRECTORY和DATA DIRECTORY 指令。此选项在副本复制服务器上很有用。

• NO_ENGINE_SUBSTITUTION

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

  默认情况下， NO_ENGINE_SUBSTITUTION已启用。

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

  禁用后 NO_ENGINE_SUBSTITUTION ，将CREATE TABLE 使用默认引擎，如果所需的引擎不可用，则会出现警告。对于 ALTER TABLE，会出现警告并且表不会更改。

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

• NO_FIELD_OPTIONS

  不要在 的输出中打印 MySQL 特定的列选项 SHOW CREATE TABLE。该模式由mysqldump在可移植模式下使用。

  笔记

  从 MySQL 5.7.22 开始， NO_FIELD_OPTIONS已弃用。它在 MySQL 8.0 中被删除。

• NO_KEY_OPTIONS

  不要在 的输出中打印 MySQL 特定的索引选项 SHOW CREATE TABLE。该模式由mysqldump在可移植模式下使用。

  笔记

  从 MySQL 5.7.22 开始， NO_KEY_OPTIONS已弃用。它在 MySQL 8.0 中被删除。

• NO_TABLE_OPTIONS

  ENGINE不要在 的输出中 打印 MySQL 特定的表选项（例如 ） SHOW CREATE TABLE。该模式由mysqldump在可移植模式下使用。

  笔记

  从 MySQL 5.7.22 开始， NO_TABLE_OPTIONS已弃用。它在 MySQL 8.0 中被删除。

• NO_UNSIGNED_SUBTRACTION

  默认情况下，整数值之间的减法（其中 1 的类型为 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启用时，减法结果是有符号的，即使任何操作数是 unsigned。c2例如，比较table 中的 列类型与table 中的t1列类型 ： c2t2

  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 节“强制转换函数和运算符”。

• NO_ZERO_DATE

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

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

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

  • 如果启用此模式和严格模式， '0000-00-00'则不允许并且插入会产生错误，除非 IGNORE同时给出。对于 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

  拒绝选择列表、 HAVING条件或ORDER 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子句中引用选择列表中的别名表达式。在 MySQL 5.7.5 之前，启用会 ONLY_FULL_GROUP_BY 禁用此扩展，因此要求 HAVING使用非别名表达式编写该子句。从 MySQL 5.7.5 开始，此限制被取消，以便HAVING无论是否 ONLY_FULL_GROUP_BY启用该子句都可以引用别名。

  有关其他讨论和示例，请参阅 第 12.19.3 节，“MySQL 处理 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的效果 。有关更多讨论，请参阅 MySQL 5.7 中的 SQL 模式更改。 ERROR_FOR_DIVISION_BY_ZERONO_ZERO_DATENO_ZERO_IN_DATE

• STRICT_TRANS_TABLES

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

  从 MySQL 5.7.4 到 5.7.7， 包括、 、 和 模式STRICT_TRANS_TABLES 的效果 。有关更多讨论，请参阅 MySQL 5.7 中的 SQL 模式更改。 ERROR_FOR_DIVISION_BY_ZERONO_ZERO_DATENO_ZERO_IN_DATE

组合 SQL 模式

提供以下特殊模式作为前面列表中模式值组合的简写。

• ANSI

  相当于 REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, 和 （从 MySQL 5.7.5 开始） ONLY_FULL_GROUP_BY。

  ANSIS如果具有外部引用的 集合函数 无法在已解析外部引用的外部查询中聚合，模式还会导致服务器返回查询错误 。这是这样一个查询： S(outer_ref)

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

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

  请参阅第 1.6 节“MySQL 标准合规性”。

• DB2

  相当于 PIPES_AS_CONCAT， ANSI_QUOTES， IGNORE_SPACE， NO_KEY_OPTIONS， NO_TABLE_OPTIONS， NO_FIELD_OPTIONS。

  笔记

  从 MySQL 5.7.22 开始，DB2 已弃用。它在 MySQL 8.0 中被删除。

• MAXDB

  相当于 PIPES_AS_CONCAT， ANSI_QUOTES， IGNORE_SPACE， NO_KEY_OPTIONS， NO_TABLE_OPTIONS， NO_FIELD_OPTIONS， NO_AUTO_CREATE_USER。

  笔记

  从 MySQL 5.7.22 开始， MAXDB已弃用。它在 MySQL 8.0 中被删除。

• MSSQL

  相当于 PIPES_AS_CONCAT， ANSI_QUOTES， IGNORE_SPACE， NO_KEY_OPTIONS， NO_TABLE_OPTIONS， NO_FIELD_OPTIONS。

  笔记

  从 MySQL 5.7.22 开始， MSSQL已弃用。它在 MySQL 8.0 中被删除。

• MYSQL323

  相当于MYSQL323， HIGH_NOT_PRECEDENCE。这意味着HIGH_NOT_PRECEDENCE 加上一些SHOW CREATE TABLE 特定于的行为 MYSQL323：

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

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

  • 表选项 显示为 。 ENGINE=engine_nameTYPE=engine_name

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

  笔记

  从 MySQL 5.7.22 开始， MYSQL323已弃用。它在 MySQL 8.0 中被删除。

• MYSQL40

  相当于MYSQL40， HIGH_NOT_PRECEDENCE。这意味着HIGH_NOT_PRECEDENCE 加上一些特定于 的行为 MYSQL40。这些与 for 相同MYSQL323，只是SHOW CREATE TABLE 不显示HEAP为MEMORY表的存储引擎。

  笔记

  从 MySQL 5.7.22 开始， MYSQL40已弃用。它在 MySQL 8.0 中被删除。

• ORACLE

  相当于 PIPES_AS_CONCAT， ANSI_QUOTES， IGNORE_SPACE， NO_KEY_OPTIONS， NO_TABLE_OPTIONS， NO_FIELD_OPTIONS， NO_AUTO_CREATE_USER。

  笔记

  从 MySQL 5.7.22 开始， ORACLE已弃用。它在 MySQL 8.0 中被删除。

• POSTGRESQL

  相当于 PIPES_AS_CONCAT， ANSI_QUOTES， IGNORE_SPACE， NO_KEY_OPTIONS， NO_TABLE_OPTIONS， NO_FIELD_OPTIONS。

  笔记

  从 MySQL 5.7.22 开始， POSTGRESQL已弃用。它在 MySQL 8.0 中被删除。

• 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版本 中，它们的效果包含在严格 SQL 模式（或 ）的效果中。因此，在所有 MySQL 5.7 版本中的效果 都是相同的（与 MySQL 5.6 中相同）。有关更多讨论，请参阅 MySQL 5.7 中的 SQL 模式更改。 NO_ZERO_DATEERROR_FOR_DIVISION_BY_ZEROSTRICT_ALL_TABLESSTRICT_TRANS_TABLESTRADITIONAL

严格SQL模式

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

如果严格模式未生效，MySQL 会插入无效或缺失值的调整值并产生警告（请参见 第 13.7.5.40 节“SHOW WARNINGS 语句”）。INSERT IGNORE 在严格模式下，您可以通过使用或来产生此行为 UPDATE IGNORE。

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

如果尝试创建超过最大密钥长度的密钥，严格模式会产生错误。当未启用严格模式时，这会导致警告并将密钥截断为最大密钥长度。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • 如果启用了严格模式，则 '0000-00-00'不允许并且插入会产生错误，除非 IGNORE也给出了。对于 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 模式（将警告升级为错误） 对语句执行的影响 。它描述了它们影响哪些语句以及它们适用于哪些错误。

下表总结了默认情况下生成错误与警告时语句行为的比较。默认情况下会产生错误的一个示例是将 a 插入NULL到NOT NULL列中。默认情况下产生警告的一个示例是将错误数据类型的值插入到列中（例如将字符串插入 'abc'到整数列中）。

运作模式	当语句默认为错误时	当语句默认为警告时
无IGNORE或严格 SQL 模式	错误	警告
和IGNORE	警告	IGNORE警告（与无或严格 SQL 模式相同）
具有严格的SQL模式	IGNORE错误（与无或严格 SQL 模式相同）	错误
withIGNORE和严格的 SQL 模式	警告	警告

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

• IGNORE 对语句执行的影响

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

IGNORE 对语句执行的影响

MySQL 中的一些语句支持可选 IGNORE关键字。此关键字会导致服务器降级某些类型的错误并生成警告。对于多行语句，将错误降级为警告可能会使行得到处理。否则， IGNORE导致语句跳到下一行而不是中止。（对于不可忽略的错误，无论关键字如何，都会发生错误IGNORE。）

示例：如果表t的主键列i包含唯一值，则尝试将相同的值插入i多行通常会产生重复键错误：

mysql> CREATE TABLE t (i INT NOT NULL PRIMARY KEY);
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: 1mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

示例：如果表t2有一NOT NULL列id，则尝试插入NULL会在严格 SQL 模式下产生错误：

mysql> CREATE TABLE t2 (id INT NOT NULL);
mysql> INSERT INTO t2 (id) VALUES(1),(NULL),(3);
ERROR 1048 (23000): Column 'id' cannot be null
mysql> SELECT * FROM t2;
Empty set (0.00 sec)

如果 SQL 模式不严格，IGNORE则会导致NULL被插入为列隐式默认值（在本例中为 0），这使得能够在不跳过该行的情况下处理该行：

mysql> INSERT INTO t2 (id) VALUES(1),(NULL),(3);
mysql> SELECT * FROM t2;
+----+
| id |
+----+
|  1 |
|  0 |
|  3 |
+----+

这些语句支持IGNORE关键字：

• CREATE TABLE ... SELECT：IGNORE不适用于语句的CREATE TABLE或 SELECT部分，但适用于插入到由 生成的行表中 SELECT。与唯一键值重复现有行的行将被丢弃。

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

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

  对于未找到与给定值匹配的分区的分区表，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 。在“严格” 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 模式”。

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

• ALTER TABLE

• CREATE TABLE

• CREATE TABLE ... SELECT

• DELETE（单表和多表均可）

• INSERT

• LOAD DATA

• LOAD XML

• SELECT SLEEP()

• UPDATE（单表和多表均可）

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

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

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 的持续开发定义了新的错误，因此可能存在不属于上述严格 SQL 模式适用的错误。

MySQL 5.7 中的 SQL 模式更改

在 MySQL 5.7.22 中，这些 SQL 模式已被弃用并在 MySQL 8.0 中被删除：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在服务器启动时将系统变量设置为 not enable ONLY_FULL_GROUP_BY。

在 MySQL 5.7 中， 不推荐使用 、 和 SQLERROR_FOR_DIVISION_BY_ZERO模式 NO_ZERO_DATE。 NO_ZERO_IN_DATE长期计划是将这三种模式包含在严格 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默认情况下启用。

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