SQL不同ID表字段相减、INSERT触发UPDATE操作问题咨询

现有代码的核心问题 #

1. 语法错误：你拼接的SQL包含INSERT、SELECT、UPDATE三条语句，mysqli_query() 默认仅支持单次执行单条SQL，多语句执行需要调用mysqli_multi_query()，但更推荐分开执行保证可控性。同时多余的SELECT语句会直接导致整段SQL语法失效。
2. 关联逻辑错误：你给出的示例数据中table1和table2的ID完全无重叠，ON table1.ID = table2.ID的关联条件匹配不到任何数据，自然不会触发任何更新。
3. 需求匹配问题：你需要的是插入消息时自动更新，在业务代码里写更新逻辑可以实现，但更符合需求的方案是用MySQL触发器，完全不需要在PHP里写额外的更新代码。

解决方案 #

方案1：PHP端执行两条SQL（加事务保证一致性） #

直接修改你的PHP代码即可，适合需要灵活调整更新逻辑的场景：

if(isset($_SESSION['id'])){
    include_once "config.php";
    $outgoing_id = $_SESSION['id'];
    $incoming_id = mysqli_real_escape_string($conn, $_POST['incoming_id']);
    $message = mysqli_real_escape_string($conn, $_POST['message']);
    if(!empty($message)){
        // 开启事务保证操作一致性
        mysqli_begin_transaction($conn);
        try {
            // 插入消息数据
            $insert_sql = "INSERT INTO messages (incoming_msg_id, outgoing_msg_id, msg)
                        VALUES ({$incoming_id}, {$outgoing_id}, '{$message}')";
            mysqli_query($conn, $insert_sql);
            // 执行更新操作，可根据你的实际关联规则修改ON和WHERE的匹配条件
            // 示例默认假设outgoing_id对应table1.ID、incoming_id对应table2.ID
            $update_sql = "UPDATE table1
                        INNER JOIN table2 ON table2.ID = {$incoming_id}
                        SET table1.X = table1.X - table2.Y
                        WHERE table1.ID = {$outgoing_id}";
            mysqli_query($conn, $update_sql);
            // 提交事务
            mysqli_commit($conn);
        } catch (Exception $e) {
            // 操作出错回滚，避免数据不一致
            mysqli_rollback($conn);
            die($e->getMessage());
        }
    }
}

方案2：MySQL触发器（真正实现插入自动触发） #

不需要修改现有PHP插入代码，只要在MySQL中创建触发器即可，插入messages表数据时会自动执行更新逻辑：
在MySQL命令行或可视化管理工具中执行以下语句创建触发器：

DELIMITER //
CREATE TRIGGER update_table1_x_after_insert_msg
AFTER INSERT ON messages
FOR EACH ROW
BEGIN
    -- 可根据实际关联规则调整匹配条件
    UPDATE table1
    INNER JOIN table2 ON table2.ID = NEW.incoming_msg_id
    SET table1.X = table1.X - table2.Y
    WHERE table1.ID = NEW.outgoing_msg_id;
END //
DELIMITER ;

触发器创建完成后，只要有数据插入messages表，就会自动触发对应的table1更新操作，不需要在PHP代码中写任何额外的更新逻辑。

内容的提问来源于stack exchange，提问作者Azis Sofyan Prasetyo