By Amal Murali


2014-05-03 15:29:22 8 Comments

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?

1 comments

@Amal Murali 2014-05-03 15:29:22

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

@Marc Alff 2014-05-07 07:53:59

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

@Amal Murali 2014-05-07 08:01:06

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

@Marc Alff 2014-05-07 08:11:24

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.

@Amal Murali 2014-05-07 08:17:55

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

@Marc Alff 2014-05-07 08:29:48

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.

@Ian Ringrose 2014-05-08 13:02:32

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

@berserk 2014-11-10 09:44:05

I was stuck at keyword 'condition'. Thanks!

@wingskush 2015-06-26 11:16:56

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

@giovannipds 2018-05-04 17:33:46

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.

@Cafebabe 2018-07-27 05:44:04

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

@Dour High Arch 2018-11-18 18:56:24

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.

Related Questions

Sponsored Content

3 Answered Questions

[SOLVED] How to get the max of two values in MySQL?

  • 2009-10-14 11:25:39
  • Mask
  • 104820 View
  • 261 Score
  • 3 Answer
  • Tags:   mysql max

33 Answered Questions

[SOLVED] Reference - What does this error mean in PHP?

7 Answered Questions

[SOLVED] Adding multiple columns AFTER a specific column in MySQL

  • 2013-07-09 06:22:00
  • Koala
  • 483256 View
  • 304 Score
  • 7 Answer
  • Tags:   mysql ddl

10 Answered Questions

[SOLVED] How to remove constraints from my MySQL table?

6 Answered Questions

[SOLVED] How to alter a column and change the default value?

  • 2012-07-03 13:51:43
  • qazwsx
  • 260171 View
  • 158 Score
  • 6 Answer
  • Tags:   mysql sql

2 Answered Questions

[SOLVED] Cast from VARCHAR to INT - MySQL

  • 2012-08-26 01:26:30
  • Lenin Raj Rajasekaran
  • 531046 View
  • 225 Score
  • 2 Answer
  • Tags:   mysql sql

6 Answered Questions

[SOLVED] MySQL add column if not exist

  • 2013-01-17 15:02:27
  • phil88530
  • 60616 View
  • 29 Score
  • 6 Answer
  • Tags:   mysql sql

1 Answered Questions

[SOLVED] MySQL: ignore errors when importing?

1 Answered Questions

[SOLVED] MySQL syntax error in the CREATE TABLE statement

  • 2012-07-08 18:19:28
  • kevin fantini
  • 10834 View
  • -1 Score
  • 1 Answer
  • Tags:   mysql

1 Answered Questions

[SOLVED] mySQL scripts syntax error

  • 2016-02-13 17:39:33
  • Cyber Shadow
  • 60 View
  • -1 Score
  • 1 Answer
  • Tags:   mysql sql

Sponsored Content