MySQL syntax error: problems caused by using reserved words as table or column names

Question

I'm trying to execute the following simple MySQL query:

INSERT INTO user_details (username, location, key)
VALUES ('Tim', 'Florida', 42)

But I get the following error:

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 'key) VALUES ('Tim', ' Florida', 42)' at line 1

How do I resolve this issue?

Answer 1

question

In MySQL, certain words such as SELECT, INSERT, DELETE, etc. are reserved words. Because they have a special meaning, MySQL treats it as a syntax error when you use them as table names, column names, or other types of identifiers - unless you enclose the identifier in backticks.

As noted in the official documentation, in section 10.2 Schema Object Names (emphasis added):

A complete list of keywords and reserved words can be found in Section 10.3 Keywords and Reserved Words . In this page, words with "(R)" are reserved words. Some reserved words are listed below, including many that may cause this problem.

• ADD
• AND
• BEFORE
• BY
• CALL
• CASE
• CONDITION
• DELETE
• DESC
• DESCRIBE
• FROM
• GROUP
• IN
• INDEX
• INSERT
• INTERVAL
• IS
• KEY
• LIKE
• LIMIT
• LONG
• MATCH
• NOT
• OPTION
• OR
• ORDER
• PARTITION
• RANK
• REFERENCES
• SELECT
• TABLE
• TO
• UPDATE
• WHERE

solution

You have two options.

1. Do not use reserved words as identifiers

The simplest solution is to avoid using reserved words as identifiers. You may be able to find another reasonable column name that is not a reserved word.

This has several advantages:

• It eliminates the possibility that you or other developers working with your database will accidentally write syntax errors because they forgot or didn't know that a specific identifier was a reserved word. There are many reserved words in MySQL, and it is unlikely that most developers know all of them. By not using these words in the first place, you avoid setting a trap for yourself or future developers.

• The way identifiers are quoted differs between SQL dialects. While MySQL uses backticks to quote identifiers by default, ANSI-compliant SQL (and MySQL in ANSI SQL mode, as described in here) uses double quotes to quote identifiers. Therefore, queries that use backticks to quote identifiers are less portable to other SQL dialects.

To reduce the risk of future errors, it is often wiser than to quote identifiers with backticks.

2. Use backticks

If the table or column cannot be renamed, enclose it in backticks (`) as described in 10.2 Schema Object Names cited earlier .

The following is an example demonstrating usage (taken from 10.3 Keywords and Reserved Words):

Similarly, the query in the question can be fixed by enclosing the key key in backticks, like this:

INSERT INTO user_details (username, location, `key`)
VALUES ('Tim', 'Florida', 42)";               ^   ^