Schema object names

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

Identifier quoting

The quoting 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.

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, underscore])

  • 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, unless they are quoted, in which case they cannot consist solely of numbers.

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

If an identifier is quoted, the quoting character can be included in the identifier. If the character included in the identifier is the same as the character 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 quoting, 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 include a qualifier for specifying the database, the statement creates the table in the default database. If no default database exists, an error occurs.

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

Since ob1.t1 includes the database qualifier ob1, the statement creates the t1 table 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 there can be space between the identifier and the qualifier. For example, tbl_name.col_name and tbl_name . col_name are equivalent.

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

• Reserved words 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, as follows:

• 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. Examples of unqualified and qualified names in a CREATE statement are as follows:

  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 reference	Meaning
  col_name	Any column named col_name in any table used 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 in the db_name database.

  To retrieve data from the same table in different databases in the same 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.