MySQL syntax error: problems caused by using reserved words as table or column names

Question

I'm trying to execute the following simple MySQL query: INSERTINTOuser_details(username,location,key)VALUES('Tim','Florida',42) but I get the following error: ERROR1064(42000): YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversi

P粉541796322 · Answer

question

In MySQL, certain words such as SELECT, INSERT, DELETE, etc. are reserved words. Because they have a special meaning, MySQL treats it as a syntax error when you use them as table names, column names, or other types of identifiers - unless you enclose the identifier in backticks.

As noted in the official documentation, in section 10.2 Schema Object Names (emphasis added):

A complete list of keywords and reserved words can be found in Section 10.3 Keywords and Reserved Words . In this page, words with "(R)" are reserved words. Some reserved words are listed below, including many that may cause this problem.

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
RANK
REFERENCES
SELECT
TABLE
TO
UPDATE
WHERE

solution

You have two options.

1. Do not use reserved words as identifiers

The simplest solution is to avoid using reserved words as identifiers. You may be able to find another reasonable column name that is not a reserved word.

This has several advantages:

It eliminates the possibility that you or other developers working with your database will accidentally write syntax errors because they forgot or didn't know that a specific identifier was a reserved word. There are many reserved words in MySQL, and it is unlikely that most developers know all of them. By not using these words in the first place, you avoid setting a trap for yourself or future developers.
The way identifiers are quoted differs between SQL dialects. While MySQL uses backticks to quote identifiers by default, ANSI-compliant SQL (and MySQL in ANSI SQL mode, as described in here) uses double quotes to quote identifiers. Therefore, queries that use backticks to quote identifiers are less portable to other SQL dialects.

To reduce the risk of future errors, it is often wiser than to quote identifiers with backticks.

2. Use backticks

If the table or column cannot be renamed, enclose it in backticks (`) as described in 10.2 Schema Object Names cited earlier .

The following is an example demonstrating usage (taken from 10.3 Keywords and Reserved Words):

Similarly, the query in the question can be fixed by enclosing the key key in backticks, like this:

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