MySQL Keywords and Reserved Words

I've seen some confusion on what constitutes a keyword or a reserved word in MySQL. The manual defines them, and has a complete list. Reserved words are a special subset of keywords, and you can't use a reserved word as an identifier unless you quote it with backticks. I discourage the use of backticks to quote identifiers, because it allows you to use lots of words and characters in your identifier that you'll probably regret later. For example:

`` mysql> create tableYou will regret this!((╯°□°)╯︵ ┻━┻` int); Query OK, 0 rows affected (0.03 sec)

mysql> desc You will regret this!; +--------------------------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------------+---------+------+-----+---------+-------+ | (╯°□°)╯︵ ┻━┻ | int(11) | YES | | NULL | | +--------------------------------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) ```

There is also an exception for table names. You can use a reserved word as a table name without quoting it with backticks as long as you prefix it with the schema name, but again I would discourage that. Here's an example:

mysql> create table table (id int); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table (id int)' at line 1 mysql> create table test.table (id int); Query OK, 0 rows affected (0.04 sec)

Non-reserved keywords are a little bit trickier. Many of them probably should be reserved, but are not. For example, lots of data type names are not reserved, nor is the word "view". Thus I can create a table like this without even having to use backticks:

create table view ( bit bit, bool bool, boolean boolean, date date, datetime datetime, enum enum('enum'), text text, time time, timestamp timestamp, year year, comment int comment 'int' );

Yuck. I think it's a good practice to familiarize yourself with all of the non-reserved MySQL keywords and avoid using them as identifiers, but unfortunately there is nothing to prevent you from doing so.

PlanetMySQL Voting: Vote UP / Vote DOWN



TEL/電話+86 13764045638
QQ 47079569