360SDN.COM

首页/MySQL/列表

MySQL 8.0 5.1.8服务器SQL模式ONLY_FULL_GROUP_BY和STRICT_TRANS_TABLES

来源:  2018-09-13 13:59:12    评论:0点击:

来源:https://blog.csdn.net/dandan520520/article/details/79744998
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节“函数名称解析和解析”。


NO_AUTO_CREATE_USER


防止GRANT声明自动创建新的用户帐户,除非认证信息被指定。该声明必须使用IDENTIFIED BY或使用身份验证插件指定非空密码IDENTIFIED WITH。


注意
从MySQL 8.0.11开始, NO_AUTO_CREATE_USER已被删除。要创建MySQL帐户,请使用 CREATE USER而不是 GRANT。


NO_AUTO_VALUE_ON_ZERO


NO_AUTO_VALUE_ON_ZERO 影响AUTO_INCREMENT 列的处理。通常情况下,您通过插入NULL或 插入列来生成列的下一个序列号0。 NO_AUTO_VALUE_ON_ZERO 为了0只NULL产生下一个序列号而抑制这种行为。


如果0已经存储在表格的AUTO_INCREMENT 列中,此模式会很有用。(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_FIELD_OPTIONS


不要在输出中打印MySQL特定的列选项 SHOW CREATE TABLE。


注意
从MySQL 8.0.11开始, NO_FIELD_OPTIONS已被删除。


NO_KEY_OPTIONS


不要在输出中打印MySQL特定的索引选项 SHOW CREATE TABLE。


注意
从MySQL 8.0.11开始, NO_KEY_OPTIONS已被删除。


NO_TABLE_OPTIONS


不要ENGINE在输出中打印MySQL特定的表选项(如 ) SHOW CREATE TABLE。


注意
从MySQL 8.0.11开始, NO_TABLE_OPTIONS已被删除。


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启用时,即使任何操作数无符号,减法结果也是有符号的。例如,比较列的类型c2在表 t1与该列的 c2表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%可用。请参见 第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在未启用严格模式的情况下启用警告, 反之亦然。


因为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


拒绝对选择列表, HAVING条件或ORDER BY列表引用既未在GROUP BY子句中命名也未在功能上依赖(由GROUP BY列唯一确定)的非 聚合列的查询。


标准SQL的MySQL扩展允许在HAVING子句中引用 选择列表中的别名表达式。该HAVING子句可以引用别名,而不管是否 ONLY_FULL_GROUP_BY启用。


有关其他讨论和示例,请参见 第12.18.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模式。


STRICT_TRANS_TABLES


为事务性存储引擎启用严格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 |
+------+------------+
组合SQL模式


提供以下特殊模式作为上述列表中模式值组合的简写。


ANSI


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


ANSI模式还会导致服务器返回一个查询错误,其中S具有外部引用 的集合函数 无法在已解析外部引用的外部查询中进行聚合。这是一个这样的问题: 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.8节“MySQL标准合规性”。


DB2


相当于 PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS。


注意
从MySQL 8.0.11开始,DB2 已被删除。


MAXDB


相当于 PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER。


注意
从MySQL 8.0.11开始, MAXDB已被删除。


MSSQL


相当于 PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS。


注意
从MySQL 8.0.11开始, MSSQL已被删除。


MYSQL323


等同于MYSQL323, HIGH_NOT_PRECEDENCE。这意味着HIGH_NOT_PRECEDENCE 加上一些SHOW CREATE TABLE 特定的行为 MYSQL323:


TIMESTAMP列显示不包含DEFAULT或 ON UPDATE属性。


字符串列显示不包含字符集和归类属性。对于 CHAR与 VARCHAR列,如果核对是二进制的,BINARY被附加到列类型。


该 表选项显示为 。 ENGINE=engine_nameTYPE=engine_name


对于MEMORY表格,存储引擎显示为HEAP。


注意
从MySQL 8.0.11开始, MYSQL323已被删除。


MYSQL40


等同于MYSQL40, HIGH_NOT_PRECEDENCE。这意味着HIGH_NOT_PRECEDENCE 加上一些特定的行为 MYSQL40。MYSQL323除了SHOW CREATE TABLE 不HEAP作为MEMORY表格的存储引擎显示之外,它们与for相同。


注意
从MySQL 8.0.11开始, MYSQL40已被删除。


ORACLE


相当于 PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER。


注意
从MySQL 8.0.11开始, ORACLE已被删除。


POSTGRESQL


相当于 PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS。


注意
从MySQL 8.0.11开始, POSTGRESQL已被删除。


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如何处理数据更改语句(如INSERTor)中的 无效值或缺失值 UPDATE。由于多种原因,值可能无效。例如,它可能具有该列的错误数据类型,或者它可能超出范围。当要插入的新行不包含定义中NULL没有显式DEFAULT子句的非列 的值时,缺少值。(对于 NULL列,NULL如果值缺失则插入。)严格模式也会影响DDL语句,如CREATE TABLE。


如果严格模式没有生效,MySQL会为无效值或缺失值插入调整后的值,并生成警告(请参见 第13.7.6.40节“SHOW WARNINGS Syntax”)。在严格模式下,您可以使用INSERT IGNORE 或生成此行为 UPDATE IGNORE。


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


对于尝试创建超过最大密钥长度的密钥的严格模式会产生错误。如果严格模式未启用,则会导致警告并将密钥截断为最大密钥长度。


严格模式不影响是否检查外键约束。foreign_key_checks可以用于此。(请参见 第5.1.5节“服务器系统变量”。)


如果启用了STRICT_ALL_TABLES或者 STRICT_TRANS_TABLES已启用严格SQL模式 ,但这些模式的效果有所不同:


对于事务性表,当启用STRICT_ALL_TABLES或 STRICT_TRANS_TABLES启用数据更改语句时,无效值或缺失值会发生错误 。该声明被中止并回滚。


对于非事务性表,如果在要插入或更新的第一行中出现错误值,那么对于任一模式,行为都是相同的:语句被中止并且表保持不变。如果语句插入或修改多行,并且第二行或更后一行中出现错误值,则结果取决于启用了哪个严格模式:


因为STRICT_ALL_TABLES,MySQL返回一个错误并忽略其余的行。但是,因为较早的行已被插入或更新,所以结果是部分更新。为避免这种情况,请使用单行语句,该语句可以在不更改表格的情况下中止。


因为 STRICT_TRANS_TABLES,MySQL会将无效值转换为列的最接近的有效值并插入调整后的值。如果缺少一个值,MySQL将插入列数据类型的隐式默认值。无论哪种情况,MySQL都会生成警告而不是错误,并继续处理该语句。第11.7节“数据类型默认值”中介绍了隐式默认值。


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


严格模式影响零除的处理,其中包括 : 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'or '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模式(将警告升级为错误)的语句执行效果 。它描述了它们影响哪些语句,以及它们适用于哪些错误。


下表列出了默认情况下产生错误与警告时语句行为的总结比较。默认情况下产生错误的一个示例是将a插入NULL到NOT NULL列中。默认情况下产生警告的一个示例是将错误数据类型的值插入列(例如将字符串插入 'abc'到整数列中)。


操作模式 当语句默认为错误时 当语句默认为警告时
没有IGNORE或严格的SQL模式 错误 警告
同 IGNORE 警告 警告(与没有IGNORE或严格的SQL模式相同)
采用严格的SQL模式 错误(与没有IGNORE或严格的SQL模式相同) 错误
采用IGNORE严格的SQL模式 警告 警告
从表中得出的一个结论是,当 IGNORE关键字和严格SQL模式都有效时,IGNORE优先。这意味着,尽管IGNORE严格的SQL模式可以被认为对错误处理有相反的效果,但它们在一起使用时不会取消。


IGNORE对陈述执行的影响


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


例如,如果表t具有主键列i,则尝试将相同的值i插入多行中通常会产生重复键错误:


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: 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不适用于声明CREATE TABLE或 SELECT部分声明,而是插入到由声明产生的行表中 SELECT。丢弃在唯一键值上复制现有行的行。


DELETE: IGNORE导致MySQL在删除行的过程中忽略错误。


INSERT:使用 IGNORE,丢弃在唯一键值上复制现有行的行。将设置为会导致数据转换错误的值设置为最接近的有效值。


对于没有找到与给定值匹配的分区的分区表,IGNORE会导致插入操作对包含不匹配值的行进行无提示失败。


LOAD DATA, LOAD XML:With 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节“服务器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

本文由:学什么技术好

为您推荐

友情链接 |九搜汽车网 |手机ok生活信息网|ok生活信息网|ok微生活
 Powered by www.360SDN.COM   京ICP备11022651号-4 © 2012-2016 版权