MySQL更新触发器复用查询结果时遇1054错误的问题咨询

这个错误的核心原因是你的触发器包含多条SQL语句，但没有用BEGIN...END包裹，也没有修改语句分隔符，导致MySQL错误地将SET语句视为触发器的唯一执行内容，后续的INSERT语句脱离了触发器的上下文，自然无法识别OLD对象。

修正后的触发器代码 #

首先需要修改MySQL的语句分隔符（默认是;，会和触发器内的语句冲突），然后用BEGIN...END包裹所有逻辑：

DELIMITER //

CREATE TRIGGER trigger_name AFTER UPDATE ON your_main_table
FOR EACH ROW
BEGIN
    -- 获取bedroomhistory的最新ID（注意：并发场景下建议用339110，前提是先插入bedroomhistory）
    SET @bedroomHistoryId = (SELECT id FROM bedroomhistory ORDER BY id DESC LIMIT 1);
    
    INSERT INTO objecthistory (
        name, street, poBox, zipcode, city, idCountry, idRegion, mapLocation,
        numberOfRooms, comment, wlanCheck, ssid, password, giftBox, parkingIndoor,
        parkingOutdoor, parkingInformations, preparation, intermediateCleaning,
        finalCleaning, finalCleaningUntil, keyHandover, idBedroom, idLandlord,
        idCaretaker, idObject, modifiedByUserID, changed
    ) VALUES (
        OLD.name, OLD.street, OLD.poBox, OLD.zipCode, OLD.city, OLD.idCountry,
        OLD.idRegion, OLD.mapLocation, OLD.numberOfRooms, OLD.comment, OLD.wlanCheck,
        OLD.ssid, OLD.password, OLD.giftBox, OLD.parkingIndoor, OLD.parkingOutdoor,
        OLD.parkingInformations, OLD.preparation, OLD.intermediateCleaning, OLD.finalCleaning,
        OLD.finalCleaningUntil, OLD.keyHandover, @bedroomHistoryId, OLD.idLandlord,
        OLD.idCaretaker, OLD.id, OLD.modifiedByUserID, NEW.changed
    );
END //

DELIMITER ;

关键注意事项 #

• 多语句触发器必须用BEGIN...END包裹：当触发器包含多条SQL指令时，必须用这个块来标记触发器的执行范围，否则MySQL会将第一条语句之后的内容视为独立的SQL，脱离触发器上下文。
• 修改分隔符的必要性：默认的;会让MySQL在遇到SET语句后的;就认为触发器定义结束，所以需要临时修改分隔符（比如//），定义完成后再改回;。
• 并发场景下的ID获取问题：用SELECT id FROM bedroomhistory ORDER BY id DESC LIMIT 1获取最新ID存在并发风险——如果同时有其他会话插入bedroomhistory，你可能拿到错误的ID。如果你的逻辑是先将旧数据插入bedroomhistory，再获取该条记录的ID，建议改用339110，它是会话级别的，只会返回当前会话插入的最后一条记录的自增ID，示例如下：

  BEGIN
      -- 先插入旧数据到bedroomhistory
      INSERT INTO bedroomhistory (字段1, 字段2, ...) VALUES (OLD.字段1, OLD.字段2, ...);
      -- 获取当前会话插入的最新ID，无需查询
      SET @bedroomHistoryId = 339110;
      
      -- 再插入到objecthistory
      INSERT INTO objecthistory (...) VALUES (...);
  END
  

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