我的MySQL CASE/WHEN语法有什么问题? [英] What is wrong with my MySQL CASE/WHEN syntax?

问题描述

我正在尝试学习一些新的MySQL语法，但是遇到了麻烦.这应该很简单...

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

在这里，我将遵循手册的内容: http://dev.mysql.com/doc/refman/5.5/en/case.html

I'm following along with the manual here: 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;

错误是:

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

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

谢谢.

推荐答案

在审查了有关固定语句的评论之后，立即发布了第二个问题，很明显，您没有在存储过程或函数中使用它. flow control statements 的文档非常含蓄它们必须位于存储过程/函数之内.

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

还要注意，我添加了一个包罗万象的ELSE块；如果您没有捕获到该值，则CASE会引发未找到案例"警告-可能是或不希望的.

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.

这篇关于我的MySQL CASE/WHEN语法有什么问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！