ANSI

此模式更改语法和行为以更接近标准 SQL。它是本节末尾列出的特殊 组合模式 之一。This mode changes syntax and behavior to conform more closely to standard SQL. It is one of the special combination modes listed at the end of this section.

STRICT_TRANS_TABLES

如果一个值无法按给定方式插入到事务表中，则中止语句。对于非事务表，如果值出现在单行语句中或多行语句的第一行，则中止语句。本节后面将提供更多详细信息。If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More details are given later in this section.

TRADITIONAL

让 MySQL 表现得像一个 “传统” 的 SQL 数据库系统。对此模式的简单描述是 “在向列插入错误值时发出错误而不是警告”。它是本节结尾列出的 组合模式 之一。Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column. It is one of the special combination modes listed at the end of this section.

ALLOW_INVALID_DATES ALLOW_INVALID_DATES

不执行完整的日期检查。只检查月份是否在 1 到 12 之间，日期是否在 1 到 31 之间。这对于从三个不同字段获取年、月、日并且存储用户输入内容而不进行日期验证的 Web 应用可能很有用。此模式适用于DATE和DATETIME列。不适用于TIMESTAMP列，TIMESTAMP 列始终需要有效日期。Do not perform full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. This may be useful for Web applications that obtain year, month, and day in three different fields and store exactly what the user inserted, without date validation. This mode applies to DATE and DATETIME columns. It does not apply to TIMESTAMP columns, which always require a valid date.

当ALLOW_INVALID_DATES被禁用时，服务器要求月份和日期值必须合法，而不仅仅是分别在 1 到 12 和 1 到 31 之间。禁用严格模式时，无效日期例如'2004-04-31'会被转换为'0000-00-00'并生成警告。启用严格模式时，无效日期会生成错误。要允许这种日期，请启用ALLOW_INVALID_DATES。With ALLOW_INVALID_DATES disabled, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES.

ANSI_QUOTES

将 " 视为标识符引用字符（类似 ` 引号字符），而不是字符串引号字符。启用此模式后，仍然可以使用 ` 来引用标识符。启用 ANSI_QUOTES 后，不能使用双引号来引用字符串字面值，因为它们会被解释为标识符。Treat " as an identifier quote character (like the ` quote character) and not as a string quote character. You can still use ` to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings because they are interpreted as identifiers.

ERROR_FOR_DIVISION_BY_ZERO ERROR_FOR_DIVISION_BY_ZERO

ERROR_FOR_DIVISION_BY_ZERO 模式影响除以零的处理，包括 MOD(N,0)。对于数据更改操作（INSERT，UPDATE），其效果也取决于是否启用了严格 SQL 模式。The ERROR_FOR_DIVISION_BY_ZERO mode affects handling of division by zero, which includes MOD(N,0). For data-change operations (INSERT, UPDATE), its effect also depends on whether strict SQL mode is enabled.

对于SELECT，除以零会返回NULL。启用ERROR_FOR_DIVISION_BY_ZERO同样会产生警告，无论是否启用严格模式。For SELECT, division by zero returns NULL. Enabling ERROR_FOR_DIVISION_BY_ZERO causes a warning to be produced as well, regardless of whether strict mode is enabled.

ERROR_FOR_DIVISION_BY_ZERO 已被弃用。ERROR_FOR_DIVISION_BY_ZERO 不是严格模式的一部分，但应与严格模式一起使用，并且默认启用。如果启用了 ERROR_FOR_DIVISION_BY_ZERO 而没有同时启用严格模式，或者反之，则会发生警告。ERROR_FOR_DIVISION_BY_ZERO is deprecated. ERROR_FOR_DIVISION_BY_ZERO is not part of strict mode, but should be used in conjunction with strict mode and is enabled by default. A warning occurs if ERROR_FOR_DIVISION_BY_ZERO is enabled without also enabling strict mode or vice versa.

由于 ERROR_FOR_DIVISION_BY_ZERO 已被弃用，您应预期在未来的 MySQL 版本中，它将作为单独的模式名称被移除，其效果将包含在严格 SQL 模式的效果中。Because ERROR_FOR_DIVISION_BY_ZERO is deprecated, you should expect it to be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

HIGH_NOT_PRECEDENCE 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 模式，可以获得旧的高优先级行为。The precedence of the NOT operator is such that expressions such as NOT a BETWEEN b AND c are parsed as NOT (a BETWEEN b AND c). In some older versions of MySQL, the expression was parsed as (NOT a) BETWEEN b AND c. The old higher-precedence behavior can be obtained by enabling the HIGH_NOT_PRECEDENCE SQL mode.

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 IGNORE_SPACE

允许在函数名和 ( 字符之间留空格。这会导致内置函数名被视为保留字。因此，与函数名相同的标识符必须按照 第 11.2 节，“模式对象名称” 中的说明加引号。例如，由于存在 COUNT() 函数，在下列语句中将 count 用作表名会导致错误：Permit spaces between a function name and the ( character. This causes built-in function names to be treated as reserved words. As a result, identifiers that are the same as function names must be quoted as described in Section 11.2, “Schema Object Names”. For example, because there is a COUNT() function, the use of count as a table name in the following statement causes an error:

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

表名应该加引号：The table name should be quoted:

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

IGNORE_SPACE SQL 模式适用于内置函数，而不适用于可加载函数或存储函数。无论是否启用了 IGNORE_SPACE，在可加载函数或存储函数名称后都有空格始终是允许的。The IGNORE_SPACE SQL mode applies to built-in functions, not to loadable functions or stored functions. It is always permissible to have spaces after a loadable function or stored function name, regardless of whether IGNORE_SPACE is enabled.

有关 IGNORE_SPACE 的进一步讨论，请参见 第 11.2.5 节，“函数名称解析和解析”。For further discussion of IGNORE_SPACE, see Section 11.2.5, “Function Name Parsing and Resolution”.

NO_AUTO_VALUE_ON_ZERO NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO 会影响 AUTO_INCREMENT 列的处理方式。通常，你可以通过向该列插入 NULL 或 0 来生成下一个序列号。NO_AUTO_VALUE_ON_ZERO 会抑制 0 的这种行为，使得只有 NULL 才会生成下一个序列号。NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.

如果在表的 AUTO_INCREMENT 列中存储了 0，这种模式可能会很有用。（顺便说一下，存储 0 并不是推荐的做法。）例如，如果你使用 mysqldump 导出表，然后再重新加载它，当 MySQL 遇到 0 值时通常会生成新的序列号，从而导致表的内容与导出的内容不同。在重新加载转储文件之前启用 NO_AUTO_VALUE_ON_ZERO 可以解决此问题。因此，mysqldump 会自动在其输出中包含一条启用 NO_AUTO_VALUE_ON_ZERO 的语句。This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.) For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. For this reason, mysqldump automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO.

NO_BACKSLASH_ESCAPES

启用此模式将禁用在字符串和标识符中使用反斜杠字符（\）作为转义字符。在启用此模式后，反斜杠将变为普通字符，与其他字符一样，并且 LIKE 表达式的默认转义序列将被更改为不使用任何转义字符。Enabling this mode disables the use of the backslash character (\) as an escape character within strings and identifiers. With this mode enabled, backslash becomes an ordinary character like any other, and the default escape sequence for LIKE expressions is changed so that no escape character is used.

NO_DIR_IN_CREATE

在创建表时，忽略所有 INDEX DIRECTORY 和 DATA DIRECTORY 指令。此选项在从属服务器上非常有用。When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives. This option is useful on replica servers.

NO_ENGINE_SUBSTITUTION

当语句如 CREATE TABLE 或 ALTER TABLE 指定的存储引擎被禁用或未编译时，控制是否自动替换为默认存储引擎。Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.

默认情况下，NO_ENGINE_SUBSTITUTION 是启用的。By default, NO_ENGINE_SUBSTITUTION is enabled.

由于存储引擎可以在运行时插拔，不可用的引擎也以同样的方式处理：Because storage engines can be pluggable at runtime, unavailable engines are treated the same way:

当NO_ENGINE_SUBSTITUTION被禁用时，对于CREATE TABLE，将使用默认存储引擎，如果所需存储引擎不可用，将出现警告。对于ALTER TABLE，将出现警告且表不会被修改。With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the default engine is used and a warning occurs if the desired engine is unavailable. For ALTER TABLE, a warning occurs and the table is not altered.

当NO_ENGINE_SUBSTITUTION被启用时，如果所需存储引擎不可用，将发生错误且表不会被创建或修改。With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable.

NO_UNSIGNED_SUBTRACTION

整数值之间的减法，如果其中一个是 UNSIGNED 类型，默认会产生无符号结果。如果结果本来会是负数，则会产生错误：Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default. If the result would otherwise have been negative, an error results:

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 模式，结果将为负数：If the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative:

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 模式时，会发生错误，列保持不变。If the result of such an operation is used to update an UNSIGNED integer column, the result is clipped to the maximum value for the column type, or clipped to 0 if NO_UNSIGNED_SUBTRACTION is enabled. With strict SQL mode enabled, an error occurs and the column remains unchanged.

当NO_UNSIGNED_SUBTRACTION 启用时，减法结果为有符号，即使任何运算数是无符号的。例如，比对表 t1 中列 c2 与表 t2 中列 c2 的类型：When NO_UNSIGNED_SUBTRACTION is enabled, the subtraction result is signed, even if any operand is unsigned. For example, compare the type of column c2 in table t1 with that of column c2 in table t2:

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%使用。参见第 14.10 节，“类型转换函数和操作符”。This means that BIGINT UNSIGNED is not 100% usable in all contexts. See Section 14.10, “Cast Functions and Operators”.

NO_ZERO_DATE

NO_ZERO_DATE 模式影响服务器是否允许 '0000-00-00' 作为有效日期。其影响还取决于是否启用了严格 SQL 模式。The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date. Its effect also depends on whether strict SQL mode is enabled.

NO_ZERO_DATE 已被弃用。NO_ZERO_DATE 不属于严格模式的一部分，但应与严格模式一起使用，并且默认启用。如果启用了 NO_ZERO_DATE 而没有同时启用严格模式，或反之，则会出现警告。NO_ZERO_DATE is deprecated. NO_ZERO_DATE is not part of strict mode, but should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_DATE is enabled without also enabling strict mode or vice versa.

由于 NO_ZERO_DATE 已被弃用，因此您应预期它将在未来的 MySQL 版本中作为单独的模式名称被移除，其效果将包含在严格 SQL 模式的效果中。Because NO_ZERO_DATE is deprecated, you should expect it to be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

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 模式。The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0. (This mode affects dates such as '2010-00-01' or '2010-01-00', but not '0000-00-00'. To control whether the server permits '0000-00-00', use the NO_ZERO_DATE mode.) The effect of NO_ZERO_IN_DATE also depends on whether strict SQL mode is enabled.

NO_ZERO_IN_DATE 已被弃用。NO_ZERO_IN_DATE 并不是严格模式的一部分，但应与严格模式一起使用，并且默认启用。如果启用 NO_ZERO_IN_DATE 而未启用严格模式，或者反之，则会出现警告。NO_ZERO_IN_DATE is deprecated. NO_ZERO_IN_DATE is not part of strict mode, but should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_IN_DATE is enabled without also enabling strict mode or vice versa.

因为 NO_ZERO_IN_DATE 已被弃用，所以你应预期它将在未来的 MySQL 版本中作为单独模式名称被移除，其效果将包含在严格 SQL 模式的效果中。Because NO_ZERO_IN_DATE is deprecated, you should expect it to be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

ONLY_FULL_GROUP_BY

条件或HAVING列表引用既未在ORDER BY子句中命名，也不在GROUP BY列的函数依赖关系（由其唯一确定的）中GROUP BY的非聚合列。Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

MySQL 对标准 SQL 的扩展允许在 HAVING 子句中引用选择列表中的别名表达式。无论是否启用了 ONLY_FULL_GROUP_BY，HAVING 子句都可以引用别名。A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. The HAVING clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY is enabled.

有关更多讨论和示例，请参见 第 14.19.3 节，“MySQL 对 GROUP BY 的处理”。For additional discussion and examples, see Section 14.19.3, “MySQL Handling of GROUP BY”.

PAD_CHAR_TO_FULL_LENGTH

默认情况下，检索 CHAR 列值时，会去掉尾随空格。如果启用了 PAD_CHAR_TO_FULL_LENGTH，则不会进行去掉操作，检索到的 CHAR 值会填充到其完整长度。此模式不适用于 VARCHAR 列，后者在检索时会保留尾随空格。By default, trailing spaces are trimmed from CHAR column values on retrieval. If PAD_CHAR_TO_FULL_LENGTH is enabled, trimming does not occur and retrieved CHAR values are padded to their full length. This mode does not apply to VARCHAR columns, for which trailing spaces are retained on retrieval.

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 的同义词。Treat || as a string concatenation operator (same as CONCAT()) rather than as a synonym for OR.

REAL_AS_FLOAT

将 REAL 视为 FLOAT 的同义词。默认情况下，MySQL 将 REAL 视为 DOUBLE 的同义词。Treat REAL as a synonym for FLOAT. By default, MySQL treats REAL as a synonym for DOUBLE.

STRICT_ALL_TABLES

为所有存储引擎启用严格的 SQL 模式。无效的数据值将被拒绝。详情请参见 严格 SQL 模式。Enable strict SQL mode for all storage engines. Invalid data values are rejected. For details, see Strict SQL Mode.

STRICT_TRANS_TABLES

为事务存储引擎启用严格的 SQL 模式，并在可能的情况下为非事务存储引擎启用。详情请参见 严格 SQL 模式。Enable strict SQL mode for transactional storage engines, and when possible for nontransactional storage engines. For details, see Strict SQL Mode.

TIME_TRUNCATE_FRACTIONAL

控制在将带有小数秒部分的TIME、DATE或TIMESTAMP值插入到具有相同类型但小数位数较少的列时是进行四舍五入还是截断。默认行为是使用四舍五入。如果启用此模式，则改为进行截断。以下语句序列说明了两者的区别：Control whether rounding or truncation occurs when inserting a TIME, DATE, or TIMESTAMP value with a fractional seconds part into a column having the same type but fewer fractional digits. The default behavior is to use rounding. If this mode is enabled, truncation occurs instead. The following sequence of statements illustrates the difference:

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);

生成的表格内容如下，其中第一个值已进行四舍五入，第二个值已进行截断：The resulting table contents look like this, where the first value has been subject to rounding and the second to truncation:

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

另请参见 第 13.2.6 节，“时间值中的分数秒”。See also Section 13.2.6, “Fractional Seconds in Time Values”.

ANSI

相当于 REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE 以及 ONLY_FULL_GROUP_BY。Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and ONLY_FULL_GROUP_BY.

ANSI 模式还会导致服务器在以下情况下返回错误：当一个集合函数 S 与一个外部引用 S(outer_ref) 在外部查询中无法进行聚合时。这样的查询如下：ANSI mode also causes the server to return an error for queries where a set function S with an outer reference S(outer_ref) cannot be aggregated in the outer query against which the outer reference has been resolved. This is such a query:

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) 时的方式相同。Here, MAX(t1.b) cannot aggregated in the outer query because it appears in the WHERE clause of that query. Standard SQL requires an error in this situation. If ANSI mode is not enabled, the server treats S(outer_ref) in such queries the same way that it would interpret S(const).

参见 第 1.7 节，“MySQL 标准兼容性”。See Section 1.7, “MySQL Standards Compliance”.

TRADITIONAL TRADITIONAL

TRADITIONAL 相当于 STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO 和 NO_ENGINE_SUBSTITUTION。TRADITIONAL is equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION.

对于事务表，当启用 STRICT_ALL_TABLES 或 STRICT_TRANS_TABLES 时，如果数据更改语句中存在无效或缺失的值，将会发生错误。该语句会被中止并回滚。For transactional tables, an error occurs for invalid or missing values in a data-change statement when either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled. The statement is aborted and rolled back.

对于非事务性表，如果错误值出现在要插入或更新的第一行中，无论哪种模式，行为都是相同的：该语句会中止，表保持不变。如果语句插入或修改多行且错误值出现在第二行或更后面的行中，则结果取决于启用了哪种严格模式：For nontransactional tables, the behavior is the same for either mode if the bad value occurs in the first row to be inserted or updated: The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict mode is enabled:

严格模式会影响除以零的处理，包括 MOD(N,0)：Strict mode affects handling of division by zero, which includes MOD(N,0):

对于数据变更操作（INSERT, UPDATE）：For data-change operations (INSERT, UPDATE):

对于SELECT，除以零会返回NULL。启用严格模式也会产生警告。For SELECT, division by zero returns NULL. Enabling strict mode causes a warning to be produced as well.

严格模式影响服务器是否允许'0000-00-00'作为有效日期：Strict mode affects whether the server permits '0000-00-00' as a valid date:

严格模式会影响服务器是否允许年份部分非零但月份或日期部分为 0 的日期（例如 '2010-00-01' 或 '2010-01-00'）：Strict mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0 (dates such as '2010-00-01' or '2010-01-00'):

IGNORE 对语句执行的影响The Effect of IGNORE on Statement Execution

严格 SQL 模式对语句执行的影响The Effect of Strict SQL Mode on Statement Execution

CREATE TABLE ... SELECT：IGNORE 不适用于 CREATE TABLE 或 SELECT 语句的部分，而是适用于向由 SELECT 产生的表中插入的行。在唯一键值上重复的行将被丢弃。CREATE TABLE ... SELECT: IGNORE does not apply to the CREATE TABLE or SELECT parts of the statement but to inserts into the table of rows produced by the SELECT. Rows that duplicate an existing row on a unique key value are discarded.

DELETE：IGNORE 会让 MySQL 在删除行的过程中忽略错误。DELETE: IGNORE causes MySQL to ignore errors during the process of deleting rows.

INSERT：使用 IGNORE 时，与唯一键值重复的行将被丢弃。设置为会导致数据转换错误的值的行将被设置为最接近的有效值。INSERT: With IGNORE, rows that duplicate an existing row on a unique key value are discarded. Rows set to values that would cause data conversion errors are set to the closest valid values instead.

对于分区表，如果没有找到匹配给定值的分区，IGNORE 会导致包含未匹配值的行的插入操作静默失败。 For partitioned tables where no partition matching a given value is found, IGNORE causes the insert operation to fail silently for rows containing the unmatched value.

LOAD DATA，LOAD XML：使用 IGNORE 时，与唯一键值重复的行将被丢弃。LOAD DATA, LOAD XML: With IGNORE, rows that duplicate an existing row on a unique key value are discarded.

UPDATE：使用 IGNORE 时，如果在唯一键值上发生重复键冲突的行将不会被更新。更新为可能导致数据转换错误的值的行将被更新为最接近的有效值。UPDATE: With IGNORE, rows for which duplicate-key conflicts occur on a unique key value are not updated. Rows updated to values that would cause data conversion errors are updated to the closest valid values instead.

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

ALTER TABLEALTER TABLE

CREATE TABLECREATE TABLE

CREATE TABLE ... SELECTCREATE TABLE ... SELECT

DELETE（单表和多表均适用）DELETE (both single table and multiple table)

INSERTINSERT

LOAD DATALOAD DATA

LOAD XMLLOAD XML

SELECT SLEEP()SELECT SLEEP()

UPDATE（单表和多表）UPDATE (both single table and multiple table)