如何修复 MySQL 错误 #1064?

2021-11-20 00:00:00 parsing syntax syntax-error mysql mysql-error-1064

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

这是什么意思?
我该如何解决?

解决方案

<块引用>

TL;DR

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

阅读错误消息.它会告诉您在您的命令中的确切位置 MySQL 被混淆了.
检查您的命令.如果您使用编程语言来创建您的命令，请使用 echo、console.log()，或等效于显示整个命令以便您可以看到它.
查看手册.通过与 MySQL 当时的预期进行比较，问题通常很明显.
检查保留字.如果错误发生在对象标识符上，请检查它是否不是保留字(如果是，请确保正确引用).

啊啊啊！！#1064 是什么意思?
错误消息可能看起来像 gobbledygook，但它们(通常)提供了令人难以置信的信息，并提供了足够的细节来查明出错的地方.通过准确理解 MySQL 告诉您的内容，您可以武装自己在未来解决此类问题.
在许多程序中，MySQL 错误是根据发生的问题的类型编码的.错误 #1064 是语法错误.
- 您所说的语法"是什么?是巫术吗?
  虽然语法"是许多程序员只在计算机上下文中遇到的词，但它实际上是从更广泛的语言学中借来的.它指的是句子结构:即语法规则；或者，换句话说，定义语言中什么构成有效句子的规则.
  例如，以下英语句子包含语法错误(因为不定冠词a"必须始终位于名词之前):
  <块引用>
  这句话包含语法错误a.
- 这与 MySQL 有什么关系?
  每当有人向计算机发出命令时，它必须做的第一件事就是解析"该命令以理解它.语法错误"意味着解析器无法理解所询问的内容，因为它不构成语言中的有效命令:换句话说，该命令违反了编程语言的语法.
  需要注意的是，计算机必须先理解该命令，然后才能对其进行任何操作.由于存在语法错误，MySQL 不知道后面是什么，因此甚至在查看数据库之前就放弃，因此架构或表内容不相关.
  莉>
我该如何解决?
显然，需要确定该命令是如何违反 MySQL 语法的.这听起来可能非常难以理解，但 MySQL 正在努力帮助我们.我们需要做的就是…
- 阅读消息！
  MySQL 不仅会准确地告诉我们解析器在何处遇到语法错误，而且还会提出修复它的建议.例如，考虑以下 SQL 命令:
```
UPDATE my_table WHERE id=101 SET name='foo'
```
  该命令产生以下错误消息:
  <块引用>
  ERROR 1064 (42000):您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的 'WHERE id=101 SET name='foo'' 附近使用的正确语法
  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，所以我将转向 UPDATE 命令的那个版本的手动输入.页面上的第一件事是命令的语法(对于每个命令都是如此):
```
UPDATE [LOW_PRIORITY] [IGNORE] table_referenceSET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...[WHERE where_condition][按...订购][LIMIT row_count]
```
  手册解释了如何在排版和语法约定，但就我们的目的而言，认识到以下内容就足够了: 方括号 [ 和 ] 中包含的子句是可选的；竖线 | 表示备选方案；和省略号 ... 表示为简洁起见省略，或者前面的条款可以重复.
  我们已经知道解析器相信我们命令中的所有内容在 WHERE 关键字之前都没有问题，或者换句话说，直到并包括表引用.查看语法，我们看到 table_reference 后面必须跟 SET 关键字:而在我们的命令中它后面实际上是 WHERE 关键字.这就解释了为什么解析器报告在那个时候遇到了问题.
预订须知
当然，这是一个简单的例子.但是，通过执行上面概述的两个步骤(即观察在命令中的确切位置，解析器发现语法被违反，并与手册中对当时预期的内容的描述进行比较em>)，几乎可以轻松识别每个语法错误.
我说几乎所有"，因为有一小类问题不太容易发现——这就是解析器认为遇到的语言元素意味着一件事而你打算它意味着另一件事的地方.举个例子:
```
UPDATE my_table SET where='foo'
```
同样，此时解析器不希望遇到 WHERE，因此会引发类似的语法错误—但您并没有打算让 where一个 SQL 关键字:您本来打算用它来标识要更新的列！但是，如架构对象名称下所述:
<块引用>
如果标识符包含特殊字符或者是保留字，则在引用它时必须引用它.(例外:限定名称中的句点后面的保留字必须是标识符，因此不需要引用.)保留字列在第 9.3 节，关键字和保留字".
```
[ deletia ]
```
标识符引号字符是反引号(`"):
```
mysql>SELECT * FROM `select` WHERE `select`.id > 100;
```
如果 ANSI_QUOTES 启用 SQL 模式，也允许在双引号内引用标识符:
```
mysql>创建表测试"(col INT)；错误 1064:您的 SQL 语法有错误...mysql>SET sql_mode='ANSI_QUOTES';mysql>创建表测试"(col INT)；查询 OK，0 行受影响(0.00 秒)
```

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

What does it mean?
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).

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.
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)
```

相关文章

如何修复 MySQL 错误 #1064?

TL;DR

啊啊啊！！#1064 是什么意思?

您所说的语法"是什么?是巫术吗?

这与 MySQL 有什么关系?

我该如何解决?

阅读消息！

检查命令的实际文本！

服从命令！

预订须知

TL;DR

Aaaagh!! What does #1064 mean?

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

What does that have to do with MySQL?

How do I fix it?

Read the message!

Examine the actual text of your command!

Obey orders!

A note of reservation