由于在 MySQL 中使用保留字作为表名或列名导致的语法错误

Syntax error due to using a reserved word as a table or column name in MySQL

提问by Amal Murali

I'm trying to execute a simple MySQL query as below:

我正在尝试执行一个简单的 MySQL 查询，如下所示：

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

ERROR 1064 (42000)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取'key) VALUES ('Tim', 'Florida', 42)'在第 1 行附近使用的正确语法

How can I fix the issue?

我该如何解决这个问题？

回答by Amal Murali

The Problem

问题

In MySQL, certain words like SELECT, INSERT, DELETEetc. 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.

在MySQL，某些词喜欢SELECT，INSERT，DELETE等是保留字。由于它们具有特殊含义，因此每当您将它们用作表名、列名或其他类型的标识符时，MySQL 都会将其视为语法错误 - 除非您用反引号将标识符括起来。

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

正如官方文档中所述，在第10.2节模式对象名称（强调）：

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 mustquote it whenever you refer to it.

...

The identifier quote character is the backtick("`"):

MySQL 中的某些对象，包括数据库、表、索引、列、别名、视图、存储过程、分区、表空间和其他对象名称，称为标识符。

...

如果标识符包含特殊字符或者是保留字，则在引用它时必须引用它。

...

标识符引号字符是反引号(" `")：

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.

完整的关键字和保留字列表可以在第10.3节关键字和保留字中找到。在该页面中，后跟“(R)”的词是保留字。下面列出了一些保留字，包括许多容易导致此问题的保留字。

• 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

• 添加
• 和
• 前
• 经过
• 称呼
• 案件
• 健康）状况
• 删除
• 发展研究中心
• 描述
• 从
• 团体
• 在
• 指数
• 插入
• 间隔
• 是
• 钥匙
• 喜欢
• 限制
• 长
• 比赛
• 不是
• 选项
• 或者
• 命令
• 划分
• 参考
• 选择
• 桌子
• 到
• 更新
• 在哪里

The Solution

解决方案

You have two options.

你有两个选择。

1. Don't use reserved words as identifiers

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

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.

• 它消除了您或使用您的数据库的其他开发人员由于忘记 - 或不知道 - 特定标识符是保留字而意外写入语法错误的可能性。MySQL 中有很多保留字，大多数开发人员不太可能知道所有这些。一开始就不要使用这些词，可以避免给自己或未来的开发人员留下陷阱。

• 引用标识符的方式因 SQL 方言而异。虽然MySQL使用反引号在默认情况下，符合ANSI标准SQL引用标识符（实际上在MySQL ANSI SQL模式，如注意这里）用来引用标识符双引号。因此，用反引号引用标识符的查询不太容易移植到其他 SQL 方言。

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

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.

如果无法重命名表或列，请将有问题的标识符用反引号 ( `)括起来，如10.2 Schema Object Names的前面引用所述。

An example to demonstrate the usage (taken from 10.3 Keywords and Reserved Words):

演示用法的示例（取自10.3 关键字和保留字）：

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)

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 keyin backticks, as shown below:

同样，问题中的查询可以通过将关键字包装key在反引号中来修复，如下所示：

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