Flite Careers

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:

1
2
3
4
5
6
7
8
9
10
mysql> create table `You 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:

1
2
3
4
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
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.

Comments

Comments