Certain objects within MySQL, including database, table, index, column, alias, view, stored procedure, partition, tablespace, 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, "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.
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 quotation marks:
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
"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
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 using
1e as an identifier, because an expression such as
1e+3 is ambiguous. Depending on context, it might be interpreted as the
1e + 3 or as the number
Be careful when using
to produce table names because it can produce names in illegal or ambiguous formats such as those just
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.
Special characters in database and table names are encoded in the corresponding file system names as described
in Section 9.2.3, "Mapping of Identifiers to File
Names". If you have databases or tables from an older version of MySQL that contain special characters
and for which the underlying directory names or file names have not been updated to use the new encoding, the
server displays their names with a prefix of
#mysql50#. For information about
referring to such names or converting them to the newer encoding, see that section.
The following table describes the maximum length for each type of identifier.
|Identifier||Maximum Length (characters)|
|Stored Procedure or Function||64|
|Log File Group||64|
|Alias||256 (see exception following table)|
|Compound Statement Label||16|
Aliases for column names in
statements are checked against the maximum column length of 64 characters (not the maximum alias length of 256
Identifiers are stored using Unicode (UTF-8). This applies to identifiers in table definitions that are stored
.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 multi-byte characters without reducing the number of characters
permitted for values stored in these columns, something not true prior to MySQL 4.1. As indicated earlier, the
permissible Unicode characters are those in the Basic Multilingual Plane (BMP). Supplementary characters are not