問題描述

向 MySQL 發(fā)出命令時(shí)，我收到錯(cuò)誤 #1064語法錯(cuò)誤".

1. 這是什么意思?

2. 我該如何解決?

<塊引用>

TL;DR

錯(cuò)誤 #1064 表示 MySQL 無法理解您的命令.修復(fù)它:

• 閱讀錯(cuò)誤消息.它會告訴您在您的命令中的確切位置 MySQL 被混淆了.

• 檢查您的命令.如果您使用編程語言來創(chuàng)建您的命令，請使用 echo、console.log()，或等效于顯示整個(gè)命令以便您可以看到它.

• 查看手冊.通過與 MySQL 當(dāng)時(shí)的預(yù)期進(jìn)行比較，問題通常很明顯.

• 檢查保留字.如果錯(cuò)誤發(fā)生在對象標(biāo)識符上，請檢查它是否不是保留字(如果是，請確保正確引用).

1. 啊啊?。?！#1064 是什么意思?

   錯(cuò)誤消息可能看起來像 gobbledygook，但它們(通常)提供了令人難以置信的信息，并提供了足夠的細(xì)節(jié)來查明出錯(cuò)的地方.通過準(zhǔn)確理解 MySQL 告訴您的內(nèi)容，您可以武裝自己在未來解決此類問題.

   在許多程序中，MySQL 錯(cuò)誤是根據(jù)發(fā)生的問題的類型編碼的.錯(cuò)誤 #1064 是語法錯(cuò)誤.

   • 您所說的語法"是什么?是巫術(shù)嗎?

     雖然語法"是許多程序員只在計(jì)算機(jī)上下文中遇到的詞，但它實(shí)際上是從更廣泛的語言學(xué)中借來的.它指的是句子結(jié)構(gòu):即語法規(guī)則；或者，換句話說，定義語言中什么構(gòu)成有效句子的規(guī)則.

     例如，以下英語句子包含語法錯(cuò)誤(因?yàn)椴欢ü谠~a"必須始終位于名詞之前):

     <塊引用>

     這句話包含語法錯(cuò)誤a.

   • 這與 MySQL 有什么關(guān)系?

     每當(dāng)有人向計(jì)算機(jī)發(fā)出命令時(shí)，它必須做的第一件事就是解析"該命令以理解它.語法錯(cuò)誤"意味著解析器無法理解所詢問的內(nèi)容，因?yàn)樗粯?gòu)成語言中的有效命令:換句話說，該命令違反了編程語言的語法.

     需要注意的是，計(jì)算機(jī)必須先理解該命令，然后才能對其進(jìn)行任何操作.由于存在語法錯(cuò)誤，MySQL 不知道后面是什么，因此甚至在查看數(shù)據(jù)庫之前就放棄，因此架構(gòu)或表內(nèi)容不相關(guān).

     莉>

2. 我該如何解決?

   顯然，需要確定該命令是如何違反 MySQL 語法的.這聽起來可能非常難以理解，但 MySQL 正在努力幫助我們.我們需要做的就是…

   • 閱讀消息！

     MySQL 不僅會準(zhǔn)確地告訴我們解析器在何處遇到語法錯(cuò)誤，而且還會提出修復(fù)它的建議.例如，考慮以下 SQL 命令:

     UPDATE my_table WHERE id=101 SET name='foo'

     該命令產(chǎn)生以下錯(cuò)誤消息:

     <塊引用>

     ERROR 1064 (42000):您的 SQL 語法有錯(cuò)誤；檢查與您的 MySQL 服務(wù)器版本相對應(yīng)的手冊，了解在第 1 行的 'WHERE id=101 SET name='foo'' 附近使用的正確語法

     MySQL 告訴我們，在WHERE 這個(gè)詞之前，一切似乎都很好，但后來遇到了問題.換句話說，它沒想到會在那個(gè)時(shí)候遇到 WHERE.

     顯示 ...near '' at line... 的消息僅表示意外遇到命令結(jié)束:也就是說，在命令結(jié)束之前應(yīng)該出現(xiàn)其他內(nèi)容.

   • 檢查命令的實(shí)際文本！

     程序員經(jīng)常使用編程語言創(chuàng)建 SQL 命令.例如，一個(gè) php 程序可能有這樣一個(gè)(錯(cuò)誤的)行:

     $result = $mysqli->query("UPDATE" . $tablename ."SET name='foo' WHERE id=101");

     如果你把這個(gè)寫成兩行

     $query = "UPDATE" .$tablename ."SET name='foo' WHERE id=101"$result = $mysqli->query($query);

     然后您可以添加 echo $query; 或 var_dump($query) 以查看查詢實(shí)際上說的是

     UPDATE userSET name='foo' WHERE id=101

     通常您會立即看到錯(cuò)誤并能夠修復(fù)它.

   • 服從命令！

     MySQL 還建議我們檢查與我們的 MySQL 版本相對應(yīng)的手冊以了解要使用的正確語法".讓我們這樣做.

     我使用的是 MySQL v5.6，所以我將轉(zhuǎn)向 UPDATE 命令的那個(gè)版本的手動輸入.頁面上的第一件事是命令的語法(對于每個(gè)命令都是如此):

     UPDATE [LOW_PRIORITY] [IGNORE] table_referenceSET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...[WHERE where_condition][按...訂購][LIMIT row_count]

     手冊解釋了如何在排版和語法約定，但就我們的目的而言，認(rèn)識到以下內(nèi)容就足夠了: 方括號 [ 和 ] 中包含的子句是可選的；豎線 | 表示備選方案；和省略號 ... 表示為簡潔起見省略，或者前面的條款可以重復(fù).

     我們已經(jīng)知道解析器相信我們命令中的所有內(nèi)容在 WHERE 關(guān)鍵字之前都沒有問題，或者換句話說，直到并包括表引用.查看語法，我們看到 table_reference 后面必須跟 SET 關(guān)鍵字:而在我們的命令中它后面實(shí)際上是 WHERE 關(guān)鍵字.這就解釋了為什么解析器報(bào)告在那個(gè)時(shí)候遇到了問題.

   預(yù)訂須知

   當(dāng)然，這是一個(gè)簡單的例子.但是，通過執(zhí)行上面概述的兩個(gè)步驟(即觀察在命令中的確切位置，解析器發(fā)現(xiàn)語法被違反，并與手冊中對當(dāng)時(shí)預(yù)期的內(nèi)容的描述進(jìn)行比較em>)，幾乎可以輕松識別每個(gè)語法錯(cuò)誤.

   我說幾乎所有"，因?yàn)橛幸恍☆悊栴}不太容易發(fā)現(xiàn)——這就是解析器認(rèn)為遇到的語言元素意味著一件事而你打算它意味著另一件事的地方.舉個(gè)例子:

   UPDATE my_table SET where='foo'

   同樣，此時(shí)解析器不希望遇到 WHERE，因此會引發(fā)類似的語法錯(cuò)誤—但您并沒有打算讓 where一個(gè) SQL 關(guān)鍵字:您本來打算用它來標(biāo)識要更新的列！但是，如架構(gòu)對象名稱 下所述:

   <塊引用>

   如果標(biāo)識符包含特殊字符或者是保留字，則在引用它時(shí)必須引用它.(例外:限定名稱中的句點(diǎn)后面的保留字必須是標(biāo)識符，因此不需要引用.)保留字列在 第 9.3 節(jié)，關(guān)鍵字和保留字".

   [ deletia ]

   標(biāo)識符引號字符是反引號(`"):

   mysql>SELECT * FROM `select` WHERE `select`.id > 100;

   如果 ANSI_QUOTES 啟用 SQL 模式，也允許在雙引號內(nèi)引用標(biāo)識符:

   mysql>創(chuàng)建表測試"(col INT)；錯(cuò)誤 1064:您的 SQL 語法有錯(cuò)誤...mysql>SET sql_mode='ANSI_QUOTES';mysql>創(chuàng)建表測試"(col INT)；查詢 OK，0 行受影響(0.00 秒)

When issuing a command to MySQL, I'm getting error #1064 "syntax error".

1. What does it mean?

2. How can I fix it?

TL;DR

Error #1064 means that MySQL can't understand your command. To fix it:

• Read the error message. It tells you exactly where in your command MySQL got confused.

• Examine your command. If you use a programming language to create your command, use echo, console.log(), or its equivalent to show the entire command so you can see it.

• Check the manual. By comparing against what MySQL expected at that point, the problem is often obvious.

• Check for reserved words. If the error occurred on an object identifier, check that it isn't a reserved word (and, if it is, ensure that it's properly quoted).

1. Aaaagh!! What does #1064 mean?

   Error messages may look like gobbledygook, but they're (often) incredibly informative and provide sufficient detail to pinpoint what went wrong. By understanding exactly what MySQL is telling you, you can arm yourself to fix any problem of this sort in the future.

   As in many programs, MySQL errors are coded according to the type of problem that occurred. Error #1064 is a syntax error.

   • What is this "syntax" of which you speak? Is it witchcraft?

     Whilst "syntax" is a word that many programmers only encounter in the context of computers, it is in fact borrowed from wider linguistics. It refers to sentence structure: i.e. the rules of grammar; or, in other words, the rules that define what constitutes a valid sentence within the language.

     For example, the following English sentence contains a syntax error (because the indefinite article "a" must always precede a noun):

     This sentence contains syntax error a.

   • What does that have to do with MySQL?

     Whenever one issues a command to a computer, one of the very first things that it must do is "parse" that command in order to make sense of it. A "syntax error" means that the parser is unable to understand what is being asked because it does not constitute a valid command within the language: in other words, the command violates the grammar of the programming language.

     It's important to note that the computer must understand the command before it can do anything with it. Because there is a syntax error, MySQL has no idea what one is after and therefore gives up before it even looks at the database and therefore the schema or table contents are not relevant.

2. How do I fix it?

   Obviously, one needs to determine how it is that the command violates MySQL's grammar. This may sound pretty impenetrable, but MySQL is trying really hard to help us here. All we need to do is…

   • Read the message!

     MySQL not only tells us exactly where the parser encountered the syntax error, but also makes a suggestion for fixing it. For example, consider the following SQL command:

     UPDATE my_table WHERE id=101 SET name='foo'
     

     That command yields the following error message:

     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 'WHERE id=101 SET name='foo'' at line 1

     MySQL is telling us that everything seemed fine up to the word WHERE, but then a problem was encountered. In other words, it wasn't expecting to encounter WHERE at that point.

     Messages that say ...near '' at line... simply mean that the end of command was encountered unexpectedly: that is, something else should appear before the command ends.

   • Examine the actual text of your command!

     Programmers often create SQL commands using a programming language. For example a php program might have a (wrong) line like this:

     $result = $mysqli->query("UPDATE " . $tablename ."SET name='foo' WHERE id=101");
     

     If you write this this in two lines

     $query = "UPDATE " . $tablename ."SET name='foo' WHERE id=101"
     $result = $mysqli->query($query);
     

     then you can add echo $query; or var_dump($query) to see that the query actually says

     UPDATE userSET name='foo' WHERE id=101
     

     Often you'll see your error immediately and be able to fix it.

   • Obey orders!

     MySQL is also recommending that we "check the manual that corresponds to our MySQL version for the right syntax to use". Let's do that.

     I'm using MySQL v5.6, so I'll turn to that version's manual entry for an UPDATE command. The very first thing on the page is the command's grammar (this is true for every command):

     UPDATE [LOW_PRIORITY] [IGNORE] table_reference
         SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
         [WHERE where_condition]
         [ORDER BY ...]
         [LIMIT row_count]
     

     The manual explains how to interpret this syntax under Typographical and Syntax Conventions, but for our purposes it's enough to recognise that: clauses contained within square brackets [ and ] are optional; vertical bars | indicate alternatives; and ellipses ... denote either an omission for brevity, or that the preceding clause may be repeated.

     We already know that the parser believed everything in our command was okay prior to the WHERE keyword, or in other words up to and including the table reference. Looking at the grammar, we see that table_reference must be followed by the SET keyword: whereas in our command it was actually followed by the WHERE keyword. This explains why the parser reports that a problem was encountered at that point.

   A note of reservation

   Of course, this was a simple example. However, by following the two steps outlined above (i.e. observing exactly where in the command the parser found the grammar to be violated and comparing against the manual's description of what was expected at that point), virtually every syntax error can be readily identified.

   I say "virtually all", because there's a small class of problems that aren't quite so easy to spot—and that is where the parser believes that the language element encountered means one thing whereas you intend it to mean another. Take the following example:

   UPDATE my_table SET where='foo'
   

   Again, the parser does not expect to encounter WHERE at this point and so will raise a similar syntax error—but you hadn't intended for that where to be an SQL keyword: you had intended for it to identify a column for updating! However, as documented under Schema Object Names:

   If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it. (Exception: A reserved word that follows a period in a qualified name must be an identifier, so it need not be quoted.) Reserved words are listed at Section 9.3, "Keywords and Reserved Words".

   [ deletia ]

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

   mysql> SELECT * FROM `select` WHERE `select`.id > 100;

   If the ANSI_QUOTES SQL mode is enabled, it is also permissible to quote identifiers within double quotation marks:

   mysql> CREATE TABLE "test" (col INT);
   ERROR 1064: You have an error in your SQL syntax...
   mysql> SET sql_mode='ANSI_QUOTES';
   mysql> CREATE TABLE "test" (col INT);
   Query OK, 0 rows affected (0.00 sec)

這篇關(guān)于如何修復(fù) MySQL 錯(cuò)誤 #1064?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！