MySQL5.7服务器 SQL 模式

这篇具有很好参考价值的文章主要介绍了MySQL5.7服务器 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-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变量需要 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 EXISTSALTER 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 DIRECTORYDATA 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启用时,减法结果是有符号的,即使任何操作数是 unsignedc2例如,比较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_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 IGNOREUPDATE 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则附加到列类型。

    • 表选项 显示为 。 ENGINE=engine_nameTYPE=engine_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_TABLES,MySQL 返回错误并忽略其余行。但是,由于已插入或更新了较早的行,因此结果是部分更新。为了避免这种情况,请使用单行语句,该语句可以在不更改表的情况下中止。

    • 对于 STRICT_TRANS_TABLES,MySQL 会将无效值转换为该列最接近的有效值,并插入调整后的值。如果缺少值,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 IGNOREUPDATE IGNORE,'0000-00-00'是允许的并且插入会产生警告。

  • 严格模式会影响服务器是否允许年部分非零但月或日部分为 0 的日期(例如'2010-00-01'或 '2010-01-00'):

    • 如果未启用严格模式,则允许包含零部分的日期并且插入不会产生警告。

    • 如果启用严格模式,则不允许包含零部分的日期,并且插入会产生错误,除非 IGNORE也给出了。对于 INSERT IGNOREUPDATE 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 插入NULLNOT 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: 1

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

示例:如果表t2有一NOT NULLid,则尝试插入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 ... 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系统变量的可能设置的更多信息,请参见 第 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默认情况下启用。

通过上述更改,默认情况下仍会启用更严格的数据检查,但可以在当前需要或有必要的环境中禁用各个模式。文章来源地址https://www.toymoban.com/news/detail-772958.html

到了这里,关于MySQL5.7服务器 SQL 模式的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • SQL Server 创建链接服务器 链接mysql 进行数据同步

    一个项目的产品迭代,因为迭代过程中两套系统同时使用,两套系统设计使用的数据库不同,从Sqlserver改到mysql了,所以需要将sqlserver的业务数据定时同步到mysql中。 当问题产生了,作为一个合格的码农,第一时间就是想着做一个程序实现数据同步,但是处于自身不叫懒惰,

    2024年02月14日
    浏览(43)
  • 服务器断电导致mysql崩溃,使用恢复模式的解决步骤

    2024-02-18T08:45:58.039432Z 0 [ERROR] InnoDB: Page [page id: space=0, page number=238] log sequence number 8738273916 is in the future! Current system log sequence number 8738272796. 2024-02-18T08:45:58.039450Z 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.

    2024年02月22日
    浏览(52)
  • [MySQL]主从服务器布置

    配置完成后重启服务器 $ sudo systemctl restart mariadb; //重启 CHANGE MASTER TO MASTER_HOST = ‘[主机ip]’, MASTER_USER = ‘filestore’, MASTER_PASSWORD = ‘[密码]’, MASTER_LOG_FILE = ‘mysql-bin.000007’, MASTER_LOG_POS = 154; start slave show slave status G ; 可能出现的问题: slava_IO 不能成功连接 . 出现的原因: 1.M

    2024年02月12日
    浏览(50)
  • MySQL数据库——MySQL优化服务器,提高MySQL的运行速度!

    MySQL是一种广泛使用的关系型数据库管理系统,优化MySQL服务器可以显著提高数据库的性能和运行速度。在下面的回答中,我将介绍一些常见的MySQL优化策略和技术,以帮助提高MySQL服务器的性能。 1、优化数据库设计: 正确设计数据库结构,使用适当的数据类型、索引和约束

    2024年02月07日
    浏览(69)
  • Linux服务器安装MYSQL

    涉及到的工具及软件连接 链接:https://pan.baidu.com/s/1r577kFeuojUrMoEUn88B8w 提取码:xh93 检查linux是否安装了mariadb数据库,mariadb数据库是mysql的分支。是免费开源的。mariadb和msyql会有冲突。首先要检查安装了mariadb, 卸载掉 对mariadb进行卸载 如果卸载不成功,需要去掉参数-y,手动确

    2024年02月06日
    浏览(50)
  • 服务器安装MySQL8

    本文所用到的为 Ubuntu 20.04  服务器  安装配置MySQL8 并启用远程连接 执行下列代码更新软件包 (软件包更新) 执行下列代码安装MySQL服务 出现此画面时 按Y继续安装 等待安装完成后 执行下述代码查看运行状态 如果出现以下画面表示MySQL服务已安装未启动 (MySQL未启动) 此时可以

    2024年02月03日
    浏览(49)
  • MySQL跨服务器关联查询

    1. 首先确认服务器的Federated引擎是否开启  修改数据库的配制文件my.ini,(我的my.ini的路径为:D:ProgramDataMySQLMySQL Server 5.7/my.ini),将federated添加到my.ini文件中 CONNECTION还可以使用另外一种方式(需要注意的是SERVER , WRAPPER):

    2024年02月22日
    浏览(41)
  • 银河麒麟服务器、centos7服务器一键卸载mysql脚本

    【出现问题】如果出现脚本执行报错的问题,基本就是文件编码的问题。 【解决办法】我们需要在linux系统里新建一个sh文件,并将脚本内容复制进去,再去执行新建的脚本即可解决问题。   【金山文档】 mysql相关 https://kdocs.cn/l/clqdydAVNJfU

    2024年02月10日
    浏览(75)
  • 阿里云服务器的mysql安装

    以下为本文参考的文章博客: https://blog.csdn.net/ChenLijiong/article/details/104089932 https://blog.csdn.net/a2814282061/article/details/108208070 https://blog.csdn.net/lkj8546/article/details/120627993 https://blog.csdn.net/weixin_44422604/article/details/107460684 提示:使用免费试用ECS进行介绍: 选择默认安全组即可 点击远

    2023年04月19日
    浏览(43)
  • mysql进阶-修改linux服务器中MySQL的字符集

    linux 中 mysql8 默认的字符集是 latin1 ,在插入中文时会报错,所以一般在配置好mysql时需要修改字符集为 utf8 【又叫utfmb3,一般开发够用,一个字符用3个字节表示】或者 utfmb4 【一个字符用4个字节表示,如果存储emoji表情,必须用utfmb4】 1366 - Incorrect string value: xE4 xBA xBA(xE7 x94 x

    2024年02月14日
    浏览(47)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包