解决MySQL BEFORE INSERT触发器查询同表的目标表指定报错

问题背景 #

创建shop_payment表的BEFORE INSERT触发器时触发错误，需求为：

• 获取同一shop_id下，满足paid_till > 新记录初始paid_on的最新支付记录paid_till
• 将该值设为新记录的paid_on，若无匹配值则用当前日期
• 根据tariff_id计算新记录的paid_till和transaction_fee

尝试嵌套子查询、临时表方法未解决，以下为原代码及表结构参考。

错误原因 #

MySQL不允许在触发器中直接查询并修改同一张表的字段，即使使用嵌套子查询，若查询条件依赖待修改的NEW.paid_on，会触发循环依赖，触发目标表更新限制。

解决代码 #

修改后的触发器代码可绕过该限制，同时保留原有业务逻辑：

CREATE DEFINER = CURRENT_USER TRIGGER `mydb_myisam`.`shop_payment_BEFORE_INSERT` BEFORE INSERT ON `shop_payment` FOR EACH ROW
BEGIN
    DECLARE initial_paid_on DATE;
    DECLARE last_paid_till DATE;

    -- 保存新记录初始paid_on值，避免后续修改影响查询条件
    SET initial_paid_on = NEW.paid_on;

    -- 通过双层派生表绕过目标表自引用限制，获取符合条件的最大paid_till
    SELECT MAX(temp.paid_till) INTO last_paid_till
    FROM (
        SELECT paid_till 
        FROM shop_payment 
        WHERE shop_id = NEW.shop_id
    ) AS temp
    WHERE temp.paid_till > initial_paid_on;

    -- 设置新记录的paid_on
    SET NEW.paid_on = IFNULL(last_paid_till, CURRENT_DATE());

    -- 根据tariff_id计算paid_till和transaction_fee
    CASE NEW.tariff_id
        WHEN 1 THEN 
            SET NEW.paid_till = DATE_ADD(NEW.paid_on, INTERVAL 30 DAY);
            SET NEW.transaction_fee = 0.2;
        WHEN 2 THEN
            SET NEW.paid_till = DATE_ADD(NEW.paid_on, INTERVAL 90 DAY);
            SET NEW.transaction_fee = 0.5;
        WHEN 3 THEN
            SET NEW.paid_till = DATE_ADD(NEW.paid_on, INTERVAL 180 DAY);
            SET NEW.transaction_fee = 2;
        WHEN 4 THEN
            SET NEW.paid_till = DATE_ADD(NEW.paid_on, INTERVAL 365 DAY);
            SET NEW.transaction_fee = 5;
    END CASE;
END

修改说明 #

1. 保存初始值：将NEW.paid_on的初始值存入变量initial_paid_on，避免后续修改NEW.paid_on导致查询条件逻辑混乱
2. 双层派生表：通过内层查询获取目标表数据生成临时结果集，外层查询基于临时结果集计算最大值，绕过MySQL对触发器中自引用表的更新限制
3. 保留业务逻辑：后续的paid_till和transaction_fee计算逻辑与原代码完全一致，确保业务需求不变

原代码参考 #

原触发器代码 #

CREATE DEFINER = CURRENT_USER TRIGGER `mydb_myisam`.`shop_payment_BEFORE_INSERT` BEFORE INSERT ON `shop_payment` FOR EACH ROW
BEGIN
    DECLARE last_paid_till DATE;

    SELECT MAX(paid_till) INTO last_paid_till FROM shop_payment
    WHERE shop_id = NEW.shop_id
    AND paid_till > NEW.paid_on;
    
    SET NEW.paid_on = IFNULL(last_paid_till, CURRENT_DATE);

    CASE NEW.tariff_id
    WHEN 1 THEN 
    SET NEW.paid_till = DATE_ADD(NEW.paid_on, INTERVAL 30 DAY);
    SET NEW.transaction_fee = 0.2;
    WHEN 2 THEN
    SET NEW.paid_till = DATE_ADD(NEW.paid_on, INTERVAL 90 DAY);
    SET NEW.transaction_fee = 0.5;
    WHEN 3 THEN
    SET NEW.paid_till = DATE_ADD(NEW.paid_on, INTERVAL 180 DAY);
    SET NEW.transaction_fee = 2;
    WHEN 4 THEN
    SET NEW.paid_till = DATE_ADD(NEW.paid_on, INTERVAL 365 DAY);
    SET NEW.transaction_fee = 5;
    END CASE;
END

尝试的嵌套子查询代码 #

SET NEW.paid_on = IFNULL(
    (SELECT * FROM (
       SELECT MAX(paid_till)
       FROM shop_payment
       WHERE shop_id = NEW.shop_id
       AND paid_till > NEW.paid_on)
    as temp),
    CURRENT_DATE);

shop_payment表结构 #

CREATE TABLE `shop_payment` (
  `paypal_payment_id` varchar(45) COLLATE utf8mb3_unicode_ci NOT NULL,
  `shop_id` int NOT NULL,
  `tariff_id` int NOT NULL,
  `details` text COLLATE utf8mb3_unicode_ci NOT NULL,
  `transaction_fee` float NOT NULL DEFAULT '0',
  `paid_on` date NOT NULL DEFAULT (curdate()),
  `paid_till` date NOT NULL,
  PRIMARY KEY (`paypal_payment_id`,`shop_id`),
  KEY `fk_shop_payment_shop_tariff1_idx` (`tariff_id`),
  KEY `fk_shop_payment_shop1_idx` (`shop_id`) /*!80000 INVISIBLE */,
  KEY `paid_on_idx` (`paid_on`) /*!80000 INVISIBLE */,
  KEY `shop_paid_till_paid_on` (`shop_id`,`paid_till`,`paid_on`) /*!80000 INVISIBLE */,
  KEY `paid_till_shop_paid_on` (`paid_till`,`shop_id`,`paid_on`),
  KEY `shop_payment_tariff_paid_idx` (`tariff_id`,`paid_on`),
  KEY `shop_paid` (`shop_id`,`paid_on`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

内容的提问来源于stack exchange，提问作者klyonsie