Certain objects within MySQL, including database, table, index, column, alias, view, stored procedure, partition, and other object names are known as identifiers. This section describes the permissible syntax for identifiers in MySQL. Section 9.2.2, “Identifier Case Sensitivity”, describes which types of identifiers are case sensitive and under what conditions.
An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it. (Exception: A reserved word that follows a period in a qualified name must be an identifier, so it need not be quoted.) Reserved words are listed at Section 9.3, “Keywords and Reserved Words”.
Identifiers are converted to Unicode internally. They may contain these characters:
Permitted characters in unquoted identifiers:
ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)
Extended: U+0080 .. U+FFFF
Permitted characters in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000:
ASCII: U+0001 .. U+007F
Extended: U+0080 .. U+FFFF
ASCII NUL (U+0000) and supplementary characters (U+10000 and higher) are not permitted in quoted or unquoted identifiers.
Identifiers may begin with a digit but unless quoted may not consist solely of digits.
Database, table, and column names cannot end with space characters.
Database and table names cannot contain “
.”, or characters that are not permitted in file names.
The identifier quote character is the backtick
SELECT * FROM `select` WHERE `select`.id > 100;
ANSI_QUOTES SQL mode is
enabled, it is also permissible to quote identifiers within double
CREATE TABLE "test" (col INT);ERROR 1064: You have an error in your SQL syntax... mysql>
CREATE TABLE "test" (col INT);Query OK, 0 rows affected (0.00 sec)
ANSI_QUOTES mode causes the
server to interpret double-quoted strings as identifiers.
Consequently, when this mode is enabled, string literals must be
enclosed within single quotation marks. They cannot be enclosed
within double quotation marks. The server SQL mode is controlled
as described in Section 5.1.7, “Server SQL Modes”.
Identifier quote characters can be included within an identifier
if you quote the identifier. If the character to be included
within the identifier is the same as that used to quote the
identifier itself, then you need to double the character. The
following statement creates a table named
that contains a column named
CREATE TABLE `a``b` (`c"d` INT);
In the select list of a query, a quoted column alias can be specified using identifier or string quoting characters:
SELECT 1 AS `one`, 2 AS 'two';+-----+-----+ | one | two | +-----+-----+ | 1 | 2 | +-----+-----+
Elsewhere in the statement, quoted references to the alias must use identifier quoting or the reference is treated as a string literal.
It is recommended that you do not use names that begin with
N are integers. For example, avoid
1e as an identifier, because an
expression such as
1e+3 is ambiguous. Depending
on context, it might be interpreted as the expression
+ 3 or as the number
Be careful when using
produce table names because it can produce names in illegal or
ambiguous formats such as those just described.
A user variable cannot be used directly in an SQL statement as an identifier or as part of an identifier. See Section 9.4, “User-Defined Variables”, for more information and examples of workarounds.
The following table describes the maximum length for each type of identifier.
|Identifier||Maximum Length (characters)|
|Alias||256 (see exception following table)|
|Compound Statement Label||16|
As of MySQL 5.0.52, aliases for column names in
CREATE VIEW statements are checked
against the maximum column length of 64 characters (not the
maximum alias length of 256 characters).
Identifiers are stored using Unicode (UTF-8). This applies to
identifiers in table definitions that are stored in
.frm files and to identifiers stored in the
grant tables in the
mysql database. The sizes
of the identifier string columns in the grant tables are measured
in characters. You can use multibyte characters without reducing
the number of characters permitted for values stored in these
columns. As indicated earlier, the permissible Unicode characters
are those in the Basic Multilingual Plane (BMP). Supplementary
characters are not permitted.
For tables using the
engine, there is an additional requirement that the combined
length of a table name and the name of the database in which it is
found must not exceed 122 characters. See
Section 126.96.36.199, “Limits Associated with Database Objects in MySQL Cluster”.