9.2.2 标识符限定符_MySQL 5.7 参考手册

MySQL 8.0 参考手册 / 第9章语言结构 / 9.2 模式对象名称 / 9.2.2 标识符限定符

9.2.2 标识符限定符

对象名称可能是不合格的或合格的。在名称解释明确的上下文中允许使用非限定名称。限定名称至少包含一个限定符，用于通过覆盖默认上下文或提供缺失的上下文来阐明解释性上下文。

例如，此语句使用非限定名称创建一个表t1：

CREATE TABLE t1 (i INT);

因为t1不包含指定数据库的限定符，所以语句在默认数据库中创建表。如果没有默认数据库，则会发生错误。

此语句使用限定名称创建一个表 db1.t1：

CREATE TABLE db1.t1 (i INT);

因为db1.t1包含数据库限定符 db1，所以该语句 t1在名为的数据库中创建db1，而不考虑默认数据库。如果没有默认数据库，则必须指定限定符。如果存在默认数据库，则可以指定限定符以指定与默认不同的数据库，或者如果默认与指定的数据库相同则使数据库显式。

限定符具有以下特征：

非限定名称由单个标识符组成。限定名称由多个标识符组成。
多部分名称的组成部分必须用句点 ( .) 字符分隔。多部分名称的初始部分充当限定符，影响解释最终标识符的上下文。
限定符字符是一个单独的标记，不需要与关联的标识符相邻。例如， tbl_name.col_name和 tbl_name . col_name是等价的。
如果多部分名称的任何组成部分需要引用，请单独引用它们而不是引用整个名称。例如，写 `my-table`.`my-column`，而不是 `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 syntax .tbl_name means the table tbl_name in the default database.

Note

This syntax is deprecated as of MySQL 5.7.20; expect it to be removed in a future version of MySQL.

The permitted qualifiers for object names depend on the object type:

A database name is fully qualified and takes no qualifier:
```
CREATE DATABASE db1;
```

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`	Column `col_name` from whichever table used in the statement contains a column of that name
`tbl_name.col_name`	Column `col_name` from table `tbl_name` of the default database
`db_name.tbl_name.col_name`	Column `col_name` from table `tbl_name` of the database `db_name`

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;

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;

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;