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 is a reserved word. 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:
- ASCII: [0-9,a-z,A-Z$_] (numerals 0-9, basic Latin letters, both lowercase and uppercase, dollar sign, underscore)
- Extended: U+0080 .. U+FFFF
The following characters are valid, but identifiers using them must be quoted:
- ASCII: U+0001 .. U+007F (full Unicode Basic Multilingual Plane (BMP) except for U+0000)
- Extended: U+0080 .. U+FFFF
- Identifier quotes can themselves be used as part of an identifier, as long as they are quoted.
There are a number of other rules for identifiers:
- Identifiers are stored as Unicode (UTF-8)
- Identifiers may or may not be case-sensitive. See Indentifier Case-sensitivity.
- Database, table and column names can't end with space characters
- Identifier names may begin with a numeral, but can't only contain numerals unless quoted.
- Identifiers are not permitted to contain the ASCII NUL character (U+0000) and supplementary characters (U+10000 and higher).
- Names such as 5e6, 9e are not prohibited, but it's strongly recommended not to use them, as they could lead to ambiguity in certain contexts, being treated as a number or expression.
- User variables cannot be used as part of an identifier, or as an identifier in an SQL statement.
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.
- Databases, tables, columns, indexes, constraints, stored routines, triggers, events, views, tablespaces, servers and log file groups have an maximum length of 64 characters.
- Compound statement labels have a maximum length of 16 characters
- Aliases have a maximum length of 256 characters, except for column aliases in CREATE VIEW statements, which adhere to the are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters).
- Users have a maximum length of 80 characters.
- Roles have a maximum length of 128 characters.
- Multi-byte characters do not count extra towards towards the character limit.
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:
MariaDB [test]> 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 | +------+
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)