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:

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 upvote
  flag
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! – Álvaro González
1 upvote
  flag
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
4 upvote
  flag
@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
upvote
  flag
I applied the wiki lock here because it was starting to accrue lots of answers that don't add anything. – Flexo

1 Answers 11

up vote 193 down vote accepted

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
  • 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)";               ^   ^
17 upvote
  flag
-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
10 upvote
  flag
@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. – Amal Murali
2 upvote
  flag
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
upvote
  flag
@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. – Amal Murali
13 upvote
  flag
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
upvote
  flag
Does a double quote(") work in MySQL, if so it would be a better option as it is in the SQL standard. – Ian Ringrose
upvote
  flag
I was stuck at keyword 'condition'. Thanks! – berserk
1 upvote
  flag
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 – wingskush

Not the answer you're looking for? Browse other questions tagged or ask your own question.