Schema objects include databases, tables, indexes, columns, aliases, views, stored procedures, partitions, and tablespaces, collectively referred to as identifiers.
Reference Identifier
Identifier characters are backticks (`). Identifiers can be quoted or unquoted. If an identifier contains special characters or reserved words, 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 identifier names without quotation marks:
ASCII: [0-9,a-z,A-Z$_] (ASCII: [0-letters, digits 0-9, dollar sign, underscores])
Extended characters: U+0080 .. U+FFFF
Identifiers that can be used in quoted identifiers include all Unicode BMP characters except U+0000:
ASCII: U+0001 .. U+007F
Extended characters: U+0080 .. U+FFFF
Identifiers, whether quoted or not, must not contain ASCII NUL (U+0000) or supplementary characters (U+10000 and above).
Identifiers can start with a digit, but cannot consist solely of digits unless they are referenced.
Database, table, and column names must not end with a space character.
If a quote character is part of the identifier, it can be included in the identifier. If a character used to form part of the identifier is also used to reference the identifier itself, the reference must be repeated twice.
As shown in the following example, create a table named a"b that contains a column named ``cd:
obclient> CREATE TABLE `a"b` (`c``d` INT);
Query OK, 0 rows affected
When referencing the alias elsewhere in the statement, you must use the identifier; 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 contains at least one qualifier.
As shown in the following example, you can create a table with an unrestricted name t1:
obclient> CREATE TABLE t1 (c INT);
Query OK, 0 rows affected
Since t1 does not contain a qualifier to specify the database, this statement creates the table in the default database. If no default database exists, an error occurs.
As shown in the following example, you can create a table with the qualified name ob1.t1:
obclient> CREATE TABLE ob1.t1 (c INT);
Query OK, 0 rows affected
Because ob1.t1 contains the database qualifier ob1, this statement creates t1 in a database named ob1. If there is no default database, you must specify a qualifier. If a default database exists, you can use the qualifier to specify a non-default database.
Limiters 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 (.).
A qualifier is an independent identifier. A space can be present between the identifier and the qualifier. For example,
tbl_name.col_nameandtbl_name . col_nameare equivalent.When both identifiers and qualifiers are present, they must be referenced separately. For example,
my-table`.`my-columnis correct, while ```my-table.my-column` `` is incorrect.The reserved words following a period (.) in the name must be identifiers.
The syntax
.tbl_namerefers to the tabletbl_namein the default database.
The qualifiers allowed for an object name depend on the object type, as follows:
The database name is fully qualified and does not contain qualifiers:
CREATE DATABASE obdb1;You can specify a database qualifier for the name of a table, view, or stored program. The following examples show unqualified and qualified names in
CREATEstatements:CREATE TABLE mytable ...; CREATE VIEW myview ...; CREATE TABLE obdb.mytable ...; CREATE VIEW obdb.myview ...;Multiple qualifiers can be specified for a column name. The following table describes the qualifiers.
Column ReferenceDescriptioncol_nameThe column containing the name col_namein any table used in the statement.tbl_name.col_nameThe column col_namein the tabletbl_nameof the default database.db_name.tbl_name.col_nameColumn col_namefrom tabletbl_namein databasedb_name.To retrieve data from the same table in different databases within a single statement, you must qualify the table. If columns from these tables are referenced, only column names that appear in both tables need to be qualified.