mysql - 发现mysql if条件语法错误

Question

使用以下sql代码:

CREATE TRIGGER yirmi_test BEFORE UPDATE ON `orders`
FOR EACH ROW
IF ((OLD.`product_price_currency` != "") AND (NEW.`product_price_currency` = "")) THEN
signal sqlstate '45000' set message_text = 'Erasing currency';
END IF;

我收到以下错误:

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 '' at line 4

Answer 1

我觉得你很好。只需使用以下开始和结束分隔符 block 进行包装即可。它通过了我的 1064 测试:

DELIMITER $$
CREATE TRIGGER yirmi_test BEFORE UPDATE ON `orders`
FOR EACH ROW
IF ((OLD.`product_price_currency` != "") AND (NEW.`product_price_currency` = "")) THEN
signal sqlstate '45000' set message_text = 'Erasing currency';
END IF;
$$
DELIMITER ;

阅读 This 的底部关于分隔符。这虽然微不足道，但可能会浪费几个小时。