mysql case语法错误,我的MySQL CASE / WHEN语法有什么问题？

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

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

Thanks in advance.

解决方案

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.