服务器 SQL 模式

内容来源于mysql官方文档，结合自己的理解翻译记录。

• 什么是服务器 SQL 模式？

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

• 有多少服务器 SQL 模式？

每种模式都可以独立开启和关闭。有关可用模式的完整列表，后面分析。 

• 您如何确定服务器 SQL 模式？

您可以使用--sql-mode选项设置默认 SQL 模式(对于mysqld启动)。使用语句SET [GLOBAL| SESSION] sql_mode ='模式'，您可以在连接中更改设置，无论是本地连接还是全局生效。您可以通过发出SELECT @@sql_mode语句来检索当前模式。

• 模式是否依赖于数据库或连接？

模式未链接到特定数据库。模式可以在本地设置为 session(连接)，也可以全局设置为服务器。您可以使用SET [GLOBAL| SESSION] sql_mode ='模式'更改这些设置。

• 严格模式会影响性能吗？

输入数据的密集验证，某些设置比未完成验证需要更多 time。虽然 performance 影响不是很大，但如果你不需要这样的验证(也许你的 application 已经处理了所有这些)，那么 MySQL 会让你选择禁用严格模式。但是，如果确实需要，严格模式可以提供此类验证。

• 安装 MySQL 5.7 时，默认服务器 SQL 模式是什么？

MySQL 5.7 中的默认 SQL 模式包括以下模式：ONLY_FULLGROUP_BY，STRICT_TRANSTABLES，NO_ZERO_INDATE，NO_ZERODATE，ERROR_FOR_DIVISION_BY_ZERO，NO_AUTO_CREATE_USER和NO_ENGINE_SUBSTITUTION。

MySQL 服务器可以在不同的 SQL 模式下运行，并且可以针对不同的 clients 应用这些模式，具体取决于sql_mode系统变量的 value。 DBA 可以将 global SQL 模式设置为匹配站点服务器操作要求，并且每个 application 都可以将其 session SQL 模式设置为自己的要求。

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

要在服务器启动时设置 SQL 模式，请在命令行上使用--sql-mode =“模式”选项，或在my.cnf(Unix 操作系统)或my.ini(Windows)等选项文件中使用sql-mode =“模式”。 modes是由逗号分隔的不同模式的列表。要显式清除 SQL 模式，请在命令行上使用--sql-mode =“”将其设置为空 string，或在选项文件中使用sql-mode =“”。

注意
MySQL 安装程序可以在安装 process 期间配置 SQL 模式。如果 SQL 模式与默认模式或您期望的模式不同，请检查服务器在启动时读取的选项文件中的设置。
要在运行时更改 SQL 模式，请使用set语句设置 global 或 session sql_mode系统变量：

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

设置GLOBAL变量需要super权限，并影响从该时间开始连接的所有 clients 的操作。设置SESSION变量仅影响当前的 client。每个 client 都可以在任何时间更改其 session sql_mode value。 

要确定当前的 global 或 session sql_mode设置，请选择其 value：

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

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

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

最重要的 SQL 模式的值：

• ANSI

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

• STRICT_TRANSTABLES

如果无法将 value 插入到 transactional table 中，则中止该语句。对于非事务性 table，如果 value 出现在 single-row 语句或 multiple-row 语句的第一行中，则中止该语句。

• TRADITIONAL

使 MySQL 的行为类似于“传统的”SQL 数据库系统。将不正确的 value 插入列时，此模式的简单描述是“给出错误而不是警告”。

注意
启用TRADITIONAL(传统)模式后，只要发生错误，insert或update就会中止。如果您使用的是非事务性存储引擎，则可能不是您想要的，因为在错误之前进行的数据更改可能无法回滚，从而导致“部分完成”更新。

引用“严格模式”时，表示启用了STRICT_TRANSTABLES或STRICT_ALLTABLES之一或两者的模式。

更多模式见官方文档。

严格的 SQL 模式

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

如果严格模式不开启，MySQL 会为无效值或缺失值插入调整后的值并生成警告。在严格模式下，您可以使用INSERT IGNORE或UPDATE IGNORE生成此行为。

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

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

严格模式不会影响是否检查外部 key 约束。 foreignkey_checks可以用于此。

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

• 对于 transactional 表，如果启用了STRICT_ALLTABLES或STRICT_TRANSTABLES，则 data-change 语句中的值无效或缺失时会发生错误。该声明被中止并回滚。
• 对于非事务性表，如果要插入或更新的第一行中出现错 value，则对于任一模式的行为都是相同的：语句被中止且 table 保持不变。如果语句插入或修改多行，并且在第二行或更高行中出现错 value，则结果取决于启用的严格模式：
• 对于STRICT_ALLTABLES，MySQL 返回错误并忽略行的 rest。但是，由于已插入或更新了较早的行，因此结果是部分更新。要避免这种情况，请使用 single-row statements，可以在不更改 table 的情况下中止。
• 对于STRICT_TRANSTABLES，MySQL 将无效的 value 转换为列的最接近的有效 value，并插入调整后的 value。如果缺少 value，MySQL 会为列数据类型插入隐式默认值 value。在任何一种情况下，MySQL 都会生成警告而不是错误，并继续处理该语句。

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

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

对于 data-change 操作(INTER，UPDATE)：

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

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

• 严格模式会影响服务器是否允许'0000-00-00'作为有效的 date：
• 如果未启用严格模式，则允许'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 关键字和严格 SQL 模式的比较

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

以下 table 提供了默认情况下生成错误与警告时语句行为的摘要比较。默认情况下产生错误的示例是将NULL插入NOT NULL列。默认情况下产生警告的示例是将错误数据类型的 value 插入到列中(例如将 string 'abc'插入 integer 列)。

运作模式	当 Statement Default 为 Error 时	当 Statement Default 为 Warning 时
没有IGNORE或严格的 SQL 模式	错误	警告
使用IGNORE	警告	警告(与没有IGNORE或严格的 SQL 模式相同)
使用严格的 SQL 模式	错误(与没有IGNORE或严格的 SQL 模式相同)	错误
使用IGNORE和严格的 SQL 模式	警告	警告

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

IGNORE 对语句执行的影响

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

例如，如果 table t具有主 key 列i，则尝试将i的相同 value 插入多行通常会产生 duplicate-key 错误：

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

使用IGNORE时，仍未插入包含重复 key 的行，但会发出警告而不是错误：

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关键字： 

• CREATE TABLE ... SELECT：IGNORE不适用于CREAT TABLE或SELECT部分的语句，但适用于通过SELECT的方式插入数据行.对于唯一键值上的重复的多行数据的行将被丢弃。
• drop：IGNORE使 MySQL 在删除行的进程期间忽略错误。
• insert：使用IGNORE，将复制唯一 key value 上现有行的行。设置为可能导致数据转换错误的值的行将设置为最接近的有效值。

对于未找到与给定值匹配的分区的分区表，IGNORE会导致 insert 操作对包含不匹配值的行默认失败。

• 加载数据，加载 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 模式下运行，并且可以针对不同的 clients 应用这些模式，具体取决于sql_mode系统变量的 value。在“严格”SQL 模式下，服务器会将某些警告升级为错误。

比如，在 non-strict SQL 模式下，将 string 'abc'插入 integer 列会导致 value 转换为 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 模式下，无效的 value 被拒绝并出现错误：

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 模式适用于以下语句：

• ALTER TABLE

• CREATE TABLE

• CREATE TABLE ... SELECT

• DELETE (both single table and multiple table)

• INSERT

• LOAD DATA

• LOAD XML

• SELECT SLEEP()

• UPDATE (both single table and multiple table)

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

 严格的 SQL 模式适用于以下错误，输入值无效或缺失一类的错误。如果值的列数据类型错误或者可能超出范围，则值无效。对于非空且没有定义默认值的字段的表，如果新添加的数据缺少该字段的值，则数据会丢失。

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 模式更改 5.7

在 MySQL 5.7.22 中，不推荐使用这些 SQL 模式，并且将在以后的 MySQL 版本中删除这些模式：DB2，MAXDB，MSSQL，MYSQL323，MYSQL40，ORACLE，POSTGRESQL，NO_FIELD_OPTIONS，NOKEY_OPTIONS，NOTABLE_OPTIONS。

在 MySQL 5.7 中，因为GROUP BY处理变得更加复杂，包括检测功能依赖性，默认情况下启用ONLY_FULLGROUP_BY SQL 模式。 

但是，如果您发现启用ONLY_FULLGROUP_BY会导致拒绝现有应用的查询，则这些操作之一应该还原操作：

• 如果可以修改有问题的查询，请执行此操作，以便非聚合列在功能上依赖于GROUP BY列，或者通过使用ANYVALUE()引用非聚合列。
• 如果无法修改有问题的查询(比如，如果它是由 third-party application 生成的)，请在服务器启动时将sql_mode系统变量设置为不启用ONLY_FULLGROUP_BY。

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