mysql reserved-words

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?


  • 13

    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!

    May 6, 2014 at 9:53

  • 1

    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?

    – Abhitalks

    May 7, 2014 at 8:34

  • 4

    @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.

    – BoltClock

    May 14, 2014 at 12:43

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

    – Flexo

    Dec 3, 2014 at 10:07


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
  • BY
  • CALL
  • CASE
  • DESC
  • FROM
  • IN
  • IS
  • KEY
  • LIKE
  • LONG
  • NOT
  • OR
  • RANK
  • TO

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)";               ^   ^


  • 25

    -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.

    – Marc Alff

    May 7, 2014 at 7:53

  • 17

    @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.

    May 7, 2014 at 8:01

  • 3

    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.

    – Marc Alff

    May 7, 2014 at 8:11

  • 16

    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.

    – Marc Alff

    May 7, 2014 at 8:29

  • 3

    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.

    Nov 18, 2018 at 18:56