Database, table, index, column, and alias names are identifiers. This section describes the permissible syntax for identifiers in MySQL. Section 8.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. The
set of alphanumeric characters from the current character set,
$” are not special. Reserved
words are listed at Section 8.3, “Reserved Words”. (Exception:
A reserved word that follows a period in a qualified name must be
an identifier, so it need not be quoted.)
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.6, “Server SQL Modes”.
As of MySQL 4.1, 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, 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 | +-----+-----+
Identifier quoting was introduced in MySQL 3.23.6 to permit use of
identifiers that contain special characters or are reserved words.
Before 3.23.6, you cannot use identifiers that require quotation
marks, so the only legal characters are the set of alphanumeric
characters from the current character set,
Elsewhere in the statement, quoted references to the alias must use identifier quoting or the reference is treated as a string literal.
Identifiers may begin with a digit but unless quoted may not consist solely of digits.
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 8.4, “User-Defined Variables”, for more information and examples of workarounds.
There are some restrictions on the characters that may appear in identifiers:
No identifier can contain ASCII NUL (
or a byte with a value of 255.
Before MySQL 4.1, identifier quote characters should not be used in identifiers.
Database, table, and column names should not end with space characters.
Database and table names cannot contain
.”, or characters that are
not permitted in file names.
The following table describes the maximum length for each type of identifier. Before MySQL 4.1.5, the maximum-length restrictions on identifiers are measured in bytes, not characters. Until that version, if you use multi-byte characters in your identifier names, the maximum length will depend on the byte count of all the characters used.
Beginning with MySQL 4.1, identifiers are stored using Unicode
(UTF-8). This applies to identifiers in table definitions that are
.frm files and to identifiers
stored in the grant tables in the
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. The permissible Unicode characters are those in the Basic
Multilingual Plane (BMP). Supplementary characters are not