的值,MySQL服务器可以以不同的SQL模式操作,并且可以为不同的客户机应用不同的这些模式sql_mode
系统变量。dba可以设置全局SQL模式以匹配站点服务器的运行需求,每个应用程序可以根据自己的需求设置会话SQL模式。
模式影响MySQL支持的SQL语法和执行的数据验证检查。这使得在不同的环境中使用MySQL以及与其他数据库服务器一起使用MySQL更加容易。
InnoDB表,考虑也
innodb_strict_mode
系统变量。它支持附加的错误检查InnoDB
表。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模式,请使用
——sql模式= "
选项,或者模式
"sql模式= "
在选项文件中,例如模式
"my.cnf
(Unix操作系统)或my.ini
(Windows)。模式
由逗号分隔的不同模式的列表。若要显式清除SQL模式,使用将其设置为空字符串——sql模式= " "
在命令行,或者sql模式= " "
在一个选项文件中。请注意MySQL安装程序在安装过程中可能会配置SQL模式。
如果SQL模式与默认模式或您期望的模式不同,请检查服务器在启动时读取的选项文件中的设置。
若要在运行时更改SQL模式,请设置全局或会话
sql_mode
系统变量使用集
声明:设置全局sql_mode = '模式”;SET SESSION sql_mode = '模式”;
设置
全球
变量需要SYSTEM_VARIABLES_ADMIN
特权(或已弃用的超级
特权),并影响从那时起连接的所有客户机的操作。设置会话
变量只影响当前客户端。每个客户机都可以更改其会话sql_mode
价值在任何时候。确定当前全局或会话
sql_mode
设置,选择它的值:选择@@GLOBAL.sql_mode;选择@@SESSION.sql_mode;
重要的SQL模式和用户定义分区。在创建数据并将数据插入分区表之后,更改服务器SQL模式可能导致这些表的行为发生重大变化,并可能导致数据丢失或损坏。在创建了使用用户定义分区的表之后,强烈建议不要更改SQL模式。
在复制分区表时,源和副本上不同的SQL模式也会导致问题。为了获得最佳结果,您应该始终在源和副本上使用相同的服务器SQL模式。
有关更多信息,请参见
最重要的
sql_mode
价值观可能是这样的:这种模式改变语法和行为,使其更接近标准SQL。这是一个特别的
如果不能将给定的值插入到事务表中,则中止语句。对于非事务性表,如果值出现在单行语句或多行语句的第一行中,则中止语句。本节稍后将给出更多细节。
让MySQL像一个”传统的”SQL数据库系统。这种模式的简单描述如下”给出错误而不是警告”将不正确的值插入列时。这是一个特别的
请注意与
传统的
启用模式,一个插入
或更新
一旦发生错误,立即中止。如果您正在使用非事务性存储引擎,这可能不是您想要的结果,因为在错误之前所做的数据更改可能无法回滚,从而导致错误”部分完成的”更新。
当本手册提及”严格的模式,”它的意思是有其中一个或两个的模式
STRICT_TRANS_TABLES
或STRICT_ALL_TABLES
启用。下面列出了所有支持的SQL模式:
不要执行日期的完整检查。只检查月的范围在1到12之间,日的范围在1到31之间。这对于在三个不同字段中获取年、月和日并准确存储用户插入的内容而不需要日期验证的Web应用程序可能很有用。该模式适用于
日期
而且DATETIME
列。它并不适用时间戳
列,它们总是需要有效的日期。与
ALLOW_INVALID_DATES
禁用后,服务器要求月和日的值是合法的,而不仅仅是1到12和1到31的范围。在禁用严格模式的情况下,无效日期如“2004-04-31”
被转换成“0000-00-00”
并生成一个警告。如果启用了严格模式,无效的日期将生成错误。要允许这样的日期,请启用ALLOW_INVALID_DATES
.治疗
"
作为标识符引用字符(如`
引用字符),而不是作为字符串引用字符。你仍然可以使用`
在启用此模式的情况下引用标识符。与ANSI_QUOTES
启用后,不能使用双引号引用字面值字符串,因为它们被解释为标识符。的
ERROR_FOR_DIVISION_BY_ZERO
模式影响对除零的处理,其中包括国防部(
.对于数据更改操作(N
, 0)插入
,更新
),其效果还取决于是否启用严格SQL模式。如果未启用此模式,则插入数除以零
零
并没有产生任何警告。如果启用此模式,则除以零插入数
零
并产生一个警告。如果启用了此模式和严格模式,则除零将产生错误,除非
忽略
也是给定的。为插入忽略
而且更新忽略
,除以零插入零
并产生一个警告。
为
选择
,除以零回报零
.启用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模式的效果中。的优先级
不
运算符是这样的,表达式如不是a在b和c之间
被解析为不是(a在b和c之间)
.在一些较老的MySQL版本中,该表达式被解析为(不是a)在b和c之间
.的方法可以获得旧的更高优先级行为HIGH_NOT_PRECEDENCE
SQL模式。设置sql_mode = ";SELECT NOT 1 BETWEEN -5 AND 5;SET sql_mode = 'HIGH_NOT_PRECEDENCE';SELECT NOT 1 BETWEEN -5 AND 5;- > 1
对象之间允许有空格
(
的性格。这将导致内置函数名被视为保留字。因此,与函数名相同的标识符必须像中描述的那样加引号COUNT ()
功能,使用数
因为下面语句中的表名会导致错误:CREATE TABLE count (i INT);错误1064(42000):您的SQL语法有一个错误
表名应该加引号:
创建表count (i INT);查询OK, 0行受影响(0.00秒)
的
IGNORE_SPACE
SQL模式适用于内置函数,不适用于用户定义函数或存储函数。在UDF或存储函数名后始终允许有空格,无论是否IGNORE_SPACE
启用。进一步讨论
IGNORE_SPACE
,请参阅NO_AUTO_VALUE_ON_ZERO
影响的处理AUTO_INCREMENT
列。通常,您可以通过插入其中之一为列生成下一个序列号零
或0
进去。NO_AUTO_VALUE_ON_ZERO
抑制此行为0
所以,只有零
生成下一个序列号。这种模式在以下情况下是有用的
0
已存储在表的AUTO_INCREMENT
列。(存储0
顺便说一下,不建议这么做。)例如,如果您用, mysqldump然后重新加载,MySQL通常会生成新的序列号,当它遇到0
值,导致表的内容与转储表的内容不同。启用NO_AUTO_VALUE_ON_ZERO
在重新加载转储文件之前解决了这个问题。由于这个原因,, mysqldump自动在输出中包含启用的语句NO_AUTO_VALUE_ON_ZERO
.启用此模式将禁用反斜杠字符(
\
)作为字符串和标识符中的转义字符。启用此模式后,反斜杠将变成与任何其他字符一样的普通字符,并且的默认转义序列就像
表达式更改为不使用转义字符。在创建表时,忽略所有
索引目录
而且数据目录
指令。这个选项在复制服务器上很有用。控制默认存储引擎的自动替换
创建表
或ALTER TABLE
指定已禁用或未编译的存储引擎。默认情况下,
NO_ENGINE_SUBSTITUTION
启用。因为存储引擎可以在运行时插入,所以不可用的引擎会以同样的方式处理:
与
NO_ENGINE_SUBSTITUTION
残疾,创建表
使用默认引擎,如果所需的引擎不可用,则会出现警告。为ALTER TABLE
时,会出现一个警告,该表不会被更改。与
NO_ENGINE_SUBSTITUTION
启用后,如果所需的引擎不可用,则会发生错误,并且不会创建或修改表。整数值之间的减法,其中1为类型
无符号
,默认情况下产生无符号结果。如果结果本来是负数,则会产生一个错误:设置sql_mode = ";查询OK, 0 rows affected (0.00 sec) mysql> SELECT CAST(0 AS UNSIGNED) - 1;错误1690 (22003):BIGINT UNSIGNED值超出'(强制转换(0为UNSIGNED) - 1)'的范围
如果
NO_UNSIGNED_SUBTRACTION
启用SQL模式,结果为负数:SET sql_mode = ' no_unsigned_subtract ';mysql> SELECT (0 AS UNSIGNED) - 1;+-------------------------+ | 作为无符号(0)- 1 | +-------------------------+ | - 1 | +-------------------------+
如果使用此类操作的结果来更新
无符号
整型列,则将结果剪切为列类型的最大值,如果是则将结果剪切为0NO_UNSIGNED_SUBTRACTION
启用。如果启用了严格的SQL模式,则会发生错误,并且列保持不变。当
NO_UNSIGNED_SUBTRACTION
启用时,对减法结果加符号,即使任何操作数是无符号的.例如,比较列的类型c2
在表t1
与列的c2
在表t2
:mysql >设置sql_mode = ";CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;mysql >描述t1;+-------+---------------------+------+-----+---------+-------+ | 字段默认零关键| | | | |类型多 | +-------+---------------------+------+-----+---------+-------+ | c2 | bigint(21)无符号| | | 0 | | +-------+---------------------+------+-----+---------+-------+ mysql >设置sql_mode =“NO_UNSIGNED_SUBTRACTION”;CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;mysql >描述t2;+-------+------------+------+-----+---------+-------+ | 字段默认零关键| | | | |类型多 | +-------+------------+------+-----+---------+-------+ | c2 | bigint (21) | | | 0 | | +-------+------------+------+-----+---------+-------+
这意味着
长整型数字无符号
并非在所有上下文中都100%可用。看到的
NO_ZERO_DATE
模式影响服务器是否允许“0000-00-00”
作为有效日期。它的效果还取决于是否启用了严格的SQL模式。如果未启用此模式,
“0000-00-00”
是允许的,并且插入不会产生警告。如果启用此模式,
“0000-00-00”
,并且插入会产生警告。如果开启了该模式和严格模式,
“0000-00-00”
是不允许的,插入会产生错误,除非忽略
也是给定的。为插入忽略
而且更新忽略
,“0000-00-00”
,并且插入会产生警告。
NO_ZERO_DATE
弃用。NO_ZERO_DATE
不是严格模式的一部分,但应该与严格模式一起使用,并在默认情况下启用。如果出现以下情况,则会出现警告NO_ZERO_DATE
在不启用严格模式的情况下启用,反之亦然。因为
NO_ZERO_DATE
已弃用,您应该预期它将在未来的MySQL版本中作为一个单独的模式名被删除,它的效果包括在严格SQL模式的效果中。的
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”
并发出警告。如果启用了此模式和严格模式,则不允许部分为零的日期,插入会产生错误,除非
忽略
也是给定的。为插入忽略
而且更新忽略
, 0部分的日期插入为“0000-00-00”
并发出警告。
NO_ZERO_IN_DATE
弃用。NO_ZERO_IN_DATE
不是严格模式的一部分,但应该与严格模式一起使用,并在默认情况下启用。如果出现以下情况,则会出现警告NO_ZERO_IN_DATE
在不启用严格模式的情况下启用,反之亦然。因为
NO_ZERO_IN_DATE
已弃用,您应该预期它将在未来的MySQL版本中作为一个单独的模式名被删除,它的效果包括在严格SQL模式的效果中。拒绝select列表,
有
条件,或命令
类中未命名的非聚合列集团
子句在功能上也不依赖于(唯一由)集团
列。标准SQL的MySQL扩展允许在
有
子句添加到选择列表中的别名表达式。的有
子句可以引用别名,无论是否ONLY_FULL_GROUP_BY
启用。默认情况下,末尾的空格是从
字符
检索时的列值。如果PAD_CHAR_TO_FULL_LENGTH
启用时,不进行修剪并检索字符
值被填充到它们的全部长度。该模式不适用于VARCHAR
列,在检索时为其保留尾随空格。请注意从MySQL 8.0.13开始,
PAD_CHAR_TO_FULL_LENGTH
弃用。希望它在MySQL的未来版本中被删除。CREATE TABLE t1 (c1 CHAR(10));查询OK, 0行影响(0.37秒)mysql> INSERT INTO t1 (c1) VALUES('xy');查询OK, 1行影响(0.01秒)mysql> SET sql_mode = ";查询OK, 0 rows affected (0.00 sec) mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;+------+-----------------+ | c1 | CHAR_LENGTH (c1 ) | +------+-----------------+ | xy | 2 | +------+-----------------+ 1行组(0.00秒)mysql >设置sql_mode =“PAD_CHAR_TO_FULL_LENGTH”;查询OK, 0 rows affected (0.00 sec) mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;+------------+-----------------+ | c1 | CHAR_LENGTH (c1 ) | +------------+-----------------+ | xy | 10 | +------------+-----------------+ 1行集(0.00秒)
为所有存储引擎开启严格SQL模式。无效的数据值将被拒绝。有关详细信息,请参见
为事务性存储引擎启用严格SQL模式,并在可能时为非事务性存储引擎启用严格SQL模式。有关详细信息,请参见
控件在插入对象时是否发生舍入或截断
时间
,日期
,或时间戳
值的小数秒部分转换为具有相同类型但小数位数较少的列。默认行为是使用舍入。如果启用了此模式,则会发生截断。下面的语句序列说明了其中的区别:CREATE TABLE t (id INT, tval TIME);设置sql_mode = ";INSERT INTO t (id, tval) VALUES(1,1.55);设置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 | +------+------------+
另请参阅
下面的特殊模式作为前面列表中模式值的组合的简写。
相当于
REAL_AS_FLOAT
,PIPES_AS_CONCAT
,ANSI_QUOTES
,IGNORE_SPACE
,ONLY_FULL_GROUP_BY
.ANSI
模式还会导致服务器对设置函数的查询返回错误年代
通过外部引用
不能在已解析外部引用的外部查询中聚合。这是这样一个查询:年代
(outer_ref
)SELECT * FROM t1 WHERE t1。a (SELECT MAX(t1.b) FROM t2 WHERE…)
在这里,
马克斯(t1.b)
对象中出现的,无法在外部查询中聚合在哪里
子句的查询。在这种情况下,标准SQL要求出现错误。如果ANSI
模式未启用时,服务器处理
在这样的查询中,它会以同样的方式进行解释年代
(outer_ref
)
.年代
(常量
)传统的
相当于STRICT_TRANS_TABLES
,STRICT_ALL_TABLES
,NO_ZERO_IN_DATE
,NO_ZERO_DATE
,ERROR_FOR_DIVISION_BY_ZERO
,NO_ENGINE_SUBSTITUTION
.
严格模式控制MySQL如何处理数据更改语句中的无效值或缺失值,例如
插入
或更新
.一个值可能是无效的,原因有几个。例如,它可能具有列的错误数据类型,或者它可能超出范围。如果要插入的新行不包含非的值,则会丢失值零
没有显式的列默认的
条款。(零
列,零
如果缺少该值,则插入。)严格模式还会影响DDL语句,例如创建表
.如果严格模式没有生效,MySQL会插入调整后的无效值或缺失值,并产生警告(参见
插入忽略
或更新忽略
.对于以下语句
选择
不更改数据的无效值在严格模式下生成警告,而不是错误。当试图创建超过最大密钥长度的密钥时,严格模式会产生错误。当没有启用严格模式时,将导致一个警告,并将密钥截断到最大密钥长度。
严格模式不影响是否检查外键约束。
foreign_key_checks
可以用来做那个。(见STRICT_ALL_TABLES
或STRICT_TRANS_TABLES
启用,尽管这些模式的效果有些不同:对于事务性表,如果数据更改语句中的值无效或缺失,则会发生错误
STRICT_ALL_TABLES
或STRICT_TRANS_TABLES
启用。语句将被中止并回滚。对于非事务性表,如果错误值出现在要插入或更新的第一行,则两种模式下的行为都是相同的:语句中止,表保持不变。如果语句插入或修改了多行,而bad值出现在第二行或之后的行,则结果取决于启用了哪种严格模式:
为
STRICT_ALL_TABLES
, MySQL返回一个错误并忽略其余行。但是,由于前面的行已经插入或更新,所以结果是部分更新。为了避免这种情况,可以使用单行语句,可以在不更改表的情况下中止该语句。为
STRICT_TRANS_TABLES
, MySQL将无效值转换为该列最接近的有效值,并插入调整后的值。如果缺少一个值,MySQL会插入列数据类型的隐式默认值。在这两种情况下,MySQL都会生成一个警告而不是错误,并继续处理该语句。隐式默认值在严格模式影响对除零的处理,其中包括
国防部(
:N
, 0)如果没有启用严格模式,则插入数除以零
零
并没有产生任何警告。如果启用了严格模式,则除零会产生错误,除非
忽略
也是给定的。为插入忽略
而且更新忽略
,除以零插入零
并产生一个警告。
为
选择
,除以零回报零
.启用严格模式也会产生警告。严格模式会影响服务器是否允许
“0000-00-00”
作为有效日期:如果没有启用严格模式,
“0000-00-00”
是允许的,并且插入不会产生警告。如果启用了严格模式,
“0000-00-00”
是不允许的,插入会产生错误,除非忽略
也是给定的。为插入忽略
而且更新忽略
,“0000-00-00”
,并且插入会产生警告。
严格模式会影响服务器是否允许年份部分为非零而月或日部分为0的日期(日期如
“2010-00-01”
或“2010-01-00”
):如果不启用严格模式,则允许部件为零的日期,并且插入不产生警告。
如果启用了严格模式,则不允许部分为零的日期,插入会产生错误,除非
忽略
也是给定的。为插入忽略
而且更新忽略
, 0部分的日期插入为“0000-00-00”
(这被认为是有效的忽略
)并发出警告。
关于严格模态的更多信息
忽略
,请参阅ERROR_FOR_DIVISION_BY_ZERO
,NO_ZERO_DATE
,NO_ZERO_IN_DATE
模式。
方法对语句执行的影响进行比较
忽略
关键字(将错误降级为警告)和严格SQL模式(将警告升级为错误)。它描述了它们影响哪些语句,以及它们应用于哪些错误。下表给出了默认情况下产生错误和产生警告时语句行为的摘要比较。默认情况下产生错误的一个例子是插入
零
成一个非空
列。默认情况下产生警告的一个例子是在列中插入错误数据类型的值(例如插入字符串)“abc”
转化为一个整数列)。运作模式 当“默认语句”为“错误”时 当默认语句为警告时 没有 忽略
或严格的SQL模式错误 警告 与 忽略
警告 警告(与无相同 忽略
或严格SQL模式)使用严格的SQL模式 错误(与没有相同 忽略
或严格SQL模式)错误 与 忽略
和严格的SQL模式警告 警告 从表格中得出的一个结论是,当
忽略
关键字和严格SQL模式都有效,忽略
优先。这意味着,尽管忽略
和严格SQL模式可以被认为对错误处理有相反的影响,它们在一起使用时不会取消。MySQL中的一些语句支持一个可选的
忽略
关键字。该关键字导致服务器降级某些类型的错误并生成警告。对于多行语句,忽略
导致语句跳到下一行而不是中止。对于不可忽略的错误,错误的发生与忽略
关键字。)示例:如果表
t
是否有主键列我
的惟一值,试图插入相同的值我
进入多行通常会产生重复键错误:mysql> INSERT INTO t (i) VALUES(1),(1);错误1062(23000):重复输入'1'键't.PRIMARY'
与
忽略
,包含重复键的行仍然没有插入,但会出现警告而不是错误:mysql> INSERT INTO t (i) VALUES(1),(1);查询OK, 1 row affected, 1 warning(0.01秒)Records: 2 duplicate: 1 Warnings: 1 mysql> SHOW Warnings;+---------+------+-----------------------------------------+ | 水平| |消息代码 | +---------+------+-----------------------------------------+ | 警告| 1062 | ' 1 '关键“t.PRIMARY重复条目 ' | +---------+------+-----------------------------------------+ 1行集(0.00秒)
这些陈述支持
忽略
关键字:的
忽略
关键字适用于以下可忽略错误: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并发出警告:设置sql_mode = ";查询OK, 0行影响(0.00 sec) mysql> INSERT INTO t (i) VALUES('abc');查询OK, 1 row affected, 1 warning(0.01秒)mysql> SHOW WARNINGS;+---------+------+--------------------------------------------------------+ | 水平| |消息代码 | +---------+------+--------------------------------------------------------+ | 警告| 1366 |不正确的整数值:“abc”列在第一行“我” | +---------+------+--------------------------------------------------------+ 1行集(0.00秒)
在严格SQL模式下,无效值将被拒绝,并返回错误:
设置sql_mode = 'STRICT_ALL_TABLES';查询OK, 0行影响(0.00 sec) mysql> INSERT INTO t (i) VALUES('abc');错误1366 (HY000):错误的整数值:'abc'列'i'在第1行
的可能设置的详细信息
sql_mode
系统变量,见
在存储程序中,如果程序是在严格模式生效时定义的,那么上面列出的类型的个别语句将以严格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_log er_no_default_for_field er_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模式所适用的错误不在上面的列表中。