MySQL中使用保留字作为表或列名的语法错误

mysql reserved-words


我试图执行一个简单的MySQL查询,如下所示。

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

解决方案

你有两个选择。

1.不要使用保留词作为标识符

最简单的解决办法就是避免使用保留词作为标识符。你也许可以为你的列找到另一个合理的名称,而不是保留词。

这样做有几个好处。

  • 它消除了你或其他使用你的数据库的开发人员由于忘记--或不知道--某个特定标识符是一个保留词而不小心写出语法错误的可能性。在MySQL中有许多保留词,而大多数开发人员不太可能知道所有的保留词。通过不使用这些词,你可以避免给自己或未来的开发者留下陷阱。

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

纯粹是为了减少未来出错的风险,这通常是一个比反击式的识别器更明智的做法。

2.使用背棍

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