做网站笔记本2014,怎样用虚拟主机建网站,泰安市住宅与房产信息网,南京发布最新通告官网地址#xff1a;MySQL :: MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes 欢迎关注留言#xff0c;我是收集整理小能手#xff0c;工具翻译#xff0c;仅供参考#xff0c;笔芯笔芯. MySQL 5.7 参考手册 / ... / 服务器 SQL 模式 5.1.10 服务器 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-modemodessql-modemodesmy.cnfmy.inimodes--sql-modesql-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_modeNO_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则附加到列类型。 表选项 显示为 。 ENGINEengine_nameTYPEengine_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_TABLESMySQL 返回错误并忽略其余行。但是由于已插入或更新了较早的行因此结果是部分更新。为了避免这种情况请使用单行语句该语句可以在不更改表的情况下中止。 对于 STRICT_TRANS_TABLESMySQL 会将无效值转换为该列最接近的有效值并插入调整后的值。如果缺少值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 ... SELECTIGNORE不适用于语句的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默认情况下启用。 通过上述更改默认情况下仍会启用更严格的数据检查但可以在当前需要或有必要的环境中禁用各个模式。
