Reserved Words and Keywords

A reserved word is a keyword that MySQL uses in its grammar, such as SELECT, TABLE, ORDER, GROUP, KEY or INDEX. You cannot use one as a plain name. If you want to, you must put it in backticks.

1

CREATE TABLE rank (id INT);

1

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 'rank (id INT)'

(RANK became reserved in MySQL 8.0, when window functions were added. A table that was legal in MySQL 5.7 now fails.)

The fix is backticks, but the better fix is a different name:

1
2
3

CREATE TABLE player_rank (id INT, `rank` INT);
INSERT INTO player_rank VALUES (1, 3);
SELECT `rank` FROM player_rank;

1
2
3
4
5
6

+------+
| rank |
+------+
|    3 |
+------+
1 row in set

Reserved vs non-reserved

• Reserved words are blocked as plain names.
• Non-reserved words (for example STATUS, TYPE, NAME, COMMENT, DATE) have a meaning in some places, but you can use them as names without quotes. They are still best avoided.

See the list on your server

The server tells you which words it reserves:

1

SELECT COUNT(*) AS reserved_words FROM information_schema.keywords WHERE reserved = 1;

1
2
3
4
5
6

+----------------+
| reserved_words |
+----------------+
|            262 |
+----------------+
1 row in set

(The number changes between versions, which is the point.)

1

SELECT word FROM information_schema.keywords WHERE reserved = 1 AND word IN ('SELECT', 'RANK', 'GROUPS', 'STATUS', 'NAME', 'ORDER', 'USER') ORDER BY word;

1
2
3
4
5
6
7
8
9

+--------+
| WORD   |
+--------+
| GROUPS |
| ORDER  |
| RANK   |
| SELECT |
+--------+
4 rows in set

INFORMATION_SCHEMA.KEYWORDS is available in MySQL 8.0 and later. Words such as STATUS, NAME and USER are keywords but not reserved, so they are absent from the result.

New versions add reserved words

Each MySQL release may reserve new words. Upgrading from 5.7 to 8.0 added RANK, ROW_NUMBER, WINDOW, GROUPS, LATERAL, CUME_DIST and others. A column called rank that worked for years suddenly failed.

How to avoid problems

• Use descriptive names with two words: sort_order, not order; customer_rank, not rank.
• Prefix or qualify ambiguous words: user_group, post_status.
• If you already have such a name, quote it with backticks everywhere, including in application code and tools.