Databases, tables, indexes, columns, aliases, views, stored routines, triggers, events, variables, partitions, tablespaces, savepoints, labels, users, roles, are collectively known as identifiers, and have certain rules for naming.
Identifiers may be quoted using the backtick character - `
. Quoting is optional for identifiers that don't contain special characters, or for identifiers that are not reserved words. If the ANSI_QUOTES
SQL_MODE flag is set, double quotes ("
) can also be used to quote identifiers.
Even when using reserved words as names, fully qualified names do not need to be quoted. For example, test.select
has only one possible meaning, so it is correctly parsed even without quotes.
The following characters are valid, and allow identifiers to be unquoted:
The following characters are valid, but identifiers using them must be quoted:
There are a number of other rules for identifiers:
The regular quote character is the backtick character - `
, but if the ANSI_QUOTES
SQL_MODE option is specified, a regular double quote - "
may be used as well.
The backtick character can be used as part of an identifier. In that case the identifier needs to be quoted. The quote character can be the backtick, but in that case, the backtick in the name must be escaped with another backtick.
MariaDB allows the column name to be used on its own if the reference will be unambiguous, or the table name to be used with the column name, or all three of the database, table and column names. A period is used to separate the identifiers, and the period can be surrounded by spaces.
Using the period to separate identifiers:
CREATE TABLE t1 (i int); INSERT INTO t1(i) VALUES (10); SELECT i FROM t1; +------+ | i | +------+ | 10 | +------+ SELECT t1.i FROM t1; +------+ | i | +------+ | 10 | +------+ SELECT test.t1.i FROM t1; +------+ | i | +------+ | 10 | +------+
The period can be separated by spaces:
SELECT test . t1 . i FROM t1; +------+ | i | +------+ | 10 | +------+
Resolving ambiguity:
CREATE TABLE t2 (i int); SELECT i FROM t1 LEFT JOIN t2 ON t1.i=t2.i; ERROR 1052 (23000): Column 'i' in field list is ambiguous SELECT t1.i FROM t1 LEFT JOIN t2 ON t1.i=t2.i; +------+ | i | +------+ | 10 | +------+
Creating a table with characters that require quoting:
CREATE TABLE 123% (i int); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '123% (i int)' at line 1 CREATE TABLE `123%` (i int); Query OK, 0 rows affected (0.85 sec) CREATE TABLE `TABLE` (i int); Query OK, 0 rows affected (0.36 sec)
Using double quotes as a quoting character:
CREATE TABLE "SELECT" (i int); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"SELECT" (i int)' at line 1 SET sql_mode='ANSI_QUOTES'; Query OK, 0 rows affected (0.03 sec) CREATE TABLE "SELECT" (i int); Query OK, 0 rows affected (0.46 sec)
Using an identifier quote as part of an identifier name:
SHOW VARIABLES LIKE 'sql_mode'; +---------------+-------------+ | Variable_name | Value | +---------------+-------------+ | sql_mode | ANSI_QUOTES | +---------------+-------------+ CREATE TABLE "fg`d" (i int); Query OK, 0 rows affected (0.34 sec)
Creating the table named *
(Unicode number: U+002A) requires quoting.
CREATE TABLE `*` (a INT);
Floating point ambiguity:
CREATE TABLE 8984444cce5d (x INT); Query OK, 0 rows affected (0.38 sec) CREATE TABLE 8981e56cce5d (x INT); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '8981e56cce5d (x INT)' at line 1 CREATE TABLE `8981e56cce5d` (x INT); Query OK, 0 rows affected (0.39 sec)
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/identifier-names/