Schema object names

Schema objects such as databases, tables, indexes, columns, aliases, views, stored procedures, partitions, and tablespaces are identifiers.

Quote identifiers

You can quote an identifier by using backticks (`) or leave it unquoted. If the identifier contains special characters or is a reserved word, you must quote the identifier.

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

OceanBase Database converts and stores identifiers as Unicode (UTF-8). An identifier can contain the following characters:

• An unquoted identifier can contain the following characters:

  • ASCII: basic Latin letters, digits 0-9, dollar signs ($), and underscores (_).

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

• Characters allowed in a quoted identifier include the full Unicode Basic Multilingual Plane (BMP), except U+0000:

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

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

• Regardless of whether an identifier is quoted or unquoted, it cannot contain the ASCII NUL (U+0000) or supplementary characters (U+10000 and higher).

• An identifier can start with a digit. However, it cannot contain only digits unless it is quoted.

• The name of a database, table, and column cannot end with a space character.

You can use backticks to quote an identifier that contains quotation marks. If the identifier contains a backtick, you must add two backticks.

For example, you can execute the following statement to create a table named a"b that contains a column named c`d:

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

If an alias is referenced elsewhere in the statement, you must use identifier quotation. Otherwise, the reference is processed as a string literal.

obclient> SELECT 1 AS `one`, 2 AS 'two';
+-----+-----+
 one  two 
+-----+-----+
   1    2 
+-----+-----+
1 row in set (0.00 sec)

Identifier qualifiers

The name of an object can be qualified or unqualified (omitted). A qualified name includes at least one qualifier.

The following sample statement creates a table whose unqualified name is t1:

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

The table is created in the default database because t1 in the statement contains no qualifiers to specify the database. If no default database exists, an error occurs.

The following sample statement creates a table whose qualified name is ob1.t1:

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

The t1 table is created in the ob1 database because ob1.t1 in the statement contains the database qualifier ob1. If no default database exists, you must specify a qualifier. If a default database exists, you can use a qualifier to specify a database.

Characteristics of quantifiers:

• An unqualified name consists of only one identifier. A qualified name consists of multiple identifiers.

• Identifiers and qualifiers are separated with periods (.).

• A qualifier is a separate token, and spaces can exist between an identifier and a qualifier. For example, tbl_name.col_name is equivalent to tbl_name . col_name.

• To quote a qualified name, quote the identifier and qualifier separately. For example, use the my-table`.`my-column format instead of the my-table.my-column format.

• A reserved word follows a period (.) in a qualified name must be an identifier.

• The syntax .tbl_name indicates a table named tbl_name in the default database.

The qualifier allowed in an object name depends on the object type. Note the following rules:

• The name of a database is fully qualified and contains no qualifier:

  CREATE DATABASE obdb1;
  

• You can specify a database name as a qualifier in the name of a table, view, or stored procedure. The following example shows unqualified and qualified names in sample CREATE statements:

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

• A column name can contain multiple qualifiers. The following table describes the types of column reference.

  Column reference	Description
  col_name	The column named col_name of any table used in the statement.
  tbl_name.col_name	The column named col_name of the table named tbl_name in the default database.
  db_name.tbl_name.col_name	The column named col_name of the table named tbl_name in the database named db_name.

  To retrieve data from tables that have the same name but belong to different databases by using one statement, you must specify the table qualifiers. If you want to reference columns in these tables, you need to specify quantifiers only for column names that exist in two or more tables.