MySQL软删除触发子分类parent_id置空报错求助

问题原因 #

MySQL有硬性限制：不能在触发器中修改触发它的那张表。你的AFTER UPDATE触发器触发时，原更新语句还在操作webpage_category表，此时表处于锁定状态，触发器里再对该表执行更新会引发冲突，因此抛出Can't update table 'webpage_category' in stored function/trigger错误。

可行解决方案 #

方案1：应用层（PHP）直接处理 #

在软删除分类的业务逻辑中，先更新子分类的parent_id为NULL，再执行软删除操作，完全避开触发器限制：

// 假设待删除分类ID为 $categoryId
// 第一步：将所有子分类的parent_id置为NULL
$updateChildStmt = $pdo->prepare("UPDATE webpage_category SET parent_id = NULL WHERE parent_id = ? AND deleted_at IS NULL");
$updateChildStmt->execute([$categoryId]);

// 第二步：软删除目标分类
$softDeleteStmt = $pdo->prepare("UPDATE webpage_category SET deleted_at = NOW() WHERE webpage_category_id = ?");
$softDeleteStmt->execute([$categoryId]);

方案2：使用存储过程封装逻辑 #

创建存储过程统一处理子分类更新和软删除操作，应用层调用存储过程即可：

DELIMITER //
CREATE PROCEDURE soft_delete_category(IN target_category_id INT)
BEGIN
    -- 先更新子分类的parent_id
    UPDATE webpage_category 
    SET parent_id = NULL 
    WHERE parent_id = target_category_id AND deleted_at IS NULL;
    
    -- 再执行软删除
    UPDATE webpage_category 
    SET deleted_at = NOW() 
    WHERE webpage_category_id = target_category_id;
END //
DELIMITER ;

PHP中调用存储过程：

$stmt = $pdo->prepare("CALL soft_delete_category(?)");
$stmt->execute([$categoryId]);

不推荐的复杂方案 #

如果执意要使用触发器，只能通过中间表中转：触发器将需要更新的子分类ID写入临时表，再通过事件调度器定期扫描临时表并更新原表。但这种方式存在延迟、增加系统复杂度，不建议用于实时业务场景。

内容的提问来源于stack exchange，提问作者D.B.