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

2021-11-20 00:00:00 mysql reserved-words

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

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

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

但我收到以下错误:

ERROR 1064 (42000):您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的 '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

我该如何解决这个问题?

How can I fix the issue?

推荐答案

问题

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

The Problem

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.

如官方文档中所述，在10.2 Schema Object Names(强调):

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

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

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.

...

标识符引号字符是<​​em>反引号(`"):

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

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

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.

  • 添加
  • AND
  • 之前
  • 由
  • 打电话
  • 案例
  • 条件
  • 删除
  • DESC
  • 描述
  • 来自
  • 组
  • 进入
  • 索引
  • 插入
  • 间隔
  • 是
  • 密钥
  • 喜欢
  • 限制
  • 长
  • 匹配
  • 不是
  • 选项
  • 或
  • 订购
  • 分区
  • 排名
  • 参考文献
  • 选择
  • 表格
  • 到
  • 更新
  • 哪里

你有两个选择.

最简单的解决方案就是避免使用保留字作为标识符.您可能会为您的列找到另一个非保留字的合理名称.

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.

这样做有几个好处:

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

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

引用标识符的方式因 SQL 方言而异.虽然 MySQL 默认使用反引号来引用标识符，但符合 ANSI 标准的 SQL(实际上是 ANSI SQL 模式下的 MySQL，如此处所述) 使用双引号来引用标识符.因此，使用反引号引用标识符的查询不太容易移植到其他 SQL 方言中.

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.

如果无法重命名表或列，请将有问题的标识符用反引号 (`) 括起来，如 10.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.3 关键字和保留字):

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)

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

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

相关文章