我的MySQL CASE / WHEN语法出了什么问题？

I'm trying to learn the ropes of some new MySQL syntax and am having trouble. This should be simple...

我正在尝试学习一些新的MySQL语法的绳索并遇到麻烦。这应该很简单......

I'm following along with the manual here: http://dev.mysql.com/doc/refman/5.5/en/case.html

我在这里关注手册:http://dev.mysql.com/doc/refman/5.5/en/case.html

but I keep getting a syntax error. Here is my routine:

但我不断收到语法错误。这是我的例程:

# Drop anonymous accounts, if any
USE mysql;
CASE (SELECT COUNT(*) FROM user WHERE User = '' AND Host = 'localhost') 
 WHEN 1 THEN
  DROP USER ''@'localhost';
  FLUSH PRIVILEGES; 
END CASE;

The error is:

错误是:

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 'CASE (SELECT COUNT(*) FROM user WHERE User = '' AND Host = 'localhost')

错误1064(42000):您的SQL语法有错误;查看与您的MySQL服务器版本对应的手册,以便在'CASE(SELECT COUNT(*)FROM user WHERE User =''AND Host ='localhost')附近使用正确的语法

Thanks in advance.

提前致谢。

1 个解决方案

#1

After reviewing your comment regarding the fixed statement but immediate second issue, it was clear that you're not using this within a stored procedure or function. The documentation for flow control statements very subtly states that they need to be within stored procedures/functions.

在审核了您对固定语句的评论但是第二个问题后,很明显您没有在存储过程或函数中使用它。流控制语句的文档非常巧妙地指出它们需要在存储过程/函数中。

Update your code to be within a procedure, and then just call the procedure to execute:

更新您的代码以在一个过程中,然后只需调用该过程来执行:

USE mysql;

DROP PROCEDURE p;
DELIMITER |
CREATE PROCEDURE p() BEGIN
    CASE (SELECT COUNT(*) FROM user WHERE User = '' AND Host = 'localhost') 
        WHEN 1 THEN
            DROP USER ''@'localhost';
            FLUSH PRIVILEGES;
        ELSE
            SELECT 'no users found!';
    END CASE;
END;
|

CALL p();

Also note that I added a catch-all ELSE block; if you don't catch the value, CASE will throw a "Case not found" warning - which may or may not be desirable.

还要注意我添加了一个全能的ELSE块;如果您没有抓住该值,CASE将抛出“未找到案例”警告 - 这可能是也可能不是。

#1