为什么MySQL语法错误消息如此糟糕？

MySQL syntax errors are often pretty vague, only specifying the line where the error occurs. Writing a parser that produces accurate and useful error message is not completely trivial, but it's not an open research problem: most compilers for general purpose programming languages produce errors messages that are much more useful.

MySQL语法错误通常很模糊，只指定发生错误的行。编写一个产生准确和有用的错误消息的解析器并不是完全无关紧要的，但它不是一个开放的研究问题：大多数通用编程语言的编译器都会产生更有用的错误消息。

So why doesn't MySQL's parser produce better error messages? Is it something to do with MySQL specifically, or the SQL grammar generally that makes this extremely difficult?

那么为什么MySQL的解析器不会产生更好的错误消息呢？具体是与MySQL有关，还是一般的SQL语法使这非常困难？

Here's an example:

这是一个例子：

SELECT * FROM foo
WHERE bar > 0
AND baz NOT NULL
ORDER BY qux ASC

MySQL generates the following error message: 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 'NULL ORDER BY qux ASC'

MySQL生成以下错误消息：您的SQL语法中有错误;检查与MySQL服务器版本对应的手册，以便在'NULL ORDER BY qux ASC'附近使用正确的语法

In fact, NOT NULL should be IS NOT NULL. So why doesn't MySQL generate an error like Unexpected token NOT at line 3 column 8?

实际上，NOT NULL应该是IS NOT NULL。那么为什么MySQL不会在第3行第8列产生类似Unexpected token NOT的错误？

2 个解决方案

#1

MySQL Optimizer optimizes the query you send. By the time it is executed, it could look pretty different. Also the idea that the line an column will stay the same during that time is not correct.

MySQL Optimizer优化您发送的查询。当它被执行时，它可能看起来非常不同。此外，列在一段时间内保持不变的想法是不正确的。

Here is a look at how MySQL optimizes queries that it gets:

以下是MySQL如何优化它获得的查询：

http://dev.mysql.com/doc/internals/en/optimizer-primary-optimizations.html

Update 1:

更新1：

The above is my assumption on why error reporting isn't that good. In no way I am saying MySQL error reporting is good, it is really bad. Sometimes I get errors that totally have nothing to do with the actual error. Here are some issues Peter Zaitsev ran into (awesome blog btw for people working with MySQL):

以上是我为什么错误报告不那么好的假设。我不是说MySQL错误报告是好的，它真的很糟糕。有时我得到的错误与实际错误完全无关。以下是Peter Zaitsev遇到的一些问题（对于使用MySQL的人来说很棒的博客）：

http://www.mysqlperformanceblog.com/2011/04/06/more-on-mysql-error-messages/ http://www.mysqlperformanceblog.com/2008/03/16/mysql-error-message-nonsenses/

#2

Oracle is where open source software goes to die. They have a disincentive to make the freely available competitor to their commercial database applications very good. It was good enough that no one would make another, for a while, and now I prefer MariaDB and the other forks when I am given a choice.

Oracle是开源软件濒临死亡的地方。他们不愿意让免费提供的商业数据库应用程序的竞争对手非常好。有一段时间没有人会再创造另一个，现在我更喜欢MariaDB和其他分叉，当我给出一个选择时。

#1