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

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?

itemprop="text">

class="normal">Answer

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 rel="noreferrer">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 href="http://dev.mysql.com/doc/refman/5.7/en/keywords.html"
rel="noreferrer">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


• REFERENCES


• SELECT


• TABLE


• TO


• UPDATE


• WHERE

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 href="http://dev.mysql.com/doc/refman/5.6/en/identifiers.html"
rel="noreferrer">10.2 Schema Object
Names.

An example to
demonstrate the usage (taken from href="http://dev.mysql.com/doc/refman/5.7/en/keywords.html" rel="noreferrer">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)"; ^
            ^