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

Identifier	Maximum Length (bytes)
Database	64
Table	64
Column	64
Index	64
Alias	255

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

No identifier can contain ASCII 0 (0x00) or a byte with a value of 255.
The use of identifier quote characters in identifiers is permitted, although it is best to avoid doing so if possible.
Database, table, and column names should not end with space characters.
Database names cannot contain ‘/’, ‘\’, ‘.’, or characters that are not allowed in a directory name.
Table names cannot contain ‘/’, ‘\’, ‘.’, or characters that are not allowed in a filename.
The length of the identifier is in bytes, not characters. If you use multi-byte characters in your identifier names, then the maximum length will depend on the byte count of all the characters used.

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.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 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.

9.2.1. Identifier Qualifiers

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 statement 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`.

If any components of a multiple-part name require quoting, quote them individually rather than quoting the name as a whole. For example, write `my-table`.`my-column`, not `my-table.my-column`.

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.

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.

The syntax .tbl_name means the table tbl_name in the default database. This syntax is accepted for ODBC compatibility because some ODBC programs prefix table names with a ‘.’ character.

9.2.2. Identifier Case Sensitivity

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds 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 case sensitive in most varieties of Unix, and not case sensitive in Windows. 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 Section 1.9.4, “MySQL Extensions to Standard SQL”. The lower_case_table_names system variable also affects how the server handles identifier case sensitivity, as described later in this section.

MySQL Enterprise. lower_case_table_names is just one of the system variables monitored by the MySQL Network Monitoring and Advisory Service. For information about subscribing to this service see, http://www.mysql.com/products/enterprise/advisors.html.

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 statement. The following statement 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, index and stored routine names are not case sensitive on any platform, nor are column aliases. Trigger names are case sensitive.

By default, table aliases are case sensitive on Unix, but not so on Windows or Mac OS X. The following statement would not work on Unix, 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;

However, this same statement is permitted on Windows. To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.

How table and database names are stored on disk and used in MySQL is affected by the lower_case_table_names system variable, which you can set when starting mysqld. lower_case_table_names can take the values shown in the following table. On Unix, the default value of lower_case_table_names is 0. On Windows the default value is 1. On Mac OS X, the default value is 2.

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. Note that if you force this variable to 0 with `--lower-case-table-names=0` on a case-insensitive filesystem and access `MyISAM` tablenames using different lettercases, index corruption may result.
`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 and table aliases.
`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`.

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 these two names are considered identical. To avoid data transfer problems stemming from lettercase of database or table names, 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 statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.
Exception: If you are using InnoDB tables, you should set lower_case_table_names to 1 on all platforms to force names to be converted to lowercase.

Note that if you plan to set the lower_case_table_names system variable to 1 on Unix, you must first convert your old database and table names to lowercase before restarting mysqld with the new variable setting.

Object names may be considered duplicates if their uppercase forms are equal according to a binary collation. That is true for names of cursors, conditions, functions, procedures, savepoints, and routine local variables. It is not true for names of columns, constraints, databases, statements prepared with PREPARE, tables, triggers, users, and user-defined variables.

9.2.3. Function Name Parsing and Resolution

MySQL 5.0 supports built-in (native) functions, user-defined functions (UDFs), and stored functions. This section describes how the server recognizes whether the name of a built-in function is used as a function call or as an identifier, and how the server determines which function to use in cases when functions of different types exist with a given name.

Built-In Function Name Parsing

The parser uses default rules for parsing names of built-in functions. These rules can be changed by enabling the IGNORE_SPACE SQL mode.

When the parser encounters a word that is the name of a built-in function, it must determine whether the name signifies a function call or is instead a non-expression reference to an identifier such as a table or column name. For example, in the following statements, the first reference to count is a function call, whereas the second reference is a table name:

SELECT COUNT(*) FROM mytable;
CREATE TABLE count (i INT);

The parser should recognize the name of a built-in function as indicating a function call only when parsing what is expected to be an expression. That is, in non-expression context, function names are permitted as identifiers.

However, some built-in functions have special parsing or implementation considerations, so the parser uses the following rules by default to distinguish whether their names are being used as function calls or as identifiers in non-expression context:

To use the name as a function call in an expression, there must be no whitespace between the name and the following ‘(’ parenthesis character.
Conversely, to use the function name as an identifier, it must not be followed immediately by a parenthesis.

The requirement that function calls be written with no whitespace between the name and the parenthesis applies only to the built-in functions that have special considerations. COUNT is one such name. The exact list of function names for which following whitespace determines their interpretation are those listed in the sql_functions[] array of the sql/lex.h source file. Before MySQL 5.1, they are rather numerous (about 200), so you may find it easiest to treat the no-whitespace requirement as applying to all function calls. In MySQL 5.1, parser improvements reduce to about 30 the number of affected function names.

For functions not listed in the sql_functions[]) array, whitespace does not matter. They are interpreted as function calls only when used in expression context and may be used freely as identifiers otherwise. ASCII is one such name. However, for these non-affected function names, interpretation may vary in expression context: func_name () is interpreted as a built-in function if there is one; if not, func_name () is interpreted as a user-defined function or stored function if one exists with that name.

The IGNORE_SPACE SQL mode can be used to modify how the parser treats function names that are whitespace-sensitive:

With IGNORE_SPACE disabled, the parser interprets the name as a function call when there is no whitespace between the name and the following parenthesis. This occurs even when the function name is used in non-expression context:
```
mysql> CREATE TABLE count(i INT);
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'count(i INT)'
```
To eliminate the error and cause the name to be treated as an identifier, either use whitespace following the name or write it as a quoted identifier (or both):
```
CREATE TABLE count (i INT);
CREATE TABLE `count`(i INT);
CREATE TABLE `count` (i INT);
```
With IGNORE_SPACE enabled, the parser loosens the requirement that there be no whitespace between the function name and the following parenthesis. This provides more flexibility in writing function calls. For example, either of the following function calls are legal:
```
SELECT COUNT(*) FROM mytable;
SELECT COUNT (*) FROM mytable;
```
However, enabling IGNORE_SPACE also has the side effect that the parser treats the affected function names as reserved words (see Section 9.3, “Reserved Words”). This means that a space following the name no longer signifies its use as an identifier. The name can be used in function calls with or without following whitespace, but causes a syntax error in non-expression context unless it is quoted. For example, with IGNORE_SPACE enabled, both of the following statements fail with a syntax error because the parser interprets count as a reserved word:
```
CREATE TABLE count(i INT);
CREATE TABLE count (i INT);
```
To use the function name in non-expression context, write it as a quoted identifier:
```
CREATE TABLE `count`(i INT);
CREATE TABLE `count` (i INT);
```

To enable the IGNORE_SPACE SQL mode, use this statement:

SET sql_mode = 'IGNORE_SPACE';

IGNORE_SPACE is also enabled by certain other composite modes such as ANSI that include it in their value:

SET sql_mode = 'ANSI';

Check Section 5.2.6, “SQL Modes”, to see which composite modes enable IGNORE_SPACE.

To minimize the dependency of SQL code on the IGNORE_SPACE setting, use these guidelines:

Avoid creating UDFs or stored functions that have the same name as a built-in function.
Avoid using function names in non-expression context. For example, these statements use count (one of the affected function names affected by IGNORE_SPACE), so they fail with or without whitespace following the name if IGNORE_SPACE is enabled:
```
CREATE TABLE count(i INT);
CREATE TABLE count (i INT);
```
If you must use a function name in non-expression context, write it as a quoted identifier:
```
CREATE TABLE `count`(i INT);
CREATE TABLE `count` (i INT);
```

Function Name Resolution

The following rules describe how the server resolves references to function names for function creation and invocation:

Built-in functions and user-defined functions
A UDF can be created with the same name as a built-in function but the UDF cannot be invoked because the parser resolves invocations of the function to refer to the built-in function. For example, if you create a UDF named ABS, references to ABS() invoke the built-in function.
Built-in functions and stored functions
It is possible to create a stored function with the same name as a built-in function, but to invoke the stored function it is necessary to qualify it with a database name. For example, if you create a stored function named PI in the test database, you invoke it as test.PI() because the server resolves PI() as a reference to the built-in function.
User-defined functions and stored functions
User-defined functions and stored functions share the same namespace, so you cannot create a UDF and a stored function with the same name.

The preceding function name resolution rules have implications for upgrading to versions of MySQL that implement new built-in functions:

If you have already created a user-defined function with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the UDF becomes inaccessible. To correct this, use DROP FUNCTION to drop the UDF, and then use CREATE FUNCTION to re-create the UDF with a different non-conflicting name.
If a new version of MySQL implements a built-in function with the same name as an existing stored function, you have two choices: Rename the stored function to use a non-conflicting name, or change calls to the function so that they use a schema qualifier (that is, use schema_name.func_name() syntax).