Syntax errors caused by using reserved words as table names or column names in MySQL

Question

I'm trying to execute a simple MySQL query like this:

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

But I get the following error:

Error 1064 (42000): There is an error in your SQL syntax; check the manual for your MySQL server version for 'key) VALUES ('Tim', 'Florida', 42 )' Correct syntax to use near

how to solve this problem?

P粉545910687 · 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 whenever you use them as table names, column names, or other types of identifiers - unless you enclose the identifier in backticks.

As stated in the official documentation, in 10.2 Schema Object Name em> (emphasis added):

A complete list of keywords and reserved words can be found in the 10.3 Keywords section and Reserved Words . In this page, words followed by "(R)" are reserved words. Some reserved words are listed below, including many that tend to cause this problem.

Add to
and
Before
author
Call
CASE
condition
delete
Descending order
describe
from
group
exist
index
insert
interval
yes
The essential
LIKE
limit
LONG
match
No
Options
or
order
Partition
Ranking
references
choose
TABLE
To
renew
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 name for your column that is not a reserved word.

This has several advantages:

It eliminates the possibility that you or other developers working with the database will accidentally write syntax errors because they forgot or did not 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 creating traps 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 (actually MySQL in ANSI SQL mode, as described here) uses double quotes to quote identifiers. Therefore, queries that use backticks to quote identifiers are less portable to other SQL dialects.

2. Use backtick

If the table or column cannot be renamed, enclose the identifier in question in backticks (

`

), as quoted earlier 10.2 Schema Object Names . Example demonstrating usage (taken from

10.3 Keywords and Reserved Words

):

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