如何修复 MySQL 错误 #1064？

How can I fix MySQL error #1064?

提问by eggyal

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

向 MySQL 发出命令时，出现错误 #1064“语法错误”。

1. What does it mean?

2. How can I fix it?

1. 这是什么意思？

2. 我该如何解决？

回答by eggyal

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 commandMySQL 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 commandso 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).

TL; 博士

错误 #1064 表示 MySQL 无法理解您的命令。要解决这个问题：

• 阅读错误消息。它会准确地告诉你MySQL在命令中的哪个地方被混淆了。

• 检查你的命令。如果您使用的编程语言来创建你的命令，使用echo，console.log()或同等学历，以显示完整的命令，所以你可以看到它。

• 检查手册。通过与当时 MySQL 的预期进行比较，问题通常很明显。

• 检查保留字。如果错误发生在对象标识符上，请检查它是否不是保留字（如果是，请确保它被正确引用）。

1. Aaaagh!! What does #1064 mean?

   Error messages may looklike 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 typeof problem that occurred. Error #1064is 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 sentencewithin 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 databaseand 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 exactlywhere 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 WHEREat 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 UPDATEcommand. 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 WHEREkeyword, or in other words up to and including the table reference. Looking at the grammar, we see that table_referencemust be followed by the SETkeyword: whereas in our command it was actually followed by the WHEREkeyword. 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 commandthe 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 WHEREat this point and so will raise a similar syntax error—but you hadn't intended for that whereto 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 mustquote 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_QUOTESSQL 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)

1. 啊啊啊！！#1064是什么意思？

   错误消息可能看起来像 gobbledygook，但它们（通常）提供了令人难以置信的信息，并提供了足够的详细信息来查明问题所在。通过准确理解 MySQL 告诉您的内容，您可以武装自己在未来解决此类问题。

   与许多程序一样，MySQL 错误根据发生的问题类型进行编码。 错误 #1064是语法错误。

   • 你说的这个“语法”是什么？是巫术吗？

     虽然“语法”是许多程序员只在计算机上下文中遇到的词，但它实际上是从更广泛的语言学中借来的。它指的是句子结构：即语法规则；或者，换句话说，定义语言中有效句子的构成规则。

     例如，以下英语句子包含语法错误（因为不定冠词“a”必须始终位于名词之前）：

     这句话包含语法错误a。

   • 这和 MySQL 有什么关系？

     每当有人向计算机发出命令时，它必须做的第一件事就是“解析”该命令以理解它。“语法错误”意味着解析器无法理解所询问的内容，因为它不构成语言中的有效命令：换句话说，该命令违反了编程语言的语法。

     需要注意的是，计算机必须先理解该命令，然后才能对其进行任何操作。因为存在语法错误，MySQL 不知道后面是什么，因此在查看数据库之前就放弃了，因此模式或表内容不相关。

2. 我如何解决它？

   显然，需要确定该命令如何违反 MySQL 的语法。这听起来可能非常难以理解，但 MySQL 正在努力帮助我们。我们需要做的就是……

   • 阅读留言！

     MySQL 不仅会准确地告诉我们解析器在何处遇到语法错误，还会提出修复它的建议。例如，考虑以下 SQL 命令：

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

     该命令产生以下错误消息：

     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 告诉我们一切似乎都很好WHERE，但后来遇到了问题。也就是说，它没想到会WHERE在那个时候相遇。

     ...near '' at line...仅表示意外遇到命令结束的消息：也就是说，在命令结束之前应该出现其他内容。

   • 检查命令的实际文本！

     程序员经常使用编程语言创建 SQL 命令。例如，一个 php 程序可能有这样一个（错误的）行：

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

     如果你把这个写成两行

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

     然后您可以添加echo $query;或var_dump($query)查看查询实际上说

     UPDATE userSET name='foo' WHERE id=101
     

     通常，您会立即看到错误并能够修复它。

   • 服从命令！

     MySQL 还建议我们“检查与我们的 MySQL 版本相对应的手册以了解要使用的正确语法”。让我们这样做。

     我使用的是 MySQL v5.6，因此我将转向该版本的手册条目以获取UPDATEcommand。页面上的第一件事是命令的语法（对于每个命令都是如此）：

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

     该手册介绍如何解释下这句法排版和语法约定，但对我们而言这足以认识到：条款包含在方括号中[和]是可选的; 竖线|表示备选方案；和省略号...表示为简洁起见省略，或者前面的条款可以重复。

     我们已经知道解析器相信我们命令中的所有内容在WHERE关键字之前都没有问题，或者换句话说，直到并包括表引用。查看语法，我们看到table_reference必须跟在SET关键字之后：而在我们的命令中，它实际上跟在WHERE关键字之后。这解释了为什么解析器报告在那时遇到了问题。

   预订须知

   当然，这只是一个简单的例子。然而，通过遵循上面概述的两个步骤（即准确地观察解析器在命令中发现违反语法的位置，并与手册中对该点预期内容的描述进行比较），几乎可以轻松识别每个语法错误。

   我说“几乎所有”，因为有一小部分问题不太容易发现——这就是解析器认为遇到的语言元素意味着一件事而你打算它意味着另一件事的地方。以下面的例子为例：

   UPDATE my_table SET where='foo'
   

   同样，解析器不希望WHERE在此时遇到，因此会引发类似的语法错误——但您并不打算where将其作为 SQL 关键字：您打算让它标识要更新的列！但是，如Schema Object Names 下所述：

   如果标识符包含特殊字符或者是保留字，则在引用它时必须引用它。（例外：限定名称中的句点后面的保留字必须是标识符，因此不需要用引号引起来。）第 9.3 节“关键字和保留字”中列出了保留字。

   [ deletia ]

   标识符引号字符是反引号（“ `”）：

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

   如果ANSI_QUOTES启用了SQL 模式，也允许在双引号内引用标识符：

   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)

回答by Umair Malhi

For my case, I was trying to execute procedure code in MySQL, and due to some issue with server in which Server can't figure out where to end the statement I was getting Error Code 1064. So I wrapped the procedure with custom DELIMITER and it worked fine.

就我而言，我试图在 MySQL 中执行过程代码，并且由于服务器的一些问题，服务器无法确定语句的结束位置，我收到了错误代码 1064。所以我用自定义 DELIMITER 包装了过程，然后它工作正常。

For example, Before it was:

例如，之前是：

DROP PROCEDURE IF EXISTS getStats;
CREATE PROCEDURE `getStats` (param_id INT, param_offset INT, param_startDate datetime, param_endDate datetime)
BEGIN
    /*Procedure Code Here*/
END;

After putting DELIMITER it was like this:

放置 DELIMITER 后是这样的：

DROP PROCEDURE IF EXISTS getStats;
DELIMITER $$
CREATE PROCEDURE `getStats` (param_id INT, param_offset INT, param_startDate datetime, param_endDate datetime)
BEGIN
    /*Procedure Code Here*/
END;
$$
DELIMITER ;