mysql - 这个简单的 MySQL 触发器的语法有什么问题？

Question

DELIMITER $$

CREATE TRIGGER ad_user AFTER DELETE

ON `user`
FOR EACH ROW BEGIN
    DECLARE max_id INT;
    DECLARE new_id INT;

    SELECT MAX(`id`) INTO max_id FROM `user`;
    IF max_id = NULL THEN
        SET max_id = 0;
    END IF

    SELECT `new_id` INTO new_id FROM `conf` WHERE `table` = 'user';

    IF max_id < new_id THEN
        UPDATE `conf` SET `new_id` = max_id WHERE `table` = 'user';
    END IF
END$$

创建触发器时出现以下错误提示:

Error Code: 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 'SELECT new_id INTO new_id FROM conf WHERE table = 'fact'; IF max_id < new_id THEN ' at line 12

谁能帮帮我？提前致谢!

Answer 1

尝试像这样编写查询:

DELIMITER $$

CREATE TRIGGER ad_user AFTER DELETE
ON `user`
FOR EACH ROW BEGIN
    DECLARE v_max_id INT;
    DECLARE v_new_id INT;

    SELECT MAX(`id`) INTO v_max_id FROM `user`;
    IF v_max_id IS NULL THEN
        SET v_max_id = 0;
    END IF;

    SELECT `new_id` INTO v_new_id FROM `conf` WHERE `table` = 'user';

    IF v_max_id < v_new_id THEN
        UPDATE conf c
            SET c.new_id = v_max_id
            WHERE c.`table` = 'user';
    END IF;
END$$

这主要是添加分号并修复了 = NULL。