Schema object names

Schema objects include databases, tables, indexes, columns, aliases, views, stored procedures, partitions, and tablespaces, 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 keyword, it must be quoted when referenced.

obclient> 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-basic Latin, digits 0-9, dollar sign, underscore])

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

• Characters allowed in quoted identifiers include the full 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 an identifier is quoted, the quotation character can be included within the identifier. If the character included in the identifier is the same as the one used to quote the identifier, it must be quoted twice.

For example, the following statement creates a table named a"b with a column named c`d:

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

When referencing an alias elsewhere in a statement, you must use identifier quotation. Otherwise, it will be treated as a string literal.

obclient> SELECT 1 AS `one`, 2 AS 'two';
+-----+-----+
| one | two |
+-----+-----+
|   1 |   2 |
+-----+-----+
1 row in set

Identifier qualifiers

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

For example, the following statement creates a table named t1:

obclient> CREATE TABLE t1 (c INT);
Query OK, 0 rows affected

Since t1 does not contain a qualifier specifying the database, the statement creates the table in the default database. If no default database exists, an error will occur.

For example, the following statement creates a table named t1 in the ob1 database:

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

Because ob1.t1 contains the database qualifier ob1, the statement creates t1 in the ob1 database. If no default database exists, you must specify the qualifier. If a default database exists, you can specify a non-default database 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 and can be separated from the identifier by spaces. For example, tbl_name.col_name and tbl_name . col_name are equivalent.

• When both an identifier and a qualifier are present, they must be quoted separately. For example, my-table`.`my-column is correct, while my-table.my-column is incorrect.

• Reserved keywords following a period (.) in a qualified name must be identifiers.

• The syntax .tbl_name refers to the table tbl_name in the default database.

The qualifiers allowed for object names depend on the object type, following these rules:

• Database names are fully qualified and do not have 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 have multiple qualifiers. The following table shows examples:

  Column reference	Meaning
  col_name	Any column named col_name in any table referenced in the statement.
  tbl_name.col_name	The column col_name in the table tbl_name in the default database.
  db_name.tbl_name.col_name	The column col_name in the table tbl_name of the db_name database.

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