[+/-]
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 allowable 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,
“_
”, and
“$
” 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
(“`
”):
mysql> SELECT * FROM `select` WHERE `select`.id > 100;
If the ANSI_QUOTES
SQL mode is
enabled, it is also allowable to quote identifiers within double
quotes:
mysql>CREATE TABLE "test" (col INT);
ERROR 1064: You have an error in your SQL syntax... mysql>SET sql_mode='ANSI_QUOTES';
mysql>CREATE TABLE "test" (col INT);
Query OK, 0 rows affected (0.00 sec)
The 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 quotes. They cannot be enclosed within
double quotes. The server SQL mode is controlled as described in
Section 5.1.8, “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 a`b
that contains a column named c"d
:
mysql> 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:
mysql> 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.
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
or
M
e
,
where M
eN
M
and
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 expression 1e
+ 3
or as the number 1e+3
.
Be careful when using MD5()
to
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 (0x00
).
Database, table, and column names should not end with space characters.
Before MySQL 5.1.6, database and table names cannot contain
“/
”,
“\
”,
“.
”, or characters that are
not allowed in file names.
As of MySQL 5.1.6, special characters in database and table names
are encoded in the corresponding file system names as described in
Section 8.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) |
Database | 64 |
Table | 64 |
Column | 64 |
Index | 64 |
Constraint | 64 |
Stored Function or Procedure | 64 |
Trigger | 64 |
View | 64 |
Event | 64 |
Tablespace | 64 |
Log File Group | 64 |
Alias | 256 (see exception following table) |
Compound Statement Label | 16 |
As of MySQL 5.1.23, 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 multi-byte characters without reducing
the number of characters allowed for values stored in these
columns, something not true prior to MySQL 4.1. The allowable
Unicode characters are those in the Basic Multilingual Plane
(BMP). Supplementary characters are not allowed.
User Comments
Add your own comment.