MySql 中文参考指南

1.7.1 MySQL Extensions to Standard SQL

MySQL Server 支持一些其他 SQL DBMS 中不太可能找到的扩展。需要注意的是，如果您使用这些扩展，那么您的代码极有可能不能移植到其他 SQL 服务器。在某些情况下，您可以通过使用如下形式的注释编写包含 MySQL 扩展但仍可移植的代码：

MySQL Server supports some extensions that you are not likely to find in other SQL DBMSs. Be warned that if you use them, your code is most likely not portable to other SQL servers. In some cases, you can write code that includes MySQL extensions, but is still portable, by using comments of the following form:

/*! MySQL-specific code */

在此情况下，MySQL Server 会解析并执行注释中的代码，就像它会执行任何其他 SQL 语句一样，但其他 SQL 服务器应忽略这些扩展。例如，MySQL Server 会识别以下语句中的 STRAIGHT_JOIN 关键字，但其他服务器不应识别：

In this case, MySQL Server parses and executes the code within the comment as it would any other SQL statement, but other SQL servers should ignore the extensions. For example, MySQL Server recognizes the STRAIGHT_JOIN keyword in the following statement, but other servers should not:

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

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

If you add a version number after the ! character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number. The KEY_BLOCK_SIZE clause in the following comment is executed only by servers from MySQL 5.1.10 or higher:

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

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

The following descriptions list MySQL extensions, organized by category.

  1. Organization of data on disk

MySQL Server 将每个数据库映射到 MySQL 数据目录下的一个目录，并将数据库中的表映射到数据库目录中的文件名称。因此，在文件名称区分大小写的操作系统（如大多数 Unix 系统）上的 MySQL Server 中，数据库名称和小写字母敏感。请参阅 Section 11.2.3, “Identifier Case Sensitivity”

MySQL Server maps each database to a directory under the MySQL data directory, and maps tables within a database to file names in the database directory. Consequently, database and table names are case-sensitive in MySQL Server on operating systems that have case-sensitive file names (such as most Unix systems). See Section 11.2.3, “Identifier Case Sensitivity”.

  1. General language syntax

默认情况下，字符串可以用 " 和 ' 括起来。如果启用了 ANSI_QUOTES SQL 模式，则字符串只能用 ' 括起来，并且服务器会将用 " 括起来的字符串解释为标识符。

By default, strings can be enclosed by " as well as '. If the ANSI_QUOTES SQL mode is enabled, strings can be enclosed only by ' and the server interprets strings enclosed by " as identifiers.

_\_是字符串中的转义符。

\ is the escape character in strings.

在 SQL 语句中，你可以使用 db_name.tbl_name 语法访问来自不同数据库的表。一些 SQL 服务器提供相同的功能，但称之为 User space 。MySQL Server 不支持此类语句中使用的表空间： CREATE TABLE ralph.my_table …​ IN my_tablespace 。

In SQL statements, you can access tables from different databases with the db_name.tbl_name syntax. Some SQL servers provide the same functionality but call this User space. MySQL Server doesn’t support tablespaces such as used in statements like this: CREATE TABLE ralph.my_table …​ IN my_tablespace.

  1. By default, strings can be enclosed by " as well as '. If the ANSI_QUOTES SQL mode is enabled, strings can be enclosed only by ' and the server interprets strings enclosed by " as identifiers.

  2. \ is the escape character in strings.

  3. In SQL statements, you can access tables from different databases with the db_name.tbl_name syntax. Some SQL servers provide the same functionality but call this User space. MySQL Server doesn’t support tablespaces such as used in statements like this: CREATE TABLE ralph.my_table …​ IN my_tablespace.

  4. SQL statement syntax

ANALYZE TABLE 、 CHECK TABLE 、 OPTIMIZE TABLE 和 REPAIR TABLE 语句。

The ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements.

CREATE DATABASE 、 DROP DATABASE 和 ALTER DATABASE 语句。请参阅 Section 15.1.12, “CREATE DATABASE Statement” 、 Section 15.1.24, “DROP DATABASE Statement” 和 Section 15.1.2, “ALTER DATABASE Statement” 。

The CREATE DATABASE, DROP DATABASE, and ALTER DATABASE statements. See Section 15.1.12, “CREATE DATABASE Statement”, Section 15.1.24, “DROP DATABASE Statement”, and Section 15.1.2, “ALTER DATABASE Statement”.

DO 语句。

The DO statement.

EXPLAIN SELECT ，用于获取有关查询优化器如何处理表的说明。

EXPLAIN SELECT to obtain a description of how tables are processed by the query optimizer.

FLUSH 和 RESET 语句。

The FLUSH and RESET statements.

SET 语句。请参阅 Section 15.7.6.1, “SET Syntax for Variable Assignment” 。

The SET statement. See Section 15.7.6.1, “SET Syntax for Variable Assignment”.

SHOW 语句。请参阅 Section 15.7.7, “SHOW Statements” 。可以通过使用 SELECT 查询 INFORMATION_SCHEMA 以更标准的方式来获取许多特定于 MySQL 的 SHOW 语句产生的信息。请参阅 Chapter 28, INFORMATION_SCHEMA Tables 。

The SHOW statement. See Section 15.7.7, “SHOW Statements”. The information produced by many of the MySQL-specific SHOW statements can be obtained in more standard fashion by using SELECT to query INFORMATION_SCHEMA. See Chapter 28, INFORMATION_SCHEMA Tables.

LOAD DATA 的用法。在很多情况下，此语法与 Oracle LOAD DATA 兼容。请参阅 Section 15.2.9, “LOAD DATA Statement” 。

Use of LOAD DATA. In many cases, this syntax is compatible with Oracle LOAD DATA. See Section 15.2.9, “LOAD DATA Statement”.

RENAME TABLE 的用法。请参阅 Section 15.1.36, “RENAME TABLE Statement” 。

Use of RENAME TABLE. See Section 15.1.36, “RENAME TABLE Statement”.

在 ALTER TABLE 语句中使用 REPLACE 代替 DELETE 加 INSERT 。请参阅 Section 15.2.12, “REPLACE Statement” 。

Use of REPLACE instead of DELETE plus INSERT. See Section 15.2.12, “REPLACE Statement”.

在 ALTER TABLE 语句中使用 CHANGE _col_name, DROP _col_name, or DROP INDEX 、 IGNORE 或 RENAME 。在 link:alter-table.html[ALTER TABLE 语句中使用多个 ADD 、 ALTER 、 DROP 或 CHANGE 子句。请参阅 Section 15.1.9, “ALTER TABLE Statement” 。

Use of CHANGE _col_name, _DROP _col_name, or _DROP INDEX, IGNORE or RENAME in ALTER TABLE statements. Use of multiple ADD, ALTER, DROP, or CHANGE clauses in an ALTER TABLE statement. See Section 15.1.9, “ALTER TABLE Statement”.

在 CREATE TABLE 语句中使用索引名称、前缀部分列上的索引，以及使用 INDEX 或 KEY 。请参阅 Section 15.1.20, “CREATE TABLE Statement” 。

Use of index names, indexes on a prefix of a column, and use of INDEX or KEY in CREATE TABLE statements. See Section 15.1.20, “CREATE TABLE Statement”.

在 CREATE TABLE 中使用 TEMPORARY 或 IF NOT EXISTS 。

Use of TEMPORARY or IF NOT EXISTS with CREATE TABLE.

在 DROP TABLE 和 DROP DATABASE 中使用 IF EXISTS 。

Use of IF EXISTS with DROP TABLE and DROP DATABASE.

通过单个 DROP TABLE 语句来删除多表的可能性。

The capability of dropping multiple tables with a single DROP TABLE statement.

UPDATE 和 DELETE 语句的 ORDER BY 和 LIMIT 子句。

The ORDER BY and LIMIT clauses of the UPDATE and DELETE statements.

INSERT INTO _tbl_name SET col_name = …​ 语法。

INSERT INTO _tbl_name SET col_name = …​_ syntax.

INSERT 和 REPLACE 语句的 DELAYED 子句。

The DELAYED clause of the INSERT and REPLACE statements.

INSERT 、 REPLACE 、 DELETE 和 UPDATE 语句的 LOW_PRIORITY 子句。

The LOW_PRIORITY clause of the INSERT, REPLACE, DELETE, and UPDATE statements.

在 SELECT 语句中使用 INTO OUTFILE 或 INTO DUMPFILE 。请参阅 Section 15.2.13, “SELECT Statement” 。

Use of INTO OUTFILE or INTO DUMPFILE in SELECT statements. See Section 15.2.13, “SELECT Statement”.

SELECT 语句中如 STRAIGHT_JOIN 或 SQL_SMALL_RESULT 等选项。

Options such as STRAIGHT_JOIN or SQL_SMALL_RESULT in SELECT statements.

无需在 GROUP BY 子句中命名所有选定的列。针对一些非常具体但相当正常的查询，这样做可以提供更好的性能。请参阅 Section 14.19, “Aggregate Functions” 。

You don’t need to name all selected columns in the GROUP BY clause. This gives better performance for some very specific, but quite normal queries. See Section 14.19, “Aggregate Functions”.

可以将 ASC 和 DESC 与 GROUP BY 配合使用，而不仅限于 ORDER BY。

You can specify ASC and DESC with GROUP BY, not just with ORDER BY.

能够在包含 := 赋值运算符的语句中设置变量。请参阅 Section 11.4, “User-Defined Variables”。

The ability to set variables in a statement with the := assignment operator. See Section 11.4, “User-Defined Variables”.

  1. The ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements.

  2. The CREATE DATABASE, DROP DATABASE, and ALTER DATABASE statements. See Section 15.1.12, “CREATE DATABASE Statement”, Section 15.1.24, “DROP DATABASE Statement”, and Section 15.1.2, “ALTER DATABASE Statement”.

  3. The DO statement.

  4. EXPLAIN SELECT to obtain a description of how tables are processed by the query optimizer.

  5. The FLUSH and RESET statements.

  6. The SET statement. See Section 15.7.6.1, “SET Syntax for Variable Assignment”.

  7. The SHOW statement. See Section 15.7.7, “SHOW Statements”. The information produced by many of the MySQL-specific SHOW statements can be obtained in more standard fashion by using SELECT to query INFORMATION_SCHEMA. See Chapter 28, INFORMATION_SCHEMA Tables.

  8. Use of LOAD DATA. In many cases, this syntax is compatible with Oracle LOAD DATA. See Section 15.2.9, “LOAD DATA Statement”.

  9. Use of RENAME TABLE. See Section 15.1.36, “RENAME TABLE Statement”.

  10. Use of REPLACE instead of DELETE plus INSERT. See Section 15.2.12, “REPLACE Statement”.

  11. Use of CHANGE _col_name, _DROP _col_name, or _DROP INDEX, IGNORE or RENAME in ALTER TABLE statements. Use of multiple ADD, ALTER, DROP, or CHANGE clauses in an ALTER TABLE statement. See Section 15.1.9, “ALTER TABLE Statement”.

  12. Use of index names, indexes on a prefix of a column, and use of INDEX or KEY in CREATE TABLE statements. See Section 15.1.20, “CREATE TABLE Statement”.

  13. Use of TEMPORARY or IF NOT EXISTS with CREATE TABLE.

  14. Use of IF EXISTS with DROP TABLE and DROP DATABASE.

  15. The capability of dropping multiple tables with a single DROP TABLE statement.

  16. The ORDER BY and LIMIT clauses of the UPDATE and DELETE statements.

  17. INSERT INTO _tbl_name SET col_name = …​_ syntax.

  18. The DELAYED clause of the INSERT and REPLACE statements.

  19. The LOW_PRIORITY clause of the INSERT, REPLACE, DELETE, and UPDATE statements.

  20. Use of INTO OUTFILE or INTO DUMPFILE in SELECT statements. See Section 15.2.13, “SELECT Statement”.

  21. Options such as STRAIGHT_JOIN or SQL_SMALL_RESULT in SELECT statements.

  22. You don’t need to name all selected columns in the GROUP BY clause. This gives better performance for some very specific, but quite normal queries. See Section 14.19, “Aggregate Functions”.

  23. You can specify ASC and DESC with GROUP BY, not just with ORDER BY.

  24. The ability to set variables in a statement with the := assignment operator. See Section 11.4, “User-Defined Variables”.

  25. Data types

MEDIUMINT 、 SET 和 ENUM 数据类型，以及各种 BLOB 和 TEXT 数据类型。

The MEDIUMINT, SET, and ENUM data types, and the various BLOB and TEXT data types.

AUTO_INCREMENT、BINARY、NULL、UNSIGNED 和 ZEROFILL 数据类型属性。

The AUTO_INCREMENT, BINARY, NULL, UNSIGNED, and ZEROFILL data type attributes.

  1. The MEDIUMINT, SET, and ENUM data types, and the various BLOB and TEXT data types.

  2. The AUTO_INCREMENT, BINARY, NULL, UNSIGNED, and ZEROFILL data type attributes.

  3. Functions and operators

为了方便从其他 SQL 环境迁移的用户，MySQL Server 支持许多函数的别名。例如，所有字符串函数均支持标准 SQL 语法和 ODBC 语法。

To make it easier for users who migrate from other SQL environments, MySQL Server supports aliases for many functions. For example, all string functions support both standard SQL syntax and ODBC syntax.

MySQL Server 将 || 和 && 运算符理解为逻辑 OR 和 AND，如 C 编程语言中。在 MySQL Server 中， || 和 OR 是同义词， && 和 AND 也是同义词。由于此语法很好，因此 MySQL Server 不支持表示字符串连接的标准 SQL || 运算符；改用 CONCAT() 。由于 CONCAT() 接受任意数量的参数，因此可轻松将 || 运算符的使用转换为 MySQL Server。

MySQL Server understands the || and && operators to mean logical OR and AND, as in the C programming language. In MySQL Server, || and OR are synonyms, as are && and AND. Because of this nice syntax, MySQL Server doesn’t support the standard SQL || operator for string concatenation; use CONCAT() instead. Because CONCAT() takes any number of arguments, it is easy to convert use of the || operator to MySQL Server.

使用链接：aggregate-functions.html#function_count[ COUNT(DISTINCT _value_list ) ] where _value_list 具有多个元素。

Use of COUNT(DISTINCT _value_list) where _value_list has more than one element.

字符串比较默认情况下不区分大小写，排序取决于当前字符集的排序规则，默认情况下为 utf8mb4。要改为执行区分大小写的比较，应使用 BINARY 属性声明列或使用 BINARY 强制转换，这会导致使用基础字符代码值而非词汇顺序进行比较。

String comparisons are case-insensitive by default, with sort ordering determined by the collation of the current character set, which is utf8mb4 by default. To perform case-sensitive comparisons instead, you should declare your columns with the BINARY attribute or use the BINARY cast, which causes comparisons to be done using the underlying character code values rather than a lexical ordering.

% 运算符是 MOD() 的同义词。也就是说， N % M 等效于 MOD(_N,M). %_ ，这对于 C 程序员以及与 PostgreSQL 的兼容性来说都是支持的。

The % operator is a synonym for MOD(). That is, N_ % M is equivalent to _MOD(_N,M). %_ is supported for C programmers and for compatibility with PostgreSQL.

[role="bare"]comparison-operators.html#operator_equal, <> 、 ⇐ 、 < 、 >= 、 > 、 << link:bit-functions.html#operator_right-shift[,>>] 、 <⇒ 、 AND 、 OR 或 LIKE 运算符可在 SELECT 语句中输出列列表中的表达式（在 FROM 的左侧）中使用。例如: mysql> SELECT col1=1 AND col2=2 FROM my_table; LAST_INSERT_ID() 函数返回最新的 AUTO_INCREMENT 值。请参阅 Section 14.15, “Information Functions” 。

The [role="bare"]comparison-operators.html#operator_equal, <>, ⇐, <, >=, >, ], <⇒, AND, OR, or LIKE operators may be used in expressions in the output column list (to the left of the FROM) in SELECT statements. For example: mysql> SELECT col1=1 AND col2=2 FROM my_table; The LAST_INSERT_ID() function returns the most recent AUTO_INCREMENT value. See Section 14.15, “Information Functions”.

LIKE 允许用于数值。

LIKE is permitted on numeric values.

REGEXP 和 NOT REGEXP 扩展正则表达式运算符。

The REGEXP and NOT REGEXP extended regular expression operators.

CONCAT() 或 CHAR() 伴有一个或多个参数。（在 MySQL Server 中，这些函数可以采用可变数量的参数。）

CONCAT() or CHAR() with one argument or more than two arguments. (In MySQL Server, these functions can take a variable number of arguments.)

BIT_COUNT() 、 CASE 、 ELT() 、 FROM_DAYS() 、 FORMAT() 、 IF() 、 MD5() 、 PERIOD_ADD() 、 PERIOD_DIFF() 、 TO_DAYS() 和 WEEKDAY() 函数。

The BIT_COUNT(), CASE, ELT(), FROM_DAYS(), FORMAT(), IF(), MD5(), PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(), and WEEKDAY() functions.

使用 TRIM() 修剪子字符串。标准 SQL 仅支持删除单个字符。

Use of TRIM() to trim substrings. Standard SQL supports removal of single characters only.

GROUP BY 函数 STD() 、 BIT_OR() 、 BIT_AND() 、 BIT_XOR() 和 GROUP_CONCAT() 。请参阅 Section 14.19, “Aggregate Functions” 。

The GROUP BY functions STD(), BIT_OR(), BIT_AND(), BIT_XOR(), and GROUP_CONCAT(). See Section 14.19, “Aggregate Functions”.

  1. To make it easier for users who migrate from other SQL environments, MySQL Server supports aliases for many functions. For example, all string functions support both standard SQL syntax and ODBC syntax.

  2. MySQL Server understands the || and && operators to mean logical OR and AND, as in the C programming language. In MySQL Server, || and OR are synonyms, as are && and AND. Because of this nice syntax, MySQL Server doesn’t support the standard SQL || operator for string concatenation; use CONCAT() instead. Because CONCAT() takes any number of arguments, it is easy to convert use of the || operator to MySQL Server.

  3. Use of COUNT(DISTINCT _value_list) where _value_list has more than one element.

  4. String comparisons are case-insensitive by default, with sort ordering determined by the collation of the current character set, which is utf8mb4 by default. To perform case-sensitive comparisons instead, you should declare your columns with the BINARY attribute or use the BINARY cast, which causes comparisons to be done using the underlying character code values rather than a lexical ordering.

  5. The % operator is a synonym for MOD(). That is, N_ % M is equivalent to _MOD(_N,M). %_ is supported for C programmers and for compatibility with PostgreSQL.

  6. The [role="bare"]comparison-operators.html#operator_equal, <>, ⇐, <, >=, >, ], <⇒, AND, OR, or LIKE operators may be used in expressions in the output column list (to the left of the FROM) in SELECT statements. For example: mysql> SELECT col1=1 AND col2=2 FROM my_table;

  7. The LAST_INSERT_ID() function returns the most recent AUTO_INCREMENT value. See Section 14.15, “Information Functions”.

  8. LIKE is permitted on numeric values.

  9. The REGEXP and NOT REGEXP extended regular expression operators.

  10. CONCAT() or CHAR() with one argument or more than two arguments. (In MySQL Server, these functions can take a variable number of arguments.)

  11. The BIT_COUNT(), CASE, ELT(), FROM_DAYS(), FORMAT(), IF(), MD5(), PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(), and WEEKDAY() functions.

  12. Use of TRIM() to trim substrings. Standard SQL supports removal of single characters only.

  13. The GROUP BY functions STD(), BIT_OR(), BIT_AND(), BIT_XOR(), and GROUP_CONCAT(). See Section 14.19, “Aggregate Functions”.