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 Length (bytes) | Allowed Characters |
| 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. 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 the section called “Treatment of Reserved Words in MySQL”. 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 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 the section called “The Server SQL Mode”.
As of MySQL 4.1, 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, 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 the section called “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 the section called “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.
MySQL allows names that consist of a single identifier or multiple identifiers. The components of a multiple-part name should be separated by period (‘.’) characters. The initial parts of a multiple-part name act as qualifiers that affect the context within which the final identifier is interpreted.
In MySQL you can refer to a column using any of the following forms:
| Column Reference | Meaning |
| col_name | The column col_name from whichever table used in the query contains a column of that name. |
| tbl_name.col_name | The column col_name from table tbl_name of the default database. |
| db_name.tbl_name.col_name | The column col_name from table tbl_name of the database db_name. This syntax is unavailable before MySQL 3.22. |
If any components of a multiple-part name require quoting, quote them individually rather than quoting the name as a whole. For example, `my-table`.`my-column` is legal, whereas `my-table.my-column` is not.
You need not specify a tbl_name or db_name.tbl_name prefix for a column reference in a statement unless the reference would be ambiguous. Suppose that tables t1 and t2 each contain a column c, and you retrieve c in a SELECT statement that uses both t1 and t2. In this case, c is ambiguous because it is not unique among the tables used in the statement. You must qualify it with a table name as t1.c or t2.c to indicate which table you mean. Similarly, to retrieve from a table t in database db1 and from a table t in database db2 in the same statement, you must refer to columns in those tables as db1.t.col_name and db2.t.col_name.
The syntax .tbl_name means the table tbl_name in the current database. This syntax is accepted for ODBC compatibility because some ODBC programs prefix table names with a ‘.’ character.
In MySQL, databases correspond to directories within the data directory. Tables within a database correspond to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system determines the case sensitivity of database and table names. This means database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix. One notable exception is Mac OS X, which is Unix-based but uses a default filesystem type (HFS+) that is not case sensitive. However, Mac OS X also supports UFS volumes, which are case sensitive just as on any Unix. See the section called “MySQL Extensions to Standard SQL”.
Note: Although database and table names are not case sensitive on some platforms, you should not refer to a given database or table using different cases within the same query. The following query would not work because it refers to a table both as my_table and as MY_TABLE:
mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;
Column names, index names, and column aliases are not case sensitive on any platform.
Table aliases are case sensitive before MySQL 4.1.1. The following query would not work because it refers to the alias both as a and as A:
mysql> SELECT col_name FROM tbl_name AS a
-> WHERE a.col_name = 1 OR A.col_name = 2;
If you have trouble remembering the allowable lettercase for database and table names, adopt a consistent convention, such as always creating databases and tables using lowercase names.
How table and database names are stored on disk and used in MySQL is defined by the lower_case_table_names system variable, which you can set when starting mysqld. lower_case_table_names can take one of the following values:
| Value | Meaning |
| 0 | Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. This is the default on Unix systems. Note that if you force this to 0 with --lower-case-table-names=0 on a case-insensitive filesystem and access MyISAM tablenames using different lettercases, this may lead to index corruption. |
| 1 | Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names as of MySQL 4.0.2, and to table aliases as of 4.1.1. This value is the default on Windows and Mac OS X systems. |
| 2 | Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. Note: This works only on filesystems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1. Setting lower_case_table_names to 2 can be done as of MySQL 4.0.18. |
If you are using MySQL on only one platform, you don't normally have to change the lower_case_table_names variable. However, you may encounter difficulties if you want to transfer tables between platforms that differ in filesystem case sensitivity. For example, on Unix, you can have two different tables named my_table and MY_TABLE, but on Windows those names are considered the same. To avoid data transfer problems stemming from database or table name lettercase, you have two options:
Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you don't see the names in their original lettercase.
Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your queries always refer to your database and table names with the correct lettercase on Windows. If you transfer your queries to Unix, where lettercase is significant, they will not work if the lettercase is incorrect.
Note that before setting lower_case_table_names to 1 on Unix, you must first convert your old database and table names to lowercase before restarting mysqld.