mysql if 报错,MySQL事件If语句END IF错误

Trying to create a MySQL event that resets a soldout status (indicated as 1).

The logic I'm trying to create is a simple IF statement.

If soldout = 1 AND Quantity > 0 in the same row

UPDATE soldout to 0

Here is my event

CREATE EVENT soldoutreset

ON SCHEDULE EVERY 5 MINUTE

DO

IF db.Inventory.soldout = 1

AND db.Inventory.Quantity > 0 THEN

UPDATE db.Inventory SET soldout = 0

WHERE soldout = 1 AND Quantity > 1

END IF;

This is resulting in an error, 'END IF' at line 9

#1064 - 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 'END IF' at line 9

Can anyone tell why this is not working?

解决方案

This should work, you are missing a part of the syntax:

DELIMITER |

CREATE EVENT soldoutreset

ON SCHEDULE EVERY 5 MINUTE

DO

BEGIN

IF db.Inventory.soldout = 1 AND db.Inventory.Quantity > 0 THEN

UPDATE db.Inventory SET soldout = 0

WHERE soldout = 1 AND Quantity > 1;

END IF;

END |

DELIMITER ;

Update:

If the event is not running as expected, it might be because the event scheduler is off. It can be enabled with:

SET GLOBAL event_scheduler = ON;