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

Database, table, index, column, and alias names all follow the same rules in MySQL.

Note that the rules changed starting with MySQL Version 3.23.6 when we introduced quoting of identifiers (database, table, and column names) with ``'. `"' will also work to quote identifiers if you run in ANSI mode. See section 1.8.2 Running MySQL in ANSI Mode.

Note that in addition to the above, you can't have ASCII(0) or ASCII(255) or the quoting character in an identifier.

Note that if the identifier is a restricted word or contains special characters you must always quote it with a ` (backtick) when you use it:

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

See section 6.1.7 Is MySQL Picky About Reserved Words?.

In MySQL versions prior to 3.23.6, the name rules are as follows:

• A name may consist of alphanumeric characters from the current character set and also `_' and `$'. The default character set is ISO-8859-1 Latin1; this may be changed with the --default-character-set option to mysqld. See section 4.6.1 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, a 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 immediately below).

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

In MySQL you can refer to a column using any of the following forms:

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. For example, suppose 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, so you must indicate which table you mean by writing t1.c or t2.c. Similarly, if you are retrieving from a table t in database db1 and from a table t in database db2, 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.

User Comments

When you want to manipulate a database with a dash
in the name, do it with backquotes.

CREATE DATABASE `my-db`;
SELECT * FROM `my-db`;
...

Field names with special (portuguese) characters like
ºçã are not allowed. <a
href=mailto:joaofgf@mail.telepac.pt>Joao</a>

Field Names with a space in it are allowed. Enclose them within the backquote character - `.

The backquote character seems to be ignored and an error arises if the field with a space is used as an argument in a MySQL function.

Add your own comment.