mysql - 更新触发后如何避免MySQL错误1442？

Question

我必须在 MySQL 而不是 PHP 中构建一个简单的推荐系统。当用户获得积分时，他的推荐人也必须自动获得该积分的 10%，以此类推，当积分大于 1 时。例子:吉姆赚了 20，所以他的推荐人约翰赚了 2，所以他的推荐人 Mark 没有赚钱，因为他的部分是 0.2

这是代码:

drop table if exists account;
create table test.account(
username char(100) PRIMARY KEY,
points int,
referrer char(100)
)ENGINE = InnoDB DEFAULT CHARACTER SET = latin1 COLLATE = latin1_general_cs;

drop trigger if exists updater;
DELIMITER //
CREATE TRIGGER updater AFTER UPDATE ON account FOR EACH ROW
BEGIN
SET @points_to_referrer=(NEW.points-OLD.points)*0.1;
IF(@points_to_referrer>1)THEN
    UPDATE account SET points=points+@points_to_referrer WHERE username=OLD.referrer;
END IF;
END //
DELIMITER ;

INSERT INTO `test`.`account` (`username`, `points`, `referrer`) VALUES ('john', '10', 'mark');
INSERT INTO `test`.`account` (`username`, `points`, `referrer`) VALUES ('mark', '10', 'jim');
INSERT INTO `test`.`account` (`username`, `points`, `referrer`) VALUES ('jim', '10', 'john');

...这是一个命令示例:

UPDATE `test`.`account` SET `points`='30' WHERE `username`='jim';

此命令触发 1442 错误而不是更新，因为它似乎是一个无限循环。我可以通过设置任何系统选项(可能在 my.ini 中)来强制执行此代码吗？或者还有其他方法可以解决这个问题吗？谢谢。

Answer 1

您不能更新调用触发器的表(帐户):

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

这样做会产生错误 1442:

Error Code: 1442
Can't update table 'account' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

---

解决方案:如果您只想在更新前设置一个字段的值，您可以使用 BEFORE UPDATE ON 来实现触发器和使用 NEW运算符(operator):

DROP TRIGGER IF EXISTS updater;
DELIMITER //
CREATE TRIGGER updater BEFORE UPDATE ON account FOR EACH ROW
BEGIN
    SET @points_to_referrer=(NEW.points-OLD.points)*0.1;
    IF(@points_to_referrer > 1)THEN
        SET NEW.points = NEW.points + @points_to_referrer;
    END IF;
END //
DELIMITER ;

您可以使用 BEFORE UPDATE/INSERT ON 设置字段值因为它们尚未更新/插入。您需要做的就是设置 NEW.<field> (当前行的所有值都可以使用 NEW.<field_name> 访问)到任何你想要的。