(mysql.info) legal-names
Info Catalog
(mysql.info) literals
(mysql.info) language-structure
(mysql.info) user-variables
9.2 Database, Table, Index, Column, and Alias Names
===================================================
Menu
* identifier-qualifiers Identifier Qualifiers
* name-case-sensitivity Identifier Case Sensitivity
Database, table, index, column, and alias names are identifiers. This
section describes the allowable syntax for identifiers in MySQL.
The following table describes the maximum length and allowable
characters for each type of identifier.
*Identifier**Maximum *Allowed Characters*
Length
(bytes)*
Database 64 Any character that is allowed in a directory
name, except ‘`/'’, ‘`\'’, or ‘`.'’
Table 64 Any character that is allowed in a filename,
except ‘`/'’, ‘`\'’, or ‘`.'’
Column 64 All characters
Index 64 All characters
Alias 255 All characters
In addition to the restrictions noted in the table, no identifier can
contain ASCII 0 or a byte with a value of 255. Database, table, and
column names should not end with space characters. The use of
identifier quote characters in identifiers is permitted, although it is
best to avoid doing so if possible.
Identifiers are stored using Unicode (UTF-8). This applies to
identifiers in table definitions that stored in `.frm' files and to
identifiers stored in the grant tables in the `mysql' database. The
sizes of the string columns in the grant tables (and in any other
tables) in MySQL 5.0 are given as number of characters. This means that
(unlike some earlier versions of MySQL) you can use multi-byte
characters without reducing the number of characters allowed for values
stored in these columns.
An identifier may be quoted or unquoted. If an identifier is a reserved
word or contains special characters, you _must_ quote it whenever you
refer to it. (Exception: A word that follows a period in a qualified
name must be an identifier, so it is not necessary to quote it, even if
it is a reserved word.) For a list of reserved words, see
reserved-words. Special characters are those outside the set of
alphanumeric characters from the current character set, ‘`_'’, and
‘`$'’.
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)
Note: Because `ANSI_QUOTES' causes the server to interpret
double-quoted strings as identifiers, string literals must be enclosed
within single quotes. They cannot be enclosed within double quotes when
`ANSI_QUOTES' is enabled.
The server SQL mode is controlled as described in
server-sql-mode.
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);
It is recommended that you do not use names of the form `Me' or `MeN',
such as `1e' or `2e2', 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.
Info Catalog
(mysql.info) literals
(mysql.info) language-structure
(mysql.info) user-variables
automatically generated byinfo2html