如何将含IF语句的两条MySQL UPDATE查询合并为单条？

需求说明 #

1. 更新submissions表的指定字段（submission_title、submission_area、submission_link、submission_citations、submission_status、submission_updatedby）
2. 当新设置的submission_status为"cancelled"，且关联的requests表中request_status为"review2"时，将该requests记录的request_status更新为"submission"

现有尝试的问题 #

第一种写法（多语句查询） #

代码：

public function updateSubmission($data){
    $this->db->query('UPDATE submissions S, requests R
                        SET S.submission_title = :submission_title, 
                            S.submission_area = :submission_area, 
                            S.submission_link = :submission_link,
                            S.submission_citations = :submission_citations,
                            S.submission_status = :submission_status,
                            S.submission_updatedby = :submission_updatedby
                        WHERE S.submission_id = :submission_id;
                        UPDATE requests R 
                        SET R.request_status = IF(R.request_status = "review2" AND :submission_status = "cancelled", "submission" )
                        WHERE R.request_id = :submission_requestid;
                        ');           

    $this->db->bind(':submission_title', $data['submission_title']);
    $this->db->bind(':submission_area', $data['submission_area']);
    $this->db->bind(':submission_link', $data['submission_link']);
    $this->db->bind(':submission_citations', $data['submission_citations']);
    $this->db->bind(':submission_status', $data['submission_status']);
    $this->db->bind(':submission_updatedby', $_SESSION['user_id']); 
    $this->db->bind(':submission_id', $data['submission_id']);   
    $this->db->bind(':submission_requestid', $data['submission_requestid']);    

    if($this->db->execute()){                            
        return true;      
    } else {
        return false;
    }
}

问题原因：PDO默认禁用多语句查询（防止SQL注入），因此只有第一条UPDATE会执行，第二条被忽略。

第二种写法（多表联更） #

代码：

$this->db->query('UPDATE submissions S, requests R
                    SET S.submission_title = :submission_title, 
                        S.submission_area = :submission_area, 
                        S.submission_link = :submission_link,
                        S.submission_citations = :submission_citations,
                        S.submission_status = :submission_status,
                        S.submission_updatedby = :submission_updatedby
                        R.request_status = IF(R.request_status = "review2" AND :submission_status = "cancelled", "submission")
                    WHERE S.submission_id = :submission_id AND R.request_id = :submission_requestid
                    ');

问题原因：语法错误——S.submission_updatedby = :submission_updatedby后缺少逗号，导致SQL解析失败；另外如果submissions和requests没有强制关联关系，这种联更逻辑可能不符合预期。

解决方案 #

方案1：使用事务（推荐，保证原子性） #

事务可以确保两条查询要么全部成功，要么全部回滚，满足你“统一执行反馈、避免部分失败”的需求。

首先给你的Database类添加事务相关方法：

// 开启事务
public function beginTransaction(){
    return $this->dbh->beginTransaction();
}

// 提交事务
public function commit(){
    return $this->dbh->commit();
}

// 回滚事务
public function rollback(){
    return $this->dbh->rollback();
}

然后修改updateSubmission方法：

public function updateSubmission($data){
    try {
        $this->db->beginTransaction();

        // 第一条更新：submissions表
        $this->db->query('UPDATE submissions S
                            SET S.submission_title = :submission_title, 
                                S.submission_area = :submission_area, 
                                S.submission_link = :submission_link,
                                S.submission_citations = :submission_citations,
                                S.submission_status = :submission_status,
                                S.submission_updatedby = :submission_updatedby
                            WHERE S.submission_id = :submission_id');
        $this->db->bind(':submission_title', $data['submission_title']);
        $this->db->bind(':submission_area', $data['submission_area']);
        $this->db->bind(':submission_link', $data['submission_link']);
        $this->db->bind(':submission_citations', $data['submission_citations']);
        $this->db->bind(':submission_status', $data['submission_status']);
        $this->db->bind(':submission_updatedby', $_SESSION['user_id']); 
        $this->db->bind(':submission_id', $data['submission_id']);
        $this->db->execute();

        // 第二条更新：requests表（带条件）
        $this->db->query('UPDATE requests R 
                            SET R.request_status = "submission"
                            WHERE R.request_id = :submission_requestid
                            AND R.request_status = "review2"
                            AND :submission_status = "cancelled"');
        $this->db->bind(':submission_requestid', $data['submission_requestid']);
        $this->db->bind(':submission_status', $data['submission_status']);
        $this->db->execute();

        $this->db->commit();
        return true;
    } catch(PDOException $e) {
        $this->db->rollback();
        // 可根据需要记录错误日志
        return false;
    }
}

方案2：修正单条多表更新语句（适合两表强关联场景） #

如果submissions和requests存在明确关联（比如submissions.submission_requestid = requests.request_id），可以用单条联更语句：

public function updateSubmission($data){
    $this->db->query('UPDATE submissions S
                        JOIN requests R ON S.submission_requestid = R.request_id
                        SET S.submission_title = :submission_title, 
                            S.submission_area = :submission_area, 
                            S.submission_link = :submission_link,
                            S.submission_citations = :submission_citations,
                            S.submission_status = :submission_status,
                            S.submission_updatedby = :submission_updatedby,
                            R.request_status = IF(R.request_status = "review2" AND :submission_status = "cancelled", "submission", R.request_status)
                        WHERE S.submission_id = :submission_id');

    $this->db->bind(':submission_title', $data['submission_title']);
    $this->db->bind(':submission_area', $data['submission_area']);
    $this->db->bind(':submission_link', $data['submission_link']);
    $this->db->bind(':submission_citations', $data['submission_citations']);
    $this->db->bind(':submission_status', $data['submission_status']);
    $this->db->bind(':submission_updatedby', $_SESSION['user_id']); 
    $this->db->bind(':submission_id', $data['submission_id']);   

    return $this->db->execute();
}

说明：用IF函数保证只有满足条件时才更新request_status，否则保持原值；使用JOIN替代逗号分隔表，逻辑更清晰。

方案3：开启PDO多语句查询（不推荐，有安全风险） #

如果坚持用多语句，需要在PDO连接时开启MYSQL_ATTR_MULTI_STATEMENTS选项，但这会增加SQL注入风险，仅在完全信任输入的场景下使用：

// 修改Database类的__construct方法中的options数组
$options = array(
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_MULTI_STATEMENTS => true
);

之后第一种写法的多语句查询就能执行，但务必确保所有输入都经过严格过滤。

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