由于在MySQL中使用保留字作为表或列名而导致的语法错误 [英] Syntax error due to using a reserved word as a table or column name in MySQL
问题描述
我正在尝试执行一个简单的MySQL查询，如下所示:
I'm trying to execute a simple MySQL query as below:
INSERT INTO user_details (username, location, key)
VALUES ('Tim', 'Florida', 42)
但是我遇到了以下错误:
But I'm getting the following error:
错误1064(42000):您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第1行的
'key) VALUES ('Tim', 'Florida', 42)'附近使用
如何解决此问题?
推荐答案
问题
在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架构对象名称 (添加了重点):
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.
...
标识符引号字符是<strong> 反引号 ("`"):
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
- 之前
- BY
- 致电
- 案例
- 条件
- 删除
- DESC
- DESCRIBE
- 从
- 组
- IN
- INDEX
- 插入
- 间隔
- IS
- KEY
- 喜欢
- LIMIT
- 长
- MATCH
- 不
- OPTION
- OR
- 订购
- PARTITION
- 参考
- 选择
- 表
- TO
- 更新
- 在哪里
- 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 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.
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)"; ^ ^
这篇关于由于在MySQL中使用保留字作为表或列名而导致的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
