24、MySQL 8.0 5.1.8服务器SQL模式

MySQL服务器可以在不同的SQL模式下运行，并且可以根据sql_mode系统变量的值对不同的客户端应用不同的模式。DBA可以设置全局SQL模式以匹配站点服务器操作要求，并且每个应用程序可以将其会话SQL模式设置为自己的要求。
模式会影响MySQL支持的SQL语法以及它执行的数据验证检查。这使得在不同的环境中使用MySQL更容易，并且可以将MySQL与其他数据库服务器一起使用。
设置SQL模式
最重要的SQL模式
SQL模式的完整列表
组合SQL模式
严格的SQL模式
IGNORE关键字和严格SQL模式的比较
有关MySQL中服务器SQL模式常见问题的答案，请参见第A.3节“MySQL 8.0 FAQ：服务器SQL模式”。
使用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模式，请 在命令行或 选项文件（如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变量需要 SYSTEM_VARIABLES_ADMIN或 SUPER特权，并影响从此时开始连接的所有客户端的操作。设置SESSION变量仅影响当前客户端。每个客户可以随时更改其会话 sql_mode值。
要确定当前的全局或会话 sql_mode设置，请选择其值：
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
重要
SQL模式和用户定义的分区。  创建数据并将其插入分区表后，更改服务器SQL模式可能会导致此类表的行为发生重大变化，并可能导致数据丢失或损坏。强烈建议您一旦创建了使用用户定义分区的表格，就不要更改SQL模式。
在复制分区表时，主站和从站上不同的SQL模式也会导致问题。为了获得最佳结果，您应始终在主服务器和从服务器上使用相同的服务器SQL模式。
最重要的sql_mode 价值可能是这些：

ANSI

此模式更改语法和行为以更贴近地符合标准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 在未启用严格模式的情况下启用警告， 反之亦然。


因为 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_PRECEDENCESQL模式可以获得旧的高优先级行为 。


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_SPACESQL模式适用于内置函数，而不是用户定义的函数或存储功能。无论是否IGNORE_SPACE启用，始终允许在UDF或存储的函数名称后面有空格 。


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