INSERT INTO user_details (username, location, key) VALUES ('Tim', 'Florida', 42)
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
Answer 1 Amal Murali
In MySQL, certain words like
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.
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.
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)"; ^ ^