无法在MySQL语法错误中运行查询意外 [英] Unable to Run Query in MySQL syntax error unexpected

问题描述

我正在运行Workbench 5.2.47.

I'm running Workbench 5.2.47.

我用基本的数据检查编写了一个漫长的过程.如果数据库中不存在记录，则将插入该记录.

I have a long procedure I wrote with basic data checking. If a record did not exist in the database, the record would be inserted.

保存的过程没有问题，但是当我尝试运行它时，MySQL 5.5会引发错误.

The procedure saved with no problems, but MySQL 5.5 throws an error when I try running it.

时间长，并且其中包含很多公司敏感数据，或者我会在此处发布.

It is long, and has a lot of company sensitive data in it, or I would post it here.

我试图通过执行一小段代码来调试过程，但似乎无法让Workbench允许我尝试的任何事情.

I am trying to debug the procedure by executing small chunks of the code, but I can't seem to get Workbench to allow anything I try.

MySQL显示了如何在 5.1.5使用存储过程.

MySQL shows how to create a stored procedure in 5.1.5 Working with Stored Procedures.

让我向您展示一些我想写的非常基本的东西:

Let me show you something very basic I am trying to write:

DELIMITER $$
DROP PROCEDURE IF EXISTS my_test;

CREATE PROCEDURE my_test()
BEGIN
    SELECT * FROM Employees;
END $$

DELIMITER ;

有了它，Workbench给了我错误，语法错误，意外的创建，期望$ end".

With that, Workbench gives me the error, "syntax error, unexpected CREATE, expecting $end".

我不明白，但是我需要做些事情，所以我继续前进.

I don't understand that, but I need to get something done, so I am moving on.

我进行了一个简单的查询:

I make a simpler query:

SET @Count=(SELECT Count(*) FROM tbl_object_users WHERE username='jp2code');
IF (@Count < 1) THEN
    INSERT INTO tbl_object_users (username, date_time) VALUES ('jp2code', NOW());
END IF;

再次，这次我的IF语句出现错误.

Again, I get an error, this time on my IF statement.

接下来，我进入PhpMyAdmin尝试使用其数据库从那里运行某些内容:

Next, I go into PhpMyAdmin to try running something from there using its database:

SET @Count=Count(id) FROM `tbl_object_users` WHERE `username`='jp2code';

它也告诉我我的SQL语法有错误.

It, too, tells me I have an error in my SQL syntax.

我确实下载并安装了最新的Workbench 6，但是它不能解决问题-而且我不喜欢该界面，因此我将其卸载并返回到Workbench 5.2.

I did download and install the newest Workbench 6, but it did not solve the problem - and I did not like the interface, so I uninstalled it and went back to Workbench 5.2.

推荐答案

问题:

DELIMITER $$
DROP PROCEDURE IF EXISTS my_test;
CREATE PROCEDURE my_test() ...

是MySQL在DROP PROCEDURE语句行的末尾看不到分号作为语句的末尾.这是因为前一行告诉MySQL，语句终止符不是分号.您告诉MySQL，语句将以两个美元符号终止.因此，MySQL正在读取DROP PROCEDURE行，以查找语句终止符.而且读取的整个Blob都不是有效的MySQL语句，它会产生语法错误.

is that MySQL isn't seeing the semicolon at the end of the DROP PROCEDURE statement line as the end of the statement. This is because the preceding line told MySQL that the statement terminator was something other than a semicolon. You told MySQL that statements were going to be terminated with two dollar signs. So MySQL is reading the DROP PROCEDURE line, looking for the statement terminator. And the whole blob it reads is NOT a valid MySQL statement, it generates a syntax error.

解决方法:要么将DROP PROCEDURE行移至 前，要么移至DELIMITER $$行；或使用指定的定界符而不是分号终止DROP PROCEDURE语句.

The fix: either move the DROP PROCEDURE line before the DELIMITER $$ line; or terminate the DROP PROCEDURE statement with the specified delimiter rather than a semicolon.

您报告的第二个问题是语法错误.发生这种情况是因为MySQL无法将 IF 识别为有效SQL语句的开头.

The second problem you report is a syntax error. That's occurring because MySQL doesn't recognize IF as the beginning of a valid SQL statement.

IF语句在MySQL存储程序的上下文中(例如，在CREATE PROCEDURE语句中)仅有效.

The IF statement is valid only within the context of a MySQL stored program (for example, within a CREATE PROCEDURE statement.)

解决方法:仅在MySQL存储程序的上下文中使用IF语句.

The fix: Use an IF statement only within the context of a MySQL stored program.

您报告的第三个问题也是语法错误.发生这种情况是因为您没有SET语句的有效语法. SET语句为用户变量分配值的MySQL语法是:

The third problem you report is also a syntax error. That's occurring because you don't have a valid syntax for a SET statement; MySQL syntax for SET statement to assign a value to user variable is:

SET @uservar = expr 

MySQL期望等号后出现表达式. MySQL 不需要SQL语句.

MySQL is expecting an expression after the equals sign. MySQL is not expecting a SQL statement.

要将值作为SELECT语句的结果分配给用户变量，请在SELECT语句中进行分配，例如:

To assign a value to a user variable as the result from a SELECT statement, do the assignment within the SELECT statement, for example:

SELECT @Count := Count(id) FROM `tbl_object_users` WHERE `username`='jp2code'

请注意，SELECT语句中的赋值运算符为 := (等于冒号)，而不仅仅是 = .

Note that the assignment operator inside the SELECT statement is := (colon equals), not just =.

这篇关于无法在MySQL语法错误中运行查询意外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！