5.1.11 服务器 SQL 模式

2022-11-03 08:12:46

文章目录


官方文档地址： 5.1.11 Server SQL Modes

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

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

关于 MySQL 中服务器 SQL 模式的常见问题的答案，参见 A.3 MySQL 8.0 FAQ: SQL Mode。

在处理InnoDB表时，还要考虑innodb_strict_mode系统变量。它支持对InnoDB表进行额外的错误检查。

设置 SQL 模式

MySQL 8.0 默认的 SQL 模式包括以下模式：

  • ONLY_FULL_GROUP_BY
  • STRICT_TRANS_TABLES
  • NO_ZERO_IN_DATE
  • NO_ZERO_DATE
  • ERROR_FOR_DIVISION_BY_ZERO
  • NO_ENGINE_SUBSTITUTION

要在服务器启动时设置 SQL 模式，在命令行上使用--sql-mode="modes"选项，或者在一个选项文件中比如my.cnf（Unix 操作系统）或者my.ini（Windows）中添加sql-mode="modes"。modes是用逗号分隔的不同模式列表。要显式清除 SQL 模式，在命令行中使用--sql-mode=""或在选项文件中使用sql-mode=""将其设置为空字符串。

注意

MySQL 安装程序可能会在安装过程中配置 SQL 模式。

如果 SQL 模式不同于默认模式或与您预期的不同，请检查服务器在启动时读取的选项文件中的设置。

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

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

设置全局变量需要SYSTEM_VARIABLES_ADMIN权限（或已弃用的SUPER权限），并影响从那时起连接的所有客户机的操作。设置SESSION变量只影响当前客户端。每个客户机都可以在任何时候更改其会话sql_mode值。

要确定当前全局或会话sql_mode设置，查看设置：

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

重要提示

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

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

有关更多信息，请参见 24.6 Restrictions and Limitations on Partitioning。

最重要的 SQL 模式

最重要的sql_mode值可能如下所示：

  • ANSI
    这种模式改变语法和行为，使之更符合标准 SQL。它是本节末尾列出的特殊组合模式之一。
  • STRICT_TRANS_TABLES
    如果不能按照给定的方式将值插入事务表中，则中止语句。对于非事务性表，如果该值出现在单行语句或多行语句的第一行中，则中止该语句。本节稍后将给出更多细节。
  • TRADITIONAL
    使 MySQL 表现得像一个“传统的” SQL 数据库系统。对这种模式的简单描述是，当在列中插入不正确的值时，“给出错误而不是警告”。它是本节末尾列出的特殊组合模式之一。

注意

在启用TRADITIONAL模式的情况下，一旦出现错误，插入或更新就会终止。如果您使用的是非事务性存储引擎，这可能不是您想要的，因为在错误之前所做的数据更改可能不会回滚，将会导致“部分完成”更新。

当本手册提到“严格模式”时，它指的是启用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模式影响对除零的处理，其中包括MOD(N, 0)。于数据更改操作（插入、更新），其效果也取决于是否启用了严格 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而不启用严格模式，则会出现警告，反之亦然。

由于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。旧的高优先级行为可以通过启用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作为表名会导致错误：

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)

IGNORE_SPACE SQL 模式适用于内置函数，不适用用户定义函数或存储函数。无论是否启用IGNORE_SPACE，都允许在用户定义函数（UDF）或存储函数名后面有空格。

关于IGNORE_SPACE的进一步讨论，请参见 9.2.5 函数名称解析。

NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO影响对AUTO_INCREMENT（自增）列的处理。通常，通过向列中插入NULL或0来生成列的下一个序列号。NO_AUTO_VALUE_ON_ZERO抑制0的这种行为，以便只有NULL才能生成下一个序列号。

如果表的AUTO_INCREMENT列中存储了0，那么这种模式很有用。（顺便说一下，不推荐存储0。）例如，如果您使用mysqldump转储表，然后重新加载它，MySQL 通常会在遇到0值时生成新的序列号，会导致产生一个与转储的表内容不同的表。在重新加载转储文件之前启用NO_AUTO_VALUE_ON_ZERO可以解决这个问题。由于这个原因，mysqldump自动在其输出中包含一条启用NO_AUTO_VALUE_ON_ZERO的语句。

NO_BACKSLASH_ESCAPES

禁止在字符串和标识符中使用反斜杠字符（\）作为转义字符。在此模式下，反斜杠将成为普通字符。

NO_DIR_IN_CREATE

创建表时，忽略所有的INDEX DIRECTORY和DATA DIRECTORY指令。这个选项在副本服务器上很有用。

NO_ENGINE_SUBSTITUTION

当CREATE TABLE或ALTER TABLE等语句指定禁用或未在其中编译的存储引擎时，会自动替换为默认存储引擎。

缺省情况下，启用NO_ENGINE_SUBSTITUTION。

因为存储引擎在运行时是可插拔的，所以不可用的引擎也以同样的方式处理：

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

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

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时，减法结果是有符号的，即使任何操作数都是无符号的。例如，比较表t1中列c2的类型与表t2中列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.11 强制转换函数和操作符。

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而没有启用严格模式，则会出现警告，反之亦然。

由于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而没有启用严格模式，则会出现警告，反之亦然。

因为NO_ZERO_IN_DATE已经弃用，所以在未来的 MySQL 版本中，应该会将其作为一个单独的模式名删除，它的效果包括在严格的 SQL 模式的效果中。

ONLY_FULL_GROUP_BY

拒绝SELECT 列表、HAVING 条件或ORDER BY 列表引用非聚合列的查询，这些列既没有在GROUP BY子句中命名，在函数上也不依赖于GROUP BY列。

标准 SQL 的 MySQL 扩展允许在HAVING子句中引用查询列表中的别名表达式。无论ONLY_FULL_GROUP_BY是否启用，HAVING子句都可以引用别名。

为了获得更多的讨论和示例，参见 12.20.3 MySQL 处理 GROUP BY。

PAD_CHAR_TO_FULL_LENGTH

默认情况下，检索时从CHAR列值裁剪尾随空格。如果启用了PAD_CHAR_TO_FULL_LENGTH，则不会进行微调，会将检索到的CHAR值填充到其完整长度。此模式不适用于VARCHAR列，检索时将保留末尾空格。

注意

从 MySQL 8.0.13 开始，PAD_CHAR_TO_FULL_LENGTH已经被弃用。希望它在未来的 MySQL 版本中被删除。

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 模式。

STRICT_TRANS_TABLES

为事务性存储引擎启用严格的 SQL 模式，如果可能，也为非事务性存储引擎启用严格的 SQL 模式。详细信息请参见严格 SQL 模式。

TIME_TRUNCATE_FRACTIONAL

控制在向具有相同类型但小数位数更少的列插入带有小数秒部分的TIME、DATE或TIMESTAMP值时是否进行舍入或截断。默认行为是使用舍入。如果启用此模式，则会发生截断。下面的语句序列说明了区别：

CREATE TABLE t (id INT, tval TIME(1));
SET sql_mode='';
INSERT INTO t (id, tval) VALUES(1, 1.55);
SET sql_mode='TIME_TRUNCATE_FRACTIONAL';
INSERT INTO t (id, tval) VALUES(2, 1.55);

结果表内容如下所示，第一个值被舍入，第二个值被截断：

mysql> SELECT id, tval FROM t ORDER BY id;
+------+------------+
| id   | tval       |
+------+------------+
|    1 | 00:00:01.6 |
|    2 | 00:00:01.5 |
+------+------------+

参见 11.2.6 时间值的小数秒。

组合 SQL 模式

下面的特殊模式是对前面列表中模式值的组合的简写。

ANSI

相当于REAL_AS_FLOAT，PIPES_AS_CONCAT，ANSI_QUOTES， IGNORE_SPACE和ONLY_FULL_GROUP_BY。

ANSI模式还会导致服务器返回一个查询错误，其中带有外部引用的函数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.7 MySQL 标准遵循。

TRADITIONAL

TRADITIONAL等价于STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO和NO_ENGINE_SUBSTITUTION。

严格 SQL 模式

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

如果严格模式不生效，MySQL 会为无效或缺失的值插入调整后的值，并产生警告（13.7.7.42 SHOW WARNINGS 声明），在严格模式下，您可以通过使用INSERT IGNORE或UPDATE IGNORE来产生这种行为。

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

当尝试创建超过最大 key 长度的 key 时，严格模式会产生错误。如果没有启用严格模式，则会出现警告并将键截断为最大 key 长度。

严格模式不影响是否检查外键约束。foreign_key_checks可以用于此。(5.1.8 服务器系统变量)。

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

  • 对于事务性表，在启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES时，数据更改语句中出现无效或缺失值的错误。语句被中止并回滚。
  • 对于非事务性表，如果坏值出现在要插入或更新的第一行，那么这两种模式的行为是相同的：语句被中止，表保持不变。

    如果语句插入或修改了多行，而坏值出现在第二行或后面的行，结果取决于启用哪种严格模式：
    对于STRICT_ALL_TABLES，MySQL 返回一个错误并忽略其余的行。但是，由于前面的行已经被插入或更新，所以结果是部分更新。为了避免这种情况，可以使用单行语句，这可以在不更改表的情况下中止。
    对于STRICT_TRANS_TABLES，MySQL 将一个无效的值转换为列最近的有效值，然后插入调整后的值。如果一个值缺失，MySQL 会为列数据类型插入隐式的默认值。无论哪种情况，MySQL 都会生成警告而不是错误，并继续处理语句。隐式默认值在 11.6 数据类型默认值 中描述。

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

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

    对于数据更改操作（插入、更新）：
    如果没有启用严格模式，则除零将插入NULL并不会产生警告。
    如果启用了严格模式，除零将产生错误，除非同时给出了IGNORE。对于INSERT IGNORE和UPDATE IGNORE，除以零会插入NULL并产生警告。

    对于查询操作，除零返回NULL。启用严格模式也会产生警告。

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

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

    如果启用严格模式，'0000-00-00'是不允许的，插入会产生错误，除非同时给出了IGNORE。对于INSERT IGNORE和UPDATE IGNORE，'0000-00-00'是允许的，插入会产生警告。

  • 严格模式影响服务器是否允许年份部分为非0，月或日部分为0的日期（例如'2010-00-01'或'2010-01-00'）：

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

    如果启用了严格模式，则不允许日期包含零部分，插入会产生错误，除非还给出了IGNORE。对于INSERT IGNORE和UPDATE IGNORE，包含零部分的日期被插入为'0000-00-00'（这在IGNORE中被认为是有效的）并产生警告。

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

严格模式结合ERROR_FOR_DIVISION_BY_ZERO、NO_ZERO_DATE和NO_ZERO_IN_DATE模式对除零、零日期和日期中的零的处理会有影响。

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

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

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

运作模式 当语句默认为错误 当语句默认为警告
没有INGORE或严格 SQL 模式 Error Warning
INGORE Warning Warning
使用严格 SQL 模式 Error Error
同时使用IGNORE和严格 SQL模式 Warning Warning

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

IGNORE 对语句执行的影响

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

例如：如果表t有一个主键列i，尝试在多行中插入相同的i值通常会产生重复键错误：

mysql> INSERT INTO t (i) VALUES(1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 't.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 't.PRIMARY' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

下面这些语句支持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.8 Server System Variables。

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

  • ALTER TABLE
  • CREATE TABLE
  • CREATE TABLE … SELECT
  • DELETE（包括单表和多表）
  • INSERT
  • LOAD DATA
  • LOAD XML
  • SELECT SLEEP()
  • UPDATE（包括单表和多表）

在存储的程序中，如果程序是在严格模式生效的情况下定义的，那么上面列出的类型的单个语句将以严格 SQL 模式执行。

严格 SQL 模式适用于以下错误，这些错误表示输入值无效或缺失的一类错误。如果值的数据类型与列的数据类型错误，或可能超出范围，则该值无效。对于没有显式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 模式适用的错误可能不在前面的列表中。

上一篇：理解javascript中的原型模式


下一篇：PPT扁平化设计总结