問(wèn)題描述
我正在嘗試執(zhí)行一個(gè)簡(jiǎn)單的 MySQL 查詢，如下所示:
I'm trying to execute a simple MySQL query as below:
INSERT INTO user_details (username, location, key)
VALUES ('Tim', 'Florida', 42)
但我收到以下錯(cuò)誤:
ERROR 1064 (42000):您的 SQL 語(yǔ)法有錯(cuò)誤；檢查與您的 MySQL 服務(wù)器版本相對(duì)應(yīng)的手冊(cè)，了解在第 1 行的 'key) VALUES ('Tim', 'Florida', 42)' 附近使用的正確語(yǔ)法
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
我該如何解決這個(gè)問(wèn)題?
How can I fix the issue?
推薦答案
問(wèn)題
在 MySQL 中，SELECT、INSERT、DELETE 等某些詞是保留字.由于它們具有特殊含義，因此每當(dāng)您將它們用作表名、列名或其他類型的標(biāo)識(shí)符時(shí)，MySQL 都會(huì)將其視為語(yǔ)法錯(cuò)誤 - 除非您用反引號(hào)將標(biāo)識(shí)符括起來(lái).
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(強(qiáng)調(diào)):
As noted in the official docs, in section 10.2 Schema Object Names (emphasis added):
MySQL 中的某些對(duì)象，包括數(shù)據(jù)庫(kù)、表、索引、列、別名、視圖、存儲(chǔ)過(guò)程、分區(qū)、表空間和其他對(duì)象名稱被稱為標(biāo)識(shí)符.
Certain objects within MySQL, including database, table, index, column, alias, view, stored procedure, partition, tablespace, and other object names are known as identifiers.
...
如果標(biāo)識(shí)符包含特殊字符或者是保留字，則在引用它時(shí)必須引用它.
If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.
...
標(biāo)識(shí)符引號(hào)字符是反引號(hào)(`"):
The identifier quote character is the backtick ("`"):
完整的關(guān)鍵字和保留字列表可以在10.3 關(guān)鍵字部分找到和保留字.在該頁(yè)面中，后跟(R)"的單詞是是保留字.下面列出了一些保留字，包括許多容易導(dǎo)致此問(wèn)題的字.
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
- 描述
- 來(lái)自
- 組
- 進(jìn)入
- 索引
- 插入
- 間隔
- 是
- 密鑰
- 喜歡
- 限制
- 長(zhǎng)
- 匹配
- 不是
- 選項(xiàng)
- 或
- 訂購(gòu)
- 分區(qū)
- 排名
- 參考文獻(xiàn)
- 選擇
- 表格
- 到
- 更新
- 哪里
你有兩個(gè)選擇.
最簡(jiǎn)單的解決方案就是避免使用保留字作為標(biāo)識(shí)符.您可能會(huì)為您的列找到另一個(gè)非保留字的合理名稱.
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.
這樣做有幾個(gè)好處:
它消除了您或使用您的數(shù)據(jù)庫(kù)的其他開(kāi)發(fā)人員由于忘記 - 或不知道 - 特定標(biāo)識(shí)符是保留字而意外寫入語(yǔ)法錯(cuò)誤的可能性.MySQL 中有很多保留字，大多數(shù)開(kāi)發(fā)人員不太可能知道所有這些.一開(kāi)始就不要使用這些詞，可以避免給自己或未來(lái)的開(kāi)發(fā)人員留下陷阱.
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.
引用標(biāo)識(shí)符的方式因 SQL 方言而異.雖然 MySQL 默認(rèn)使用反引號(hào)來(lái)引用標(biāo)識(shí)符，但符合 ANSI 標(biāo)準(zhǔn)的 SQL(實(shí)際上是 ANSI SQL 模式下的 MySQL，如此處所述) 使用雙引號(hào)來(lái)引用標(biāo)識(shí)符.因此，使用反引號(hào)引用標(biāo)識(shí)符的查詢不太容易移植到其他 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.
純粹是為了降低未來(lái)出錯(cuò)的風(fēng)險(xiǎn)，這通常比反引號(hào)標(biāo)識(shí)符更明智.
Purely for the sake of reducing the risk of future mistakes, this is usually a wiser course of action than backtick-quoting the identifier.
如果無(wú)法重命名表或列，請(qǐng)將有問(wèn)題的標(biāo)識(shí)符用反引號(hào) (`) 括起來(lái)，如 10.2 架構(gòu)對(duì)象名稱.
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 關(guān)鍵字和保留字):
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)
同樣，可以通過(guò)將關(guān)鍵字key包裹在反引號(hào)中來(lái)修復(fù)來(lái)自問(wèn)題的查詢，如下所示:
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)"; ^ ^
這篇關(guān)于由于在 MySQL 中使用保留字作為表名或列名導(dǎo)致的語(yǔ)法錯(cuò)誤的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！