MySQL与SQL标准符合性

本节介绍MySQL与ANSI / ISO SQL标准的关系。MySQL Server对SQL标准有许多扩展，在这里您可以了解它们是什么以及如何使用它们。您还可以找到有关MySQL Server缺少的功能以及如何解决某些差异的信息。

自1986年以来，SQL标准一直在发展，并且存在多个版本。在本手册中，“ SQL-92 ”是指1992年发布的标准。“ SQL：1999 ”，“ SQL：2003 ”，“ SQL：2008 ”和“ SQL：2011 ”是指该标准的版本。相应的年份，最后一个是最新版本。我们使用短语“ SQL标准”或“标准SQL ”随时表示SQL标准的当前版本。

我们对该产品的主要目标之一是继续努力使其符合SQL标准，但又不牺牲速度或可靠性。如果这大大增加了MySQL服务器在我们大部分用户群中的可用性，那么我们不怕添加SQL扩展或对非SQL功能的支持。该HANDLER接口是这一战略的一个例子。请参见“ HANDLER语句”。

我们将继续支持事务性和非事务性数据库，以满足关键任务24/7的使用和繁重的Web或日志记录的使用。

MySQL Server最初旨在与小型计算机系统上的中型数据库（10-100百万行，或每个表约100MB）一起使用。今天，MySQL服务器可以处理TB级的数据库。

尽管MySQL复制功能提供了重要的功能，但我们的目标不是实时支持。

MySQL支持ODBC级别0到3.51。

MySQL使用NDBCLUSTER存储引擎支持高可用性数据库集群。请参见MySQL NDB Cluster 8.0。

我们实现了XML功能，该功能支持大多数W3C XPath标准。请参见“ XML函数”。

MySQL支持RFC 7159定义的本机JSON数据类型，并基于ECMAScript标准（ECMA-262）。请参见“ JSON数据类型”。MySQL还实现了SQL：2016标准的预发布草案指定的SQL / JSON函数的子集；有关更多信息，请参见“ JSON函数”。

选择SQL模式

MySQL服务器可以在不同的SQL模式下运行，并且可以根据sql_mode系统变量的值将这些模式不同地应用于不同的客户端。DBA可以设置全局SQL模式以匹配站点服务器操作要求，并且每个应用程序都可以将其会话SQL模式设置为自己的要求。

模式会影响MySQL支持的SQL语法以及它执行的数据验证检查。这使得在不同环境中使用MySQL以及将MySQL与其他数据库服务器一起使用更加容易。

有关设置SQL模式的更多信息，请参见“服务器SQL模式”。

在ANSI模式下运行MySQL

要以ANSI模式运行MySQL Server，请使用该选项启动mysqld--ansi。以ANSI模式运行服务器与使用以下选项启动服务器相同：

--transaction-isolation=SERIALIZABLE --sql-mode=ANSI

为了在运行时获得相同的效果，请执行以下两个语句：

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET GLOBAL sql_mode = 'ANSI';

您可以看到将sql_mode系统变量设置为'ANSI'启用与ANSI模式相关的所有SQL模式选项，如下所示：

mysql> SET GLOBAL sql_mode='ANSI';
mysql> SELECT @@GLOBAL.sql_mode;
-> 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI'

使用ANSI模式运行服务器与--ansi将SQL模式设置为并不完全相同，'ANSI'因为该--ansi选项还可以设置事务隔离级别。

请参见“服务器命令选项”。

MySQL对标准SQL的扩展

MySQL Server支持某些您可能在其他SQL DBMS中找不到的扩展。请注意，如果使用它们，您的代码将无法移植到其他SQL Server。在某些情况下，可以使用以下形式的注释编写包含MySQL扩展但仍可移植的代码：

/*! MySQL-specific code */

在这种情况下，MySQL Server会像处理其他任何SQL语句一样解析并执行注释中的代码，但是其他SQL Server将忽略这些扩展。例如，MySQL Server可以STRAIGHT_JOIN在以下语句中识别关键字，而其他服务器则不能：

SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...

如果在!字符后添加版本号，则仅当MySQL版本大于或等于指定的版本号时，才会执行注释中的语法。KEY_BLOCK_SIZE以下注释中的子句仅由MySQL 5.1.10或更高版本的服务器执行：

CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;

以下描述列出了按类别组织的MySQL扩展。

磁盘上数据的组织
MySQL Server将每个数据库映射到MySQL数据目录下的目录，并将数据库内的表映射到数据库目录中的文件名。因此，数据库和表名在具有区分大小写的文件名的操作系统（例如大多数Unix系统）上的MySQL Server中是区分大小写的。请参见“标识符区分大小写”。
通用语言语法
- 默认情况下，字符串可以被封闭"以及'。如果ANSI_QUOTES启用了SQL模式，则字符串只能用括起来，'并且服务器将用括起来的字符串解释"为标识符。
- \是字符串中的转义字符。
- 在SQL语句中，可以使用db_name.tbl_name语法从不同数据库访问表。某些SQL Server提供相同的功能，但称之为User space。MySQL Server不支持诸如以下语句中使用的表空间CREATE TABLE ralph.my_table ... IN my_tablespace。
SQL语句语法
- 在ANALYZE TABLE，CHECK TABLE，OPTIMIZE TABLE，和REPAIR TABLE语句。
- CREATE DATABASE，DROP DATABASE和ALTER DATABASE语句。请参见“ CREATE DATABASE语句”，“ DROP DATABASE语句”和“ ALTER DATABASE语句”。
- 该DO声明。
- EXPLAIN SELECT获取有关查询优化器如何处理表的描述。
- 该FLUSH和RESET语句。
- 该SET声明。请参见“变量分配的SET语法”。
- 该SHOW声明。请参见“ SHOW语句”。SHOW通过使用SELECTquery，可以以更标准的方式获得许多特定于MySQL的语句产生的信息INFORMATION_SCHEMA。请参见INFORMATION_SCHEMA表。
- 使用LOAD DATA。在许多情况下，此语法与Oracle兼容LOAD DATA。请参见“ LOAD DATA语句”。
- 使用RENAME TABLE。请参见“ RENAME TABLE语句”。
- 用REPLACE代替DELETE加号INSERT。请参见“ REPLACE语句”。
- 使用的，或者，或者在声明。使用多个的，，，或在条款声明。请参见“ ALTER TABLE语句”。CHANGE col_nameDROP col_nameDROP INDEXIGNORERENAMEALTER TABLEADDALTERDROPCHANGEALTER TABLE
- 索引名称的使用，列前缀的索引以及INDEX或KEYin CREATE TABLE语句的使用。请参见“ CREATE TABLE语句”。
- 使用的TEMPORARY或IF NOT EXISTS与CREATE TABLE。
- IF EXISTS与DROP TABLE和一起使用DROP DATABASE。
- 使用单个DROP TABLE语句删除多个表的功能。
- 和语句的ORDER BY和LIMIT子句。UPDATEDELETE
- INSERT INTO tbl_name SET col_name=...句法。
- 和语句的DELAYED子句。INSERTREPLACE
- 在LOW_PRIORITY该条款INSERT，REPLACE，DELETE，和UPDATE语句。
- 在语句中使用INTO OUTFILE或。请参见“ SELECT语句”。INTO DUMPFILESELECT
- 选项，例如STRAIGHT_JOIN或SQL_SMALL_RESULTin SELECT语句。
- 您无需在GROUP BY子句中命名所有选定的列。对于某些非常特定但非常普通的查询，这可以提供更好的性能。请参见“聚合（GROUP BY）函数”。
- 您可以指定ASC并DESC用GROUP BY，不仅仅是ORDER BY。
- 使用:=赋值运算符在语句中设置变量的能力。请参见“用户定义的变量”。
资料类型
- MEDIUMINT，SET和ENUM数据类型，以及各种BLOB和TEXT数据类型。
- AUTO_INCREMENT，BINARY，NULL，UNSIGNED，和ZEROFILL数据类型属性。
功能和运算符
- 为了使从其他SQL环境迁移的用户更容易，MySQL Server支持许多功能的别名。例如，所有字符串函数都支持标准SQL语法和ODBC语法。
- 与C语言一样， MySQL Server将||and &&运算符理解为逻辑OR和AND。在MySQL服务器，||并且OR是同义词，因为是&&和AND。由于这种漂亮的语法，MySQL Server不支持||用于字符串连接的标准SQL 运算符。使用CONCAT()代替。由于CONCAT()采用任意数量的参数，因此很容易将||运算符的使用转换为MySQL Server。
- 使用where 具有多个元素。COUNT(DISTINCT value_list)value_list
- 默认情况下，字符串比较不区分大小写，排序顺序由当前字符集的排序规则确定utf8mb4。要执行区分大小写的比较，您应该BINARY使用属性声明列或使用BINARY强制转换，这将导致使用基本字符代码值而不是词法顺序来进行比较。
- 该%运营商的代名词MOD()。即等于。支持C程序员并与PostgreSQL兼容。N%MMOD(N,M)%
- =，<>，<=，<，>=，>，<<，>>，<=>，AND，OR，或LIKE运营商可以在输出列列表中的表达式用（向左的FROM）的SELECT语句。例如：
```
mysql> SELECT col1=1 AND col2=2 FROM my_table;
```
- 该LAST_INSERT_ID()函数返回最新AUTO_INCREMENT值。请参见“信息函数”。
- LIKE允许使用数值。
- REGEXP和NOT REGEXP扩展正则表达式运算符。
- CONCAT()或CHAR()带有一个或两个以上参数。（在MySQL Server中，这些函数可以使用可变数量的参数。）
- BIT_COUNT()，CASE，ELT()，FROM_DAYS()，FORMAT()，IF()，MD5()，PERIOD_ADD()，PERIOD_DIFF()，TO_DAYS()，和WEEKDAY()功能。
- 使用的TRIM()修剪子。标准SQL仅支持删除单个字符。
- 该GROUP BY功能STD()，BIT_OR()，BIT_AND()，BIT_XOR()，和GROUP_CONCAT()。请参见“聚合（GROUP BY）函数”。

MySQL与标准SQL的区别

我们试图使MySQL Server遵循ANSI SQL标准和ODBC SQL标准，但是MySQL Server在某些情况下执行操作的方式有所不同：

MySQL和标准SQL特权系统之间有一些区别。例如，在MySQL中，删除表时不会自动撤销表的特权。您必须显式发出一条REVOKE语句来撤销表的特权。有关更多信息，请参见“ REVOKE语句”。
该CAST()函数不支持强制转换为REAL或BIGINT

选择表差异

MySQL服务器不支持SELECT ... INTO TABLESybase SQL扩展。相反，MySQL Server支持INSERT INTO ... SELECT标准的SQL语法，这基本上是相同的。请参见“ INSERT ... SELECT语句”。例如：

INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

或者，您可以使用SELECT ... INTO OUTFILE或CREATE TABLE ... SELECT。

您可以使用SELECT ... INTO用户定义的变量。也可以使用游标和局部变量在存储的例程内部使用相同的语法。请参见“ SELECT ... INTO语句”。

更新差异

如果您访问表中要在表达式中更新UPDATE的列，请使用该列的当前值。以下语句中的第二个赋值设置col2为当前（更新）col1值，而不是原始col1值。结果是col1和col2具有相同的值。此行为不同于标准SQL。

UPDATE t1 SET col1 = col1 	+ 1, col2 = col1;

外键约束差异

MySQL的外键约束实现在以下关键方面与SQL标准不同：

如果父表中有几行具有相同的引用键值，则InnoDB执行外键检查，就像其他具有相同键值的父行不存在一样。例如，如果定义RESTRICT类型约束，并且子行中有多个父行，InnoDB则不允许删除任何父行。
如果ON UPDATE CASCADE或ON UPDATE SET NULL递归更新同一级联中先前已更新的同一表，则其行为类似于RESTRICT。这意味着您不能使用自引用ON UPDATE CASCADE或ON UPDATE SET NULL操作。这是为了防止级联更新导致无限循环。自引用的ON DELETE SET NULL，在另一方面，是可能的，因为是自引用ON DELETE CASCADE。级联操作嵌套的深度不得超过15层。
在插入，删除或更新许多行的SQL语句中，将逐行检查外键约束（例如唯一约束）。执行外键检查时，InnoDB在必须检查的子记录或父记录上设置共享的行级锁。MySQL立即检查外键约束；该检查不会推迟到事务提交。根据SQL标准，默认行为应推迟检查。也就是说，仅在处理了整个SQL语句之后才检查约束。这意味着不可能使用外键删除引用自身的行。
包括在内的任何存储引擎都不能InnoDB识别或强制执行MATCH引用完整性约束定义中使用的子句。使用显式MATCH子句不会产生指定的效果，并且会导致ON DELETE和ON UPDATE子句被忽略。MATCH应避免指定。
MATCHSQL标准中的子句控制NULL与引用表中的主键进行比较时如何处理复合（多列）外键中的值。MySQL本质上实现了定义的语义MATCH SIMPLE，该语义允许外键全部或部分NULL。在这种情况下，即使插入的（子表）行与引用的（父）表中的任何行都不匹配，也可以将其插入。（可以使用触发器来实现其他语义。）
MySQL出于性能原因要求对引用的列进行索引。但是，MySQL没有强制要求UNIQUE必须声明或声明所引用的列NOT NULL。
一个FOREIGN KEY引用了非约束UNIQUE关键不是标准的SQL，而是一个InnoDB扩展。的NDB存储引擎，在另一方面，要求在作为外键引用的任何列的显式唯一密钥（或主键）。
NULL对于诸如UPDATE或的操作，未很好地定义对非唯一键或包含值的键的外键引用的处理DELETE CASCADE。建议您使用仅引用UNIQUE（包括PRIMARY）和NOT NULL键的外键。
MySQL解析但忽略“内联REFERENCES规范”（如SQL标准所定义），其中引用被定义为列规范的一部分。MySQL REFERENCES仅在作为单独FOREIGN KEY规范的一部分指定时才接受子句。对于不支持外键的存储引擎（例如MyISAM），MySQL Server会解析并忽略外键规范。

'-'作为注释的开始

标准SQL使用C语法/* this is a comment */进行注释，而MySQL Server也支持该语法。MySQL还支持对此语法的扩展，如“注释语法”中所述，该扩展使特定于MySQL的SQL能够嵌入到注释中。

标准SQL使用“--”作为开始注释序列。MySQL Server #用作开始注释字符。MySQL Server还支持--注释样式的变体。即，--开始注释序列后必须跟一个空格（或一个控制字符，例如换行符）。需要该空间来防止使用以下结构的自动生成的SQL查询出现问题，在该结构中我们自动为插入付款值payment：

UPDATE account SET credit=credit-payment

考虑一下如果payment值为负，会发生什么情况-1：

UPDATE account SET credit=credit--1

credit--1是SQL中的有效表达式，但--被解释为注释的开始，部分表达式被丢弃。结果是一条语句，其含义与预期的完全不同：

UPDATE account SET credit=credit

该声明的价值完全没有变化。这说明允许以评论开头--可能会造成严重后果。

使用我们的实现需要在后面加上一个空格，--以便在MySQL Server中将其识别为开始注释序列。因此，credit--1使用安全。

另一个安全功能是mysql命令行客户端忽略以开头的行--。

MySQL如何处理约束

MySQL使您既可以使用允许回滚的事务表，也可以使用不允许回滚的非事务表。因此，MySQL中的约束处理与其他DBMS中的约束处理有所不同。当您在非事务处理表中插入或更新了很多行时，如果发生错误，则无法回滚更改，则必须处理这种情况。

基本原理是，MySQL Server会在解析要执行的语句时尝试为其检测到的所有内容产生错误，并尝试从执行该语句时发生的任何错误中恢复。在大多数情况下，我们会这样做，但并非全部。

当发生错误时，MySQL具有的选项是在中间停止语句或从问题中尽可能恢复并继续。默认情况下，服务器遵循后面的过程。例如，这意味着服务器可以将无效值强制为最接近的有效值。

有几个SQL模式选项可用来更好地控制不良数据值的处理以及在发生错误时继续执行语句还是中止操作。使用这些选项，您可以配置MySQL Server以更传统的方式运行，就像其他拒绝不正确输入的DBMS一样。可以在服务器启动时全局设置SQL模式，以影响所有客户端。各个客户端可以在运行时设置SQL模式，这使每个客户端可以选择最适合其要求的行为。请参见“服务器SQL模式”。

以下各节描述了MySQL Server如何处理不同类型的约束。

主键和唯一索引约束

通常，数据更改语句（例如INSERT或UPDATE）会发生错误，这些错误会违反主键，唯一键或外键约束。如果您使用事务存储引擎（例如）InnoDB，MySQL会自动回滚该语句。如果您使用的是非事务性存储引擎，则MySQL会在发生错误的行停止处理该语句，并保留所有未处理的行。

MySQL支持的IGNORE关键字INSERT，UPDATE等。如果使用它，MySQL将忽略主键或唯一键冲突，并继续处理下一行。请参见本节中所使用的语句（“ INSERT语句”，“ UPDATE语句”，等等）。

您可以获取有关使用mysql_info()C API函数实际插入或更新的行数的信息。您也可以使用该SHOW WARNINGS语句。请参见“ mysql_info（）”和“ SHOW WARNINGS语句”。

InnoDB和NDB表支持外键。请参见“外键约束”。

外部关键约束

外键使您可以跨表交叉引用相关数据，外键约束有助于保持此扩展数据的一致性。

MySQL支持ON UPDATE和ON DELETE外键的引用CREATE TABLE和ALTER TABLE声明。可用参照动作RESTRICT，CASCADE，SET NULL，和NO ACTION（默认值）。

SET DEFAULTMySQL服务器也支持，但是当前被拒绝为无效服务器InnoDB。由于MySQL不支持延迟约束检查，NO ACTION因此将其视为RESTRICT。有关MySQL支持的外键确切语法，请参见“外键约束”。

MATCH FULL，，MATCH PARTIAL和MATCH SIMPLE被允许，但应避免使用它们，因为它们会导致MySQL Server忽略同一语句中使用的任何ON DELETEor ON UPDATE子句。MATCHoptions在MySQL中没有任何其他作用，实际上会MATCH SIMPLE全时强制执行语义。

MySQL要求对外键列进行索引；如果创建具有外键约束但在给定列上没有索引的表，则会创建一个索引。

您可以从INFORMATION_SCHEMA.KEY_COLUMN_USAGE表中获取有关外键的信息。此处显示了针对该表的查询示例：

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
> WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;
+--------------	+---------------	+-------------	+-----------------	+
| TABLE_SCHEMA  | TABLE_NAME  | COLUMN_NAME  | CONSTRAINT_NAME  |
+--------------	+---------------	+-------------	+-----------------	+
| fk1  | myuser  | myuser_id  | f  |
| fk1  | product_order  | customer_id  | f2  |
| fk1  | product_order  | product_id  | f1  |
+--------------	+---------------	+-------------	+-----------------	+
3 rows in set (0.01 sec)

有关InnoDB表上外键的信息也可以在数据库的INNODB_FOREIGN和INNODB_FOREIGN_COLS表中找到INFORMATION_SCHEMA。

InnoDB和NDB表支持外键。

对无效数据的强制约束

默认情况下，MySQL 8.0拒绝无效或不正确的数据值，并中止出现它们的语句。可以通过禁用严格的SQL模式（请参见“服务器SQL模式”）来更改此行为，以更宽容无效值，以便服务器将其强制为有效值，以进行数据输入。不建议。

较早版本的MySQL默认情况下采用宽容行为。有关此行为的说明，请参见无效数据的约束。

ENUM和SET约束

ENUM和SET列提供了一种有效的方式来定义只能包含一组给定值的列。请参见“ ENUM类型”和“ SET类型”。

除非禁用了严格模式（不建议这样做，但请参见“服务器SQL模式”），ENUM否则a 或SETcolumn 的定义将作为对输入到该列中的值的约束。不满足以下条件的值会发生错误：

ENUM值必须是列定义中列出的值之一，或其内部等效数字。该值不能是错误值（即0或空字符串）。对于定义为一列ENUM('a','b','c')，值，如''，'d'或者'ax'是无效的，并且将被拒绝。
SET值必须是空字符串或仅由在列定义中列出的值的一个值由逗号分隔。对于定义为的列SET('a','b','c')，诸如'd'或的值'a,b,c,d'无效并且被拒绝。

如果使用INSERT IGNORE或，则可以在严格模式下抑制无效值的错误UPDATE IGNORE。在这种情况下，将生成警告而不是错误。对于ENUM，该值将作为错误成员（0）插入。对于SET，将按给定值插入，除了删除所有无效的子字符串外。例如，'a,x,b,y'结果为'a,b'。