Rechercher dans le manuel MySQL
9.2 Schema Object Names
[+/-]
Certain objects within MySQL, including database, table, index, column, alias, view, stored procedure, partition, tablespace, resource group 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.
The identifier quote character is the backtick
(`
):
If the ANSI_QUOTES
SQL mode is
enabled, it is also permissible to quote identifiers within double
quotation marks:
- ERROR 1064: You have an error in your SQL syntax...
- 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 quotation marks. They cannot be enclosed
within double quotation marks. The server SQL mode is controlled
as described in Section 5.1.11, “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
:
In the select list of a query, a quoted column alias can be specified using identifier or string quoting characters:
- +-----+-----+
- | 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
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 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”.
The following table describes the maximum length for each type of identifier.
Identifier Type | Maximum Length (characters) |
---|---|
Database | 64 (NDB storage engine: 63) |
Table | 64 (NDB storage engine: 63) |
Column | 64 |
Index | 64 |
Constraint | 64 |
Stored Program | 64 |
View | 64 |
Tablespace | 64 |
Server | 64 |
Log File Group | 64 |
Alias | 256 (see exception following table) |
Compound Statement Label | 16 |
User-Defined Variable | 64 |
Resource Group | 64 |
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 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.
NDB Cluster imposes a maximum length of 63 characters for names of databases and tables. See Section 22.1.7.5, “Limits Associated with Database Objects in NDB Cluster”.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-identifiers.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.