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?
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)"; ^
^
No comments:
Post a Comment