MySQL :: MySQL 8.4 参考手册 :: 11.2.2 标识符限定符

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 11 语言结构 / 11.2 模式对象名称 / 11.2.2 标识符限定符

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

11.2.2 标识符限定符11.2.2 Identifier Qualifiers

对象名称可以是未限定的或限定的。在名称的解释没有歧义的情况下，允许使用未限定名称。限定名称至少包含一个限定符，以通过覆盖默认上下文或提供缺失的上下文来明确解释的环境。Object names may be unqualified or qualified. An unqualified name is permitted in contexts where interpretation of the name is unambiguous. A qualified name includes at least one qualifier to clarify the interpretive context by overriding a default context or providing missing context.

例如，该语句使用未限定的名称 t1 创建一个表：For example, this statement creates a table using the unqualified name t1:

CREATE TABLE t1 (i INT);

由于 t1 没有包含指定数据库的限定符，该语句将在默认数据库中创建表。如果没有默认数据库，则会发生错误。Because t1 includes no qualifier to specify a database, the statement creates the table in the default database. If there is no default database, an error occurs.

该语句使用限定名称 db1.t1 创建一个表：This statement creates a table using the qualified name db1.t1:

CREATE TABLE db1.t1 (i INT);

因为 db1.t1 包含数据库限定符 db1，该语句会在名为 db1 的数据库中创建 t1，而不管默认数据库是什么。如果没有默认数据库，则必须指定限定符。如果存在默认数据库，则可以指定限定符，以选择与默认数据库不同的数据库，或在默认数据库与指定数据库相同的情况下明确指定数据库。Because db1.t1 includes a database qualifier db1, the statement creates t1 in the database named db1, regardless of the default database. The qualifier must be specified if there is no default database. The qualifier may be specified if there is a default database, to specify a database different from the default, or to make the database explicit if the default is the same as the one specified.

限定符具有以下特性：Qualifiers have these characteristics:

未限定的名称由单个标识符组成。限定名称由多个标识符组成。An unqualified name consists of a single identifier. A qualified name consists of multiple identifiers.
多部分名称的各个部分必须用句点（.）字符分隔。多部分名称的初始部分充当限定符，会影响解释最终标识符的上下文。The components of a multiple-part name must be separated by period (.) characters. The initial parts of a multiple-part name act as qualifiers that affect the context within which to interpret the final identifier.
限定符字符是一个独立的标记，不必与相关标识符相邻。例如，tbl_name.col_name 和 tbl_name . col_name 是等效的。The qualifier character is a separate token and need not be contiguous with the associated identifiers. For example, tbl_name.col_name and tbl_name . col_name are equivalent.
如果多部分名称的任何部分需要加引号，应分别加引号，而不是对整个名称加引号。例如，应写 `my-table`.`my-column`，而不是 `my-table.my-column`。If any components of a multiple-part name require quoting, quote them individually rather than quoting the name as a whole. For example, write `my-table`.`my-column`, not `my-table.my-column`.
在限定名称中，紧跟句点的保留字必须是标识符，因此在该上下文中不需要加引号。A reserved word that follows a period in a qualified name must be an identifier, so in that context it need not be quoted.

对象名称允许的限定符取决于对象类型：The permitted qualifiers for object names depend on the object type:

数据库名称是完全限定的，不需要限定符：A database name is fully qualified and takes no qualifier:
```
CREATE DATABASE db1;
```
表、视图或存储程序名称可以指定数据库名称限定符。在 CREATE 语句中未限定和已限定名称的示例如下：A table, view, or stored program name may be given a database-name qualifier. Examples of unqualified and qualified names in CREATE statements:
```
CREATE TABLE mytable ...;
CREATE VIEW myview ...;
CREATE PROCEDURE myproc ...;
CREATE FUNCTION myfunc ...;
CREATE EVENT myevent ...;

CREATE TABLE mydb.mytable ...;
CREATE VIEW mydb.myview ...;
CREATE PROCEDURE mydb.myproc ...;
CREATE FUNCTION mydb.myfunc ...;
CREATE EVENT mydb.myevent ...;
```
触发器与表相关联，因此任何限定符都适用于表名：A trigger is associated with a table, so any qualifier applies to the table name:
```
CREATE TRIGGER mytrigger ... ON mytable ...;

CREATE TRIGGER mytrigger ... ON mydb.mytable ...;
```

可以给列名提供多个限定符，以在引用它的语句中指示上下文，如下表所示。A column name may be given multiple qualifiers to indicate context in statements that reference it, as shown in the following table.

列引用Column Reference	意义Meaning
`col_name``col_name`	在语句中使用的任意表中的列 `col_name` 包含该名称的列Column `col_name` from whichever table used in the statement contains a column of that name
`tbl_name.col_name``tbl_name.col_name`	默认数据库中表 `tbl_name` 的列 `col_name`Column `col_name` from table `tbl_name` of the default database
`db_name.tbl_name.col_name``db_name.tbl_name.col_name`	数据库 `db_name` 的表 `tbl_name` 中的列 `col_name`Column `col_name` from table `tbl_name` of the database `db_name`

换句话说，列名可以带表名限定符，而表名本身也可以带数据库名限定符。在 SELECT 语句中未限定和限定列引用的例子：In other words, a column name may be given a table-name qualifier, which itself may be given a database-name qualifier. Examples of unqualified and qualified column references in SELECT statements:

SELECT c1 FROM mytable
WHERE c2 > 100;

SELECT mytable.c1 FROM mytable
WHERE mytable.c2 > 100;

SELECT mydb.mytable.c1 FROM mydb.mytable
WHERE mydb.mytable.c2 > 100;

除非未限定的引用存在歧义，否则在语句中无需为对象引用指定限定符。假设列 c1 只出现在表 t1 中，c2 只出现在 t2 中，而 c 同时出现在 t1 和 t2 中。在同时引用这两个表的语句中，对 c 的任何未限定引用都是歧义的，必须限定为 t1.c 或 t2.c 以指明你指的是哪一个表：You need not specify a qualifier for an object reference in a statement unless the unqualified reference is ambiguous. Suppose that column c1 occurs only in table t1, c2 only in t2, and c in both t1 and t2. Any unqualified reference to c is ambiguous in a statement that refers to both tables and must be qualified as t1.c or t2.c to indicate which table you mean:

SELECT c1, c2, t1.c FROM t1 INNER JOIN t2
WHERE t2.c > 100;

类似地，要在同一语句中从数据库 db1 的表 t 和数据库 db2 的表 t 中检索数据，必须限定表的引用：对于这些表中的列引用，仅对在两个表中都出现的列名需要限定。假设列 c1 只出现在表 db1.t 中，列 c2 只出现在表 db2.t 中，而列 c 同时出现在 db1.t 和 db2.t 中。在这种情况下，c 是模糊的，必须限定，而 c1 和 c2 则不需要：Similarly, to retrieve from a table t in database db1 and from a table t in database db2 in the same statement, you must qualify the table references: For references to columns in those tables, qualifiers are required only for column names that appear in both tables. Suppose that column c1 occurs only in table db1.t, c2 only in db2.t, and c in both db1.t and db2.t. In this case, c is ambiguous and must be qualified but c1 and c2 need not be:

SELECT c1, c2, db1.t.c FROM db1.t INNER JOIN db2.t
WHERE db2.t.c > 100;

表别名使限定列引用可以更简单地书写：Table aliases enable qualified column references to be written more simply:

SELECT c1, c2, t1.c FROM db1.t AS t1 INNER JOIN db2.t AS t2
WHERE t2.c > 100;

首页上一级下一节