如何修复MySQL错误#1064?

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?

   我怎样才能修好它呢?

6 个解决方案

#1

TL;DR

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

错误#1064意味着MySQL不能理解您的命令。修复:

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

  读取错误消息。它会告诉你在命令MySQL中哪里出现了混乱。

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

  检查手册。通过与MySQL当时的预期进行比较，问题通常是显而易见的。

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

   错误消息可能看起来像是官样文章，但它们(通常)提供了令人难以置信的信息，并提供了足够的细节来查明哪里出错了。通过准确理解MySQL告诉您的内容，您可以为将来修复此类问题做好准备。

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

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

   • 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):

     例如，下列英文句子包含一个语法错误(因为不定冠词“a”必须总是在名词之前):

     This sentence contains syntax error a.

     这个句子包含语法错误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.

     需要注意的是，计算机必须理解命令，然后才能使用它。因为有语法错误，MySQL不知道后面是什么，因此在查看数据库之前就放弃了，因此模式或表内容不相关。

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…

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

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

     MySQL不仅告诉我们解析器在什么地方遇到语法错误，而且还提供了修复语法错误的建议。例如，考虑以下SQL命令:

     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

     错误1064 (42000):SQL语法中有错误;检查与MySQL服务器版本对应的手册，找到正确的语法，以便在第1行“WHERE id=101 SET name='foo”附近使用

     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.

     MySQL告诉我们一切看起来都很好，但是后来遇到了一个问题。换句话说，它并不期望在那个时候遇到什么。

     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.

     消息说…“行……附近简单地说，命令的结束是出乎意料的:也就是说，在命令结束之前应该出现一些其他的东西。

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

     MySQL还建议我们“检查对应于MySQL版本的手册以获得正确的语法”。做一下。

     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):

     我使用的是MySQL v5.6，因此我将使用该版本的手动条目作为更新命令。页面上的第一件事就是命令的语法(每个命令都是如此):

     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.

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

   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:

   同样，解析器不期望在此时遇到类似的语法错误，但您并没有打算在哪里使用SQL关键字:您本打算让它标识一个列以进行更新!但是，正如在模式对象名称下记录的那样:

   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, “Reserved Words”.

   如果一个标识符包含特殊字符或者是一个保留字，您必须在引用它时引用它。(例外:限定名中包含句号的保留字必须是标识符，因此不需要引用。)保留词列于第9.3节“保留词”。

   [ deletia ]

   The identifier quote character is the backtick (“`”):

   标识符引号字符是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:

   如果启用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)

#2

If you receive the error with SQuirreL Client on running an SQL rxpression with a semicolon like CREATE PROCEDURE then you need the MySQL plugin. You can select it on install. It is not selected by default.

如果在运行SQL rxpression并运行一个类似CREATE过程的分号时收到SQuirreL客户机的错误，那么您需要MySQL插件。您可以在安装时选择它。默认情况下不选择它。

#3

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。所以我用自定义分隔符包装了这个过程，它运行得很好。

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:

把定界器放进去后，就像这样:

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 ;

#4

You also get this error when you are attempting to insert JSON or other data with special characters, without the needed quotes, eg:

当您试图插入带有特殊字符的JSON或其他数据而不使用所需的引号时，也会出现此错误，例如:

UPDATE myTable SET myJSONfield = {};

change it to

将其更改为

UPDATE myTable SET myJSONfield = '{}';

#5

Various reason for it.. say for e.g. if we Declare any variable then it should be before any other type of statements. or else we need to put a BEGIN block before that.

各种原因. .例如，如果我们声明任何变量，那么它应该在任何其他类型的语句之前。或者我们需要在此之前设置一个开始块。

DECLARE _RoomID INTEGER ;

    SET _dtTodayTmp=NOW();
    SET _dtToday=DATE_FORMAT(_dtTodayTmp,"%m/%d/%y");
    SET _tmNow=DATE_FORMAT(_dtTodayTmp,"%h:%i:%s");

    DECLARE tree_cursor1 CURSOR 
    FOR SELECT roomid FROM reservationDet rd WHERE rd.status=3 AND rd.compcode=pCompCode; 

gives error so we need to make it

给出错误，所以我们需要去做

DECLARE _RoomID INTEGER ;
    SET _dtTodayTmp=NOW();
    SET _dtToday=DATE_FORMAT(_dtTodayTmp,"%m/%d/%y");
    SET _tmNow=DATE_FORMAT(_dtTodayTmp,"%h:%i:%s"); 

    **BEGIN**
        DECLARE tree_cursor1 CURSOR
        FOR SELECT roomid FROM reservationDet WHERE STATUS = 3 AND compcode = pCompCode ; 

#6

It might be because your string or inserted data contain a single quote ' you can try

这可能是因为您的字符串或插入的数据包含一个单引号“您可以尝试”。

mysql_real_escape_string() for mysql or mysqli_real_escape_string() for mysqli

function to escape the string while inserting data(insert query) into database.

函数在向数据库插入数据(插入查询)时转义字符串。