Syntax error due to using a reserved word as a table or column name in MySQL

Question

I'm trying to execute a simple MySQL query as below:

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

But I'm getting 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 can I fix the issue?

Macur

If you've been redirected here but you don't get any error message, you're probably failing to do error checking. Please look for advice on how to do it for your language, library and/or tool—don't just post here every time something doesn't work. Thank you! – Hyades

Why only MySQL? Is this not a problem across technologies/languages? Shouldn't such a reference/canonical question/answer be applicable irrespective of a tag? Will we have another similar reference QA separately for SQLServer because backticks won't apply there; or C or C# or VB and we might end up with a hundred such reference QA? – Stodder

@abhitalks: Because every implementation has a different flavor of SQL and what applies to MySQL might not necessarily apply to other flavors. A reference question might surface if the respective community deems a need for it, otherwise I wouldn't worry about it. And having a dozen reference questions beats having thousands of duplicate questions IMO. – Floodlight

I applied the wiki lock here because it was starting to accrue lots of answers that don't add anything. – Hydrolyze

Answer 1

The Problem

In MySQL, certain words like SELECT, INSERT, DELETE etc. are reserved words. Since they have a special meaning, MySQL treats it as a syntax error whenever you use them as a table name, column name, or other kind of identifier - unless you surround the identifier with backticks.

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

Certain objects within MySQL, including database, table, index, column, alias, view, stored procedure, partition, tablespace, and other object names are known as identifiers.

...

If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.

...

The identifier quote character is the backtick ("`"):

A complete list of keywords and reserved words can be found in section 10.3 Keywords and Reserved Words. In that page, words followed by "(R)" are reserved words. Some reserved words are listed below, including many that tend to cause this issue.

• 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

The Solution

You have two options.

1. Don't use reserved words as identifiers

The simplest solution is simply to avoid using reserved words as identifiers. You can probably find another reasonable name for your column that is not a reserved word.

Doing this has a couple of advantages:

• It eliminates the possibility that you or another developer using your database will accidentally write a syntax error due to forgetting - or not knowing - that a particular identifier is a reserved word. There are many reserved words in MySQL and most developers are unlikely to know all of them. By not using these words in the first place, you avoid leaving traps for yourself or future developers.

• The means of quoting identifiers differs between SQL dialects. While MySQL uses backticks for quoting identifiers by default, ANSI-compliant SQL (and indeed MySQL in ANSI SQL mode, as noted here) uses double quotes for quoting identifiers. As such, queries that quote identifiers with backticks are less easily portable to other SQL dialects.

Purely for the sake of reducing the risk of future mistakes, this is usually a wiser course of action than backtick-quoting the identifier.

2. Use backticks

If renaming the table or column isn't possible, wrap the offending identifier in backticks (`) as described in the earlier quote from 10.2 Schema Object Names.

An example to demonstrate the usage (taken from 10.3 Keywords and Reserved Words):

mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000): You have an error in your SQL syntax.
near 'interval (begin INT, end INT)'
mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)

Similarly, the query from the question can be fixed by wrapping the keyword key in backticks, as shown below:

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

Macur

-1. I think suggesting to use begin and end without backticks in a reference answer to this issue is particularly evil. A better practice is to use backticks, period, without having to know which keyword is reserved or non reserved. – Athematic

@MarcAlff: begin and end are not reserved words. The above example was just to demonstrate how the error message can be resolved by using backticks. And simply not using a reserved word is a better practice than blindly backtick-quoting all identifiers even when they're not needed. – Macur

I agree solution 1 is better, when someone can actually choose the identifier names. When the name can not be changed, as in solution 2, having to investigate which identifiers are keywords, and if these keywords are reserved or not (even if future versions ?), is a source of complication. BTW, removed the -1 as the example actually comes from the manual. – Athematic

@MarcAlff That's exactly why there are two solutions. If the identifier name cannot be changed, the solution there would be to quote it with a backtick. I don't see why non-reserved words should be quoted, though. It's just personal preference, I guess. – Macur

New reserved words are created frequently in MySQL, with new releases. For example, NONBLOCKING in MySQL 5.7. Quoting systematically tends to be more robust to changes, and helps upgrades. As for removing the -1, I was optimistic. You are correct, my removal failed due to this timer. – Athematic

Does a double quote(") work in MySQL, if so it would be a better option as it is in the SQL standard. – Zamboanga

It didnot work for me in sql server 2008. I instead used [ ] big brackets For eg : [interval] that did the trick for me. I hope it is helpful to somebody just in case – Celeriac

for intervals we can also use "min" and "max" column names. I was using "from" and "to" but I changed after reading this thread. Thanks guys. – Cosmonautics

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

Another disadvantage of using reserved words as identifiers: it makes searching your code impossible. If you name one of your tables Table then searching for it will return too many false positives. – Vashtivashtia

@IanRingrose You can use double quotes instead of backticks when you enable ANSI_QUOTES or ANSI mode. – Exude

@AmalMurali because "non reserved" words of today are "reserved words" of tomorrow. Who knows. I had a system failing silently because my hosting decided to upgrade MySQL to version 8.0.1 (which introduced new reserved words, like "rows"). I had one column named "rows". – Bialy

I used managed to create a column named order by renaming order_to_rename to order. – Goiter