Skip to main content

Schema object names

Schema objects include databases, tables, indexes, columns, aliases, views, stored procedures, partitions, and tablespaces. These objects are collectively referred to as identifiers.

Identifier quotation​

The quotation character for identifiers is the backtick (`). Identifiers can be quoted or unquoted. If an identifier contains special characters or is a reserved word, it must be quoted.

SELECT * FROM `table` WHERE `table`.id > 10;

Internally, identifiers are converted and stored as Unicode (UTF-8). Identifiers can contain the following characters:

  • Characters allowed in unquoted identifiers:

    • ASCII: [0-9,a-z,A-Z$_] (ASCII: [0-9, a-z, A-Z, $, _])

    • Extended characters: U+0080 .. U+FFFF

  • Characters allowed in quoted identifiers include the entire Unicode BMP, except for U+0000:

    • ASCII: U+0001 .. U+007F

    • Extended characters: U+0080 .. U+FFFF

  • ASCII NUL (U+0000) and supplementary characters (U+10000 and above) are not allowed in quoted or unquoted identifiers.

  • Identifiers can start with a number, but cannot consist solely of numbers unless quoted.

  • Database, table, and column names cannot end with a space character.

If you quote an identifier, the quotation character can be included in the identifier. If the character included in the identifier is the same as the quotation character used to quote the identifier, you must quote the identifier twice.

For example, you can create a table named a"b with a column named c`d:

CREATE TABLE `a"b` (`c``d` INT);
Query OK, 0 rows affected (0.080 sec)

When you reference an alias in other parts of a statement, you must quote the alias. Otherwise, it will be treated as a string literal.

SELECT 1 AS `one`, 2 AS 'two';
+-----+-----+
| one | two |
+-----+-----+
|   1 |   2 |
+-----+-----+
1 row in set (0.001 sec)

Qualifiers for identifiers​

Object names can be qualified or unqualified (omitted). A qualified name must include at least one qualifier.

For example, you can create a table named t1 without specifying a database:

CREATE TABLE t1 (c INT);
Query OK, 0 rows affected (0.071 sec)

Since t1 does not include a qualifier to specify the database, the statement creates the table in the default database. If no default database exists, an error occurs.

For example, you can create a table named ob1.t1 by specifying the database qualifier ob1:

CREATE TABLE ob1.t1 (c INT);
Query OK, 0 rows affected (0.061 sec)

Because ob1.t1 includes the database qualifier ob1, the statement creates the table t1 in the database ob1. If no default database exists, you must specify the qualifier. If a default database exists, you can specify a non-default database by using the qualifier.

Qualifiers have the following characteristics:

  • An unqualified name consists of a single identifier. A qualified name consists of multiple identifiers.

  • Identifiers and qualifiers are separated by a period (.) character.

  • A qualifier is an independent token. Identifiers and qualifiers can be separated by spaces. For example, tbl_name.col_name and tbl_name . col_name are equivalent.

  • If both identifiers and qualifiers exist, they must be quoted separately. For example, my-table`.`my-column is correct, but ```my-table.my-column` `` is incorrect.

  • Reserved words that follow a period (.) in a qualified name must be identifiers.

  • The syntax .tbl_name specifies the table tbl_name in the default database.

The qualifiers allowed for object names depend on the object type, and the rules are as follows:

  • Database names are fully qualified and do not include qualifiers:

    CREATE DATABASE obdb1;

  • Table, view, or stored procedure names can be specified with a database qualifier. Here are examples of unqualified and qualified names in a CREATE statement:

    CREATE TABLE mytable ...;
    CREATE VIEW myview ...;

    CREATE TABLE obdb.mytable ...;
    CREATE VIEW obdb.myview ...;

  • Column names can be specified with multiple qualifiers. The following table shows examples.

    Column referenceMeaning
    col_nameA column named col_name in any table referenced in the statement.
    tbl_name.col_nameA column named col_name in the table tbl_name in the default database.
    db_name.tbl_name.col_nameA column named col_name in the table tbl_name in the database db_name.

    To retrieve data from the same table in different databases in a single statement, you must qualify the table. If you reference columns in these tables, only column names that exist in both tables need to be qualified.

Contents

Edit this page