【MySQL精通之路】MySQL符合的标准

下面会遇到很多不认识的标准和名词，不用着急和放弃，一点一点的啃，查阅资料，很多标准不需要过多关注，只需要了解即可。

1.概述

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

2.SQL标准

SQL标准自1986年以来一直在发展，目前已有多个版本。在本手册中，“SQL-92”是指1992年发布的标准。“SQL:1999”、“SQL:2003”、“SQLServer:2008”和“SQL:2011”是指相应年份发布的标准版本，最后一个版本为最新版本。我们使用短语“SQL标准”或“标准SQL”来表示任何时候的SQL标准的当前版本。

我们使用该产品的主要目标之一是继续努力遵守SQL标准，但不牺牲速度或可靠性。如果这大大提高了MySQL Server对我们大部分用户的可用性，我们不怕为SQL添加扩展或支持非SQL功能。HANDLER接口就是这种策略的一个例子。参见第15.2.5节“处理方声明”。

3.MySQL Server以前和现在

我们继续支持事务性和非事务性数据库，以满足关键任务的全天候使用和繁重的Web或日志使用。
MySQL Server最初设计用于小型计算机系统上的中型数据库（1000万至1亿行，即每个表约100MB）。如今，MySQL Server处理TB大小的数据库。
尽管MySQL复制功能提供了重要的功能，但我们的目标并不是实时支持。

MySQL支持ODBC级别0到3.51。

MySQL支持使用NDBCLUSTER存储引擎的高可用性数据库集群。

请参阅第25章，MySQL NDB Cluster 8.0。

我们实现了支持大部分W3C XPath标准的XML功能。请参阅第14.11节“XML函数”。

MySQL支持RFC 7159定义的本地JSON数据类型，并基于ECMAScript标准（ECMA-262）。

请参阅第13.5节“JSON数据类型”。MySQL还实现了SQL:2016标准发布前草案中指定的SQL/JSON函数的子集；有关更多信息，请参阅第14.17节“JSON函数”。

4.选择SQL模式

MySQL服务器可以在不同的SQL模式下运行，并且可以根据sql_mode系统变量的值将这些模式应用于不同的客户端。

DBA可以设置全局SQL模式以匹配站点服务器的操作要求，并且每个应用程序可以根据自己的要求设置其会话SQL模式。
模式会影响MySQL支持的SQL语法及其执行的数据验证检查。这使得在不同的环境中使用MySQL以及将MySQL与其他数据库服务器一起使用更容易。
有关设置SQL模式的更多信息，请参阅第7.1.11节“服务器SQL模式”。

5.在ANSI模式下运行MySQL

要在ANSI模式下运行MySQL Server，请使用--ANSI选项启动mysqld。在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选项还设置事务隔离级别。
请参阅第7.1.7节“服务器命令选项”。

6.MySQL对标准SQL的扩展

MySQL Server支持一些在其他SQL数据库管理系统中找不到的扩展。请注意，如果您使用它们，您的代码很可能无法移植到其他SQL服务器。在某些情况下，您可以使用以下形式的注释来编写包含MySQL扩展但仍然可移植的代码：

/*! MySQL-specific code */

在这种情况下，MySQL Server会像解析任何其他SQL语句一样解析并执行注释中的代码，但其他SQL服务器应该忽略扩展。例如，MySQL Server可以识别以下语句中的STRIGHT_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扩展。

6.1 磁盘上的数据组织

MySQL Server将每个数据库映射到MySQL数据目录下的一个目录，并将数据库中的表映射到数据库目录中的文件名。因此，在具有区分大小写文件名的操作系统（如大多数Unix系统）上的MySQL Server中，数据库和表名是区分大小写的。参见第11.2.3节“标识符大小写敏感度”。

6.2 通用语言语法

默认情况下，字符串可以用“以及”括起来。如果启用ANSI_QUOTES SQL模式，则字符串只能用“”括起来，并且服务器将用“”围起来的字符串解释为标识符。
\是字符串中的转义符。
在SQL语句中，可以使用db_name.tbl_name语法访问不同数据库中的表。某些SQL服务器提供相同的功能，但调用此用户空间。MySQL Server不支持诸如以下语句中使用的表空间：CREATE TABLE ralph.my_TABLE。。。在my_tablespace中。

6.3 SQL语句语法

ANALYZE TABLE、CHECK TABLE、OPTIMIZE TABLE和REPAIR TABLE语句。
CREATE DATABASE、DROP DATABASE和ALTER DATABASE语句。请参阅第15.1.12节“CREATE DATABASE语句”、第15.1.24节“DROP DATABASE语句“和第15.1.2节“ALTER DATABASE语句。
DO声明。
EXPLAIN SELECT以获得查询优化器如何处理表的描述。
FLUSH和RESET语句。
SET语句。参见第15.7.6.1节“变量赋值的SET语法”。
SHOW声明。参见第15.7.7节，“表演声明”。许多MySQL特定的SHOW语句产生的信息可以通过使用SELECT查询information_SCHEMA以更标准的方式获得。请参阅第28章INFORMATION_SCHEMA表格。
LOAD DATA的使用。在许多情况下，此语法与Oracle LOAD DATA兼容。参见第15.2.9节“LOAD DATA语句”。
RENAME表格的使用。参见第15.1.36节“RENAME表格声明”。
使用REPLACE而不是DELETE加INSERT。参见第15.2.12节，“REPLACE声明”。
在ALTER TABLE语句中使用CHANGE col_name、DROP col_name或DROP INDEX、IGNORE或RENAME。在ALTER TABLE语句中使用多个ADD、ALTER、DROP或CHANGE子句。请参阅第15.1.9节“ALTER TABLE语句”。
在CREATE TABLE语句中使用索引名称、列前缀上的索引以及index或KEY。参见第15.1.20节“CREATE TABLE语句”。
与CREATE TABLE一起使用TEMPORARY或IF NOT。
将IF EXISTS与DROP TABLE和DROP DATABASE一起使用。
使用单个DROP TABLE语句删除多个表的功能。
UPDATE和DELETE语句的ORDER BY和LIMIT子句。
插入tbl_name SET col_name=。。。语法。
INSERT和REPLACE语句的DELAYED子句。
INSERT、REPLACE、DELETE和UPDATE语句的LOW_PRIORITY子句。
在SELECT语句中使用INTO OUTFILE或INTO DUMPFILE。参见第15.2.13节“SELECT语句”。
SELECT语句中的STRIGHT_JOIN或SQL_SMALL_RESULT等选项。
您不需要在GROUP BY子句中命名所有选定的列。这为一些非常具体但非常正常的查询提供了更好的性能。参见第14.19节“聚合函数”。
可以使用GROUP BY指定ASC和DESC，而不仅仅是使用ORDER BY。
使用：=赋值运算符在语句中设置变量的能力。参见第11.4节“用户定义变量”。

6.4 数据类型

MEDIUMINT、SET和ENUM数据类型，以及各种BLOB和TEXT数据类型。
AUTO_INCREMENT、BINARY、NULL、UNSIGNED和ZEROFILL数据类型属性。

6.5 功能和操作

为了方便从其他SQL环境迁移的用户，MySQL Server支持许多函数的别名。

例如，所有字符串函数都支持标准SQL语法和ODBC语法。

MySQL Server将||和&&运算符理解为逻辑OR和and，就像在C编程语言中一样。

在MySQL Server中，||和OR是同义词，&&和and也是同义词。

由于这种良好的语法，MySQL Server不支持标准的SQL ||运算符进行字符串连接；

请改用CONCAT（）。

因为CONCAT（）接受任意数量的参数，所以很容易将||运算符的使用转换为MySQL Server。

使用COUNT（DISTINCT value_list），其中value_list有多个元素。

默认情况下，字符串比较不区分大小写，排序顺序由当前字符集的排序规则决定，默认情况下为utf8mb4。

要执行区分大小写的比较

您应该使用BINARY属性声明列，或者使用BINARY强制转换，这会导致使用基础字符代码值而不是词法排序来进行比较。

%运算符是MOD（）的同义词。也就是说，N%M等于MOD（N，M）。%支持C程序员以及与PostgreSQL的兼容性。

=、<>、<=、<、>=、>、<<、>>、<=>、AND、OR或LIKE运算符可用于SELECT语句中输出列列表（FROM左侧）的表达式中。

mysql> SELECT col1=1 AND col2=2 FROM my_table;

 LAST_INSERT_ID（）函数返回最新的AUTO_INCREMENT值。参见第14.15节“信息功能”。

允许对数值使用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_CONNCT（）。参见第14.19节“聚合函数”。