如何修改PHP代码替换SQL现有条目避免重复创建？

我负责修复某网站的PHP代码，目前遇到核心问题：员工入职时会被录入系统并分配SQL数据库中的rosterID；当员工离职后重新入职时，需分配新的rosterID，但当前代码会创建新条目，导致同一人存在新旧两个rosterID的重复记录。

需求：当SQL Server中存在该员工的旧条目时，新条目替换旧条目，确保同一员工仅保留一条最新记录（可通过替换旧值或删除旧条目实现）。

现有问题代码片段 #

// 如果存在重复条目，抛出提交错误，不添加新条目到SQL表
if(sqlsrv_num_rows($stmtGet) > 0) {
    // 如果勾选了重新认证框，添加新记录为'current'并将旧记录更新为'old'
    if (isset($_GET['recertify'])) {
        if ($Station != "" and $Trainee_Name != "" and $Trainer_Name != "" and $date != "") {
            $updatesql = "UPDATE $certifications SET $certifications.[Recertification] = '$updateCert' 
                WHERE $certifications.[Roster ID] = '$rosterID' AND $certifications.[Station] = '$Station' AND $certifications.[Recertification] = '$Recert'";
            $sql = "INSERT INTO $certifications ([Roster ID], Station, Trainer_Name, Date, Recertification) VALUES
                ('$rosterID', '$Station', '$Trainer_Name', '$date', '$Recert')";
 
            if(sqlsrv_query($conn, $updatesql) AND sqlsrv_query($conn, $sql)){
                ?><p> <?php echo $Trainee_Name;?> has successfully been recertified at <?php echo $Station;?>.</p><?php
            } else{
                if( ($errors = sqlsrv_errors() ) != null) {
                    foreach( $errors as $error ) {
                        echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
                        echo "code: ".$error[ 'code']."<br />";
                        echo "message: ".$error[ 'message']."<br />";
                    }
                }
            }
        } else {
            echo "Submission Failed. Please try again.";
        } 
    }
}

解决方案 #

方案一：使用SQL Server MERGE语句（原子操作，推荐） #

MERGE语句可在单次原子操作中完成「匹配旧记录则更新，无匹配则插入」，避免并发场景下的异常，同时彻底解决重复记录问题。

修改后代码（同时修复SQL注入风险，采用参数化查询）：

if(sqlsrv_num_rows($stmtGet) > 0) {
    if (isset($_GET['recertify'])) {
        if ($Station != "" and $Trainee_Name != "" and $Trainer_Name != "" and $date != "") {
            // MERGE语句：匹配则更新，不匹配则插入
            $mergeSql = "MERGE INTO $certifications AS target
                USING (SELECT ? AS [Roster ID], ? AS Station, ? AS Trainer_Name, ? AS Date, ? AS Recertification) AS source
                ON target.[Roster ID] = source.[Roster ID] AND target.Station = source.Station
                WHEN MATCHED THEN 
                    UPDATE SET Trainer_Name = source.Trainer_Name, Date = source.Date, Recertification = source.Recertification
                WHEN NOT MATCHED THEN
                    INSERT ([Roster ID], Station, Trainer_Name, Date, Recertification)
                    VALUES (source.[Roster ID], source.Station, source.Trainer_Name, source.Date, source.Recertification);";
            
            // 参数化查询，防止SQL注入
            $params = array(
                $rosterID,
                $Station,
                $Trainer_Name,
                $date,
                $Recert
            );
            
            $stmt = sqlsrv_prepare($conn, $mergeSql, $params);
            if(sqlsrv_execute($stmt)){
                ?><p><?php echo $Trainee_Name;?> 已在<?php echo $Station;?>成功完成重新认证。</p><?php
            } else{
                if( ($errors = sqlsrv_errors() ) != null) {
                    foreach( $errors as $error ) {
                        echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
                        echo "错误码: ".$error[ 'code']."<br />";
                        echo "错误信息: ".$error[ 'message']."<br />";
                    }
                }
            }
        } else {
            echo "提交失败，请重试。";
        } 
    }
}

说明：

• 通过ON条件匹配旧记录（示例中匹配RosterID和Station，可根据业务调整匹配规则）
• 匹配到旧记录时直接覆盖为新值，未匹配则插入新记录
• 用sqlsrv_prepare+sqlsrv_execute替代字符串拼接，彻底规避SQL注入风险

方案二：先删除旧记录再插入新记录 #

如果业务逻辑简单，可直接删除对应旧记录后插入新条目，确保仅保留最新一条记录。

修改后代码（同样采用参数化查询）：

if(sqlsrv_num_rows($stmtGet) > 0) {
    if (isset($_GET['recertify'])) {
        if ($Station != "" and $Trainee_Name != "" and $Trainer_Name != "" and $date != "") {
            // 1. 删除旧记录
            $deleteSql = "DELETE FROM $certifications WHERE [Roster ID] = ? AND Station = ?";
            $deleteParams = array($rosterID, $Station);
            $deleteStmt = sqlsrv_prepare($conn, $deleteSql, $deleteParams);
            
            // 2. 插入新记录
            $insertSql = "INSERT INTO $certifications ([Roster ID], Station, Trainer_Name, Date, Recertification) VALUES (?, ?, ?, ?, ?)";
            $insertParams = array($rosterID, $Station, $Trainer_Name, $date, $Recert);
            $insertStmt = sqlsrv_prepare($conn, $insertSql, $insertParams);
            
            // 执行两个操作
            if(sqlsrv_execute($deleteStmt) && sqlsrv_execute($insertStmt)){
                ?><p><?php echo $Trainee_Name;?> 已在<?php echo $Station;?>成功完成重新认证。</p><?php
            } else{
                if( ($errors = sqlsrv_errors() ) != null) {
                    foreach( $errors as $error ) {
                        echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
                        echo "错误码: ".$error[ 'code']."<br />";
                        echo "错误信息: ".$error[ 'message']."<br />";
                    }
                }
            }
        } else {
            echo "提交失败，请重试。";
        } 
    }
}

说明：

• 先删除匹配RosterID和Station的旧记录，再插入新条目
• 同样使用参数化查询防止SQL注入
• 注意：并发场景下可能出现短暂的记录缺失，MERGE方案更安全

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