如何修复MySQL错误#1064? [英] How can I fix MySQL error #1064?

问题描述

向MySQL发出命令时，出现错误#1064语法错误".

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

1. 是什么意思?

1. What does it mean?

我该如何解决?

推荐答案

TL; DR

错误#1064表示MySQL无法理解您的命令.要解决它:

TL;DR

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

• 阅读错误消息.它告诉您您的命令中的确切位置 MySQL感到困惑.

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

检查您的命令.如果您使用编程语言创建命令，请使用echo，console.log()或等效语言来显示 entire命令，以便您可以看到它.

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.

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

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 !! #1064 是什么意思?

   错误消息可能看起来像gobbledygook，但它们(通常)提供了令人难以置信的丰富信息，并提供了足够的详细信息来查明出了什么问题.通过确切地了解MySQL会告诉您什么，您可以武装自己以解决将来的任何此类问题.

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错误是根据发生的问题的类型进行编码的. 错误#1064 是语法错误.

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.

例如，以下英语句子包含语法错误(因为不定冠词"a"必须始终在名词之前):

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.

与MySQL有什么关系?

每当向计算机发出命令时，它首先要做的一件事情就是解析"该命令以使其有意义. 语法错误"表示解析器无法理解所询问的内容，因为它不构成该语言中的有效命令:换句话说，该命令违反了编程语言的语法.

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.

请务必注意，计算机必须先了解该命令，然后才能对其执行任何操作.由于存在语法错误，MySQL不知道后面是什么，因此甚至在查看数据库之前就放弃了 ，因此架构或表内容无关.

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语法的方式.这听起来似乎很难理解，但是MySQL确实在努力为我们提供帮助.我们需要做的就是…

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不仅准确地告诉我们解析器在哪里遇到语法错误，而且还提出了修复它的建议.例如，考虑以下SQL命令:

• 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告诉我们WHERE一词看起来一切正常，但随后遇到问题.换句话说，没想到那时会遇到WHERE.

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.

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

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.

程序员通常使用编程语言创建SQL命令.例如，一个php程序可能有这样的(错误)行:

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

然后您可以添加echo $query;或var_dump($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.

MySQL还建议我们"检查与我们的MySQL版本相对应的手册以使用正确的语法".让我们开始吧.

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 v5.6，因此我将转到该版本的UPDATE命令手动输入.页面上的第一件事是命令的语法(每个命令都适用):

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.

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

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.

当然，这是一个简单的例子.但是，按照上面概述的两个步骤操作(即观察在命令中的确切位置，分析器发现语法被违反，然后与手册中对的描述进行比较). em>)，几乎可以很容易地识别出每个语法错误.

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'

同样，解析器不会期望此时遇到WHERE，因此会引发类似的语法错误—但您并没有打算将where用作SQL关键字:您曾打算将其用作确定要更新的列！但是，如架构对象名称中所述:

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;

如果 ANSI_QUOTES SQL模式为启用后，也可以在双引号内用引号引起来:

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)

这篇关于如何修复MySQL错误#1064?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！