reference manuals search engine

MySQL Reference Manual - 4.0.20

10.2 Database, Table, Index, Column, and Alias Names

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.

In addition to the restrictions noted in the table, no identifier can contain ASCII 0 or a byte with a value of 255. Before MySQL 4.1, identifier quote characters should not be used in identifiers.

Beginning with MySQL 4.1, identifiers are stored using Unicode (UTF8). This applies to identifiers in table definitions that stored in `.frm' files and to identifiers stored in the grant tables in the mysql database. Although Unicode identifiers can include multi-byte characters, note that the maximum lengths shown in the table are byte counts. If an identifier does contain multi-byte characters, the number of characters allowed in the identifier is less than the value shown in the table.

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. For a list of reserved words, see Treatment of Reserved Words in MySQL. Special characters are those outside the set of alphanumeric characters from the current character set, `_', and `$'.

The quote character is the backtick (``'):

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

If the server SQL mode includes the ANSI_QUOTES mode option, it is also allowable to quote identifiers with 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)

See section Selecting SQL Modes.

As of MySQL 4.1, identifier quote characters can be included within an identifier by quoting 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 a`b that contains a column named c"d:

mysql> CREATE TABLE `a``b` (`c"d` INT);

Identifier quoting was introduced in MySQL 3.23.6 to allow use of identifiers that are reserved words or that contain special characters. Before 3.23.6, you cannot use identifiers that require quotes, so the rules for legal identifiers are more restrictive:

• A name may consist of alphanumeric characters from the current character set, `_', and `$'. The default character set is ISO-8859-1 (Latin1). This may be changed with the --default-character-set option to mysqld. See section The Character Set Used for Data and Sorting.

• A name may start with any character that is legal in a name. In particular, a name may start with a digit; this differs from many other database systems! However, an unquoted name cannot consist only of digits.

• You cannot use the `.' character in names because it is used to extend the format by which you can refer to columns (see section Identifier Qualifiers).

It is recommended that you do not use names like 1e, because an expression like 1e+1 is ambiguous. It might be interpreted as the expression 1e + 1 or as the number 1e+1, depending on context.

This document was generated on July, 26 2004 using texi2html 1.70.