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 for each type of identifier.

There are some restrictions on the characters that may appear in identifiers:

As of MySQL 5.1.6, special characters in database and table names are encoded in the corresponding filesystem names as described in Section 9.2.3, “Mapping of Identifiers to Filenames”. If you have databases or tables from an older version of MySQL that contain special characters and that have not been updated to use the new encoding, you will see their names displayed with a prefix of #mysql50#. For information about referring to such names or converting them to the newer encoding, see that section.

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 string columns in the grant tables (and in any other tables) in MySQL 5.1 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 need not be quoted even if it is reserved.) For a list of reserved words, see Section 9.3, “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 the ANSI_QUOTES mode causes the server to interpret double-quoted strings as identifiers, string literals must be enclosed within single quotes when this mode is enabled. They cannot be enclosed within double quotes.

The server SQL mode is controlled as described in Section 5.2.6, “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);

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

It is recommended that you do not use names of the form Me or MeN, where M and N are integers. For example, avoid using 1e or 2e2 as identifiers, 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.

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.

Be careful when using MD5() to produce table names because it can produce names in illegal or ambiguous formats such as those just described.