Identifiers in MySQL

An identifier is simply the name of an alias, a database, a table, a column, or an index. It is how you uniquely identify that object. Before you can begin creating your own databases and tables, we should discuss what identifiers are valid in MySQL.

Generally speaking, identifiers can contain any characters , with these exceptions:

• They can't contain quote characters, ACSII(0) and ASCII(255).

• Database names can contain any characters that are allowed in a directory name, but not the characters that have special meaning in a directory name (/, \, and .) for obvious reasons.

• Table names can contain any characters that are allowed in filenames, except for . and /.

All identifiers except aliases can be up to 64 characters long. Alias names (covered in Chapter 7, "Advanced Queries") can be up to 255 characters long.

One strange rule about identifiers in MySQL is that you can use reserved words as identifiers, as long as they have quotes around them. For example, you could have a table called TABLE. Of course, just because you can doesn't mean you should, and this is a practice best avoided. Even if it does not confuse you when working with the system, it may confuse the program mysqldump, which is often used for backups .

There is a short list of reserved words that MySQL will allow you to have as identifiers without quoting. This is contrary to the ANSI standard for SQL, but it is fairly common in day-to-day use. The most common examples you will see are DATE and TIMESTAMP used as column names.