MySql 中文参考指南

1.7 MySQL Standards Compliance

本节描述了 MySQL 如何与 ANSI/ISO SQL 标准相关。MySQL Server 对 SQL 标准进行了许多扩展，您可以在此处了解它们是什么以及如何使用它们。您还可以找到有关 MySQL Server 缺少的功能的信息，以及如何解决一些差异。

This section describes how MySQL relates to the ANSI/ISO SQL standards. MySQL Server has many extensions to the SQL standard, and here you can find out what they are and how to use them. You can also find information about functionality missing from MySQL Server, and how to work around some of the differences.

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

The SQL standard has been evolving since 1986 and several versions exist. In this manual, “SQL-92” refers to the standard released in 1992. “SQL:1999”, “SQL:2003”, “SQL:2008”, and “SQL:2011” refer to the versions of the standard released in the corresponding years, with the last being the most recent version. We use the phrase “the SQL standard” or “standard SQL” to mean the current version of the SQL Standard at any time.

该产品的我们的主要目标之一是继续致力于遵循 SQL 标准，但不会牺牲速度或可靠性。如果这对我们的大量用户群体极大地提高了 MySQL Server 的可用性，我们不害怕向 SQL 添加扩展功能或支持非 SQL 功能。 HANDLER 接口是这一策略的一个示例。参见 Section 15.2.5, “HANDLER Statement” 。

One of our main goals with the product is to continue to work toward compliance with the SQL standard, but without sacrificing speed or reliability. We are not afraid to add extensions to SQL or support for non-SQL features if this greatly increases the usability of MySQL Server for a large segment of our user base. The HANDLER interface is an example of this strategy. See Section 15.2.5, “HANDLER Statement”.

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

We continue to support transactional and nontransactional databases to satisfy both mission-critical 24/7 usage and heavy Web or logging usage.

MySQL 服务器最初设计用于小型计算机系统上的中型数据库（1000-1 亿行，或每个表约 100MB）。现在，MySQL 服务器能处理 TB 级的数据库。

MySQL Server was originally designed to work with medium-sized databases (10-100 million rows, or about 100MB per table) on small computer systems. Today MySQL Server handles terabyte-sized databases.

我们没有定位实时支持，虽然 MySQL 复制功能提供了重要的功能。

We are not targeting real-time support, although MySQL replication capabilities offer significant functionality.

MySQL 支持 ODBC 级别 0 到 3.51。

MySQL supports ODBC levels 0 to 3.51.

MySQL 使用 NDBCLUSTER 存储引擎支持高可用性数据库集群。参见 Chapter 25, MySQL NDB Cluster 9.0 。

MySQL supports high-availability database clustering using the NDBCLUSTER storage engine. See Chapter 25, MySQL NDB Cluster 9.0.

我们实现了支持 W3C XPath 标准的大多数内容的 XML 功能。参见 Section 14.11, “XML Functions”。

We implement XML functionality which supports most of the W3C XPath standard. See Section 14.11, “XML Functions”.

MySQL 支持 RFC 7159 定义的基于 ECMAScript 标准 (ECMA-262) 的原生 JSON 数据类型。参见 Section 13.5, “The JSON Data Type”。MySQL 还实现了 SQL:2016 标准预发布草案中指定的一部分 SQL/JSON 函数；有关更多信息，请参见 Section 14.17, “JSON Functions”。

MySQL supports a native JSON data type as defined by RFC 7159, and based on the ECMAScript standard (ECMA-262). See Section 13.5, “The JSON Data Type”. MySQL also implements a subset of the SQL/JSON functions specified by a pre-publication draft of the SQL:2016 standard; see Section 14.17, “JSON Functions”, for more information.

Selecting SQL Modes

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

The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements.

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

Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.

有关设置 SQL 模式的更多信息，请参见 Section 7.1.11, “Server SQL Modes”。

For more information on setting the SQL mode, see Section 7.1.11, “Server SQL Modes”.

Running MySQL in ANSI Mode

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

To run MySQL Server in ANSI mode, start mysqld with the —ansi option. Running the server in ANSI mode is the same as starting it with the following options:

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

如需在运行时实现相同的效果，请执行以下两个语句：

To achieve the same effect at runtime, execute these two statements:

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

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

You can see that setting the sql_mode system variable to 'ANSI' enables all SQL mode options that are relevant for ANSI mode as follows:

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 选项还设置了事务隔离级别。

Running the server in ANSI mode with —ansi is not quite the same as setting the SQL mode to 'ANSI' because the —ansi option also sets the transaction isolation level.

参见 Section 7.1.7, “Server Command Options”。

See Section 7.1.7, “Server Command Options”.