MySQL服务器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.cnf
my.ini
modes
--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模式
最重要的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)
INSERT
UPDATE
- 如果未启用此模式,则除以零将插入
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_PRECEDENCE
SQL模式可以获得旧的更高优先级行为。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; -> 1IGNORE_SPACE
函数名称和
(
字符之间可以有空格。这会使内置函数名称被视为保留字。结果,必须如“架构对象名称”中所述,引用与函数名称相同的标识符。例如,因为有一个COUNT()
函数,所以count
在以下语句中将as作为表名使用会导致错误: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_SPACE
SQL模式适用于内置函数,而不是用户定义的函数或存储功能。不管是否IGNORE_SPACE
启用,始终允许在UDF或存储的函数名称后留空格。有关进一步的讨论
IGNORE_SPACE
,请参见“函数名称的解析和解析”。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
创建表时,忽略all
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_UNSIGNED_SUBTRACTION
UNSIGNED
默认情况下,整数值之间的减法(其中一个类型为)会产生无符号结果。如果结果否则为负,则将导致错误:mysql>
SET sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql>SELECT CAST(0AS 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(0AS 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 BIGINTUNSIGNED NOT NULL); mysql>CREATE TABLE t1SELECT c1 - 1AS c2FROM 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 t2SELECT c1 - 1AS c2FROM test; mysql>DESCRIBE t2; +------- +------------ +------ +----- +--------- +------- + | Field | Type | Null | Key | Default | Extra | +------- +------------ +------ +----- +--------- +------- + | c2 | bigint(21) | NO | | 0 | | +------- +------------ +------ +----- +--------- +------- +这意味着
BIGINT UNSIGNED
并非在所有情况下都100%可用。请参见“信息函数”。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
列的未聚合的列。MySQL对标准SQL的扩展允许在
HAVING
子句中引用选择列表中的别名表达式。HAVING
不管是否ONLY_FULL_GROUP_BY
启用,该子句都可以引用别名。有关其他讨论和示例,请参见“ GROUP BY的MySQL处理”。
PAD_CHAR_TO_FULL_LENGTH
默认情况下,
CHAR
检索时会从列值中修剪尾随空格。如果PAD_CHAR_TO_FULL_LENGTH
启用,则不会进行修整,并且将检索到的CHAR
值填充到其全长。此模式不适VARCHAR
用于在检索时保留尾随空格的列。注意
从MySQL 8.0.13开始,
PAD_CHAR_TO_FULL_LENGTH
已弃用。它将在将来的MySQL版本中删除。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, tvalFROM tORDER 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 t1WHERE t1.IN (SELECT MAX(t1.b)FROM t2WHERE ...);在这里,
MAX(t1.b)
不能在外部查询中聚合,因为它出现在该WHERE
查询的子句中。在这种情况下,标准SQL需要一个错误。如果ANSI
未启用mode,则服务器在此类查询中的处理方式与解释方式相同。S(outer_ref)
S(const)
请参见“ MySQL标准符合性”。
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如何处理数据更改语句(例如INSERT
或)中的无效或缺失值UPDATE
。值可能由于多种原因而无效。例如,它可能具有错误的列数据类型,或者可能超出范围。当要插入的新行不包含其定义中NULL
没有显式DEFAULT
子句的非列的值时,缺少值。(对于NULL
列,NULL
如果缺少该值,则将其插入。)严格模式还会影响DDL语句,例如CREATE TABLE
。
如果严格模式无效,则MySQL会为无效或缺失的值插入调整后的值并产生警告(请参见“ SHOW WARNINGS语句”)。在严格模式下,可以通过使用INSERT IGNORE
或来产生此行为UPDATE IGNORE
。
对于SELECT
不更改数据的语句,无效值会在严格模式下生成警告,而不是错误。
对于试图创建超过最大密钥长度的密钥,严格模式会产生错误。如果未启用严格模式,则会导致警告并把密钥截断为最大密钥长度。
严格模式不影响是否检查外键约束。foreign_key_checks
可以用于此。(请参见“服务器系统变量”。)
如果启用STRICT_ALL_TABLES
或STRICT_TRANS_TABLES
启用了严格SQL模式,尽管这些模式的效果有些不同:
- 对于事务表,启用
STRICT_ALL_TABLES
或时,数据更改语句中的无效值或缺失值会发生错误STRICT_TRANS_TABLES
。该语句被中止并回滚。 对于非事务处理表,如果在要插入或更新的第一行中出现错误值,则两种模式的行为都相同:语句中止且表保持不变。如果该语句插入或修改了多行,并且错误值出现在第二行或更高行中,则结果取决于启用了哪种严格模式:
- 对于
STRICT_ALL_TABLES
,MySQL返回错误,并忽略其余行。但是,由于已插入或更新了较早的行,因此结果是部分更新。为避免这种情况,请使用单行语句,该语句可以在不更改表的情况下中止。 - 对于
STRICT_TRANS_TABLES
,MySQL将无效值转换为该列的最接近有效值,并插入调整后的值。如果缺少值,MySQL将为列数据类型插入隐式默认值。无论哪种情况,MySQL都会生成警告而不是错误,并继续处理该语句。“数据类型默认值”中介绍了隐式默认值。
- 对于
严格模式会影响按零,零日期和日期零的除法处理,如下所示:
严格模式会影响除以零的处理,其中包括:
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模式的比较。
严格模式影响由零,零日期和零在日期与结合处理划分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
具有主键column i
,则尝试将相同的in值i
插入多行通常会产生重复键错误:
mysql>INSERT INTO t (i)VALUES (1),(1); ERROR 1062 (23000): Duplicate entry '1' for key 't.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 't.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
:用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
系统变量可能设置的更多信息,请参见“服务器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