批量插入dr_scan表遇重复值时出现MySQL子查询返回多行错误

我在dr_scan表中有scan_freq字段，需求是：向该表插入记录时，若要插入的barcode值已存在于表中，就将原有重复行的scan_freq设为0；若不存在，新插入行的scan_freq设为1。

我写了下面的代码，单条插入时符合预期，但批量插入包含重复值的记录时，出现subquery returns more than 1 row错误。

if ($savec == "confirm") 
{ 
    $checkIfExistsQuery = "SELECT COUNT(*) as count FROM dr_scan WHERE barcode = (SELECT barcode FROM temp_scan_save WHERE user_id='$user_id' )"; 
    $checkResult = mysqli_query($conn, $checkIfExistsQuery); 
    $rowCount = mysqli_fetch_assoc($checkResult)['count']; 
    if ($rowCount > 0) 
    {
         $updateSql = "UPDATE dr_scan SET scan_freq = 0 WHERE barcode = (SELECT barcode FROM temp_scan_save WHERE user_id='$user_id' )"; 
         $updateResult = mysqli_query($conn, $updateSql); 
     } 
        $insertSql = "INSERT INTO dr_scan (barcode, machine_type, line, date, user_id, device_id, center, mstatus, mstatus_changed, live_status, transfer_status, scan_freq) SELECT barcode, machine_type, line, date, user_id, device_id, center, mstatus, mstatus_changed, live_status, transfer_status, 1 as scan_frequency FROM temp_scan_save WHERE user_id='$user_id'"; 
        $insertResult = mysqli_query($conn, $insertSql); 
        if (isset($updateResult) && $updateResult && isset($insertResult) && $insertResult)
         { $response = array("response" => "success"); echo json_encode($response); 
        } else 
        { 
            $response = array("response" => "failure"); echo json_encode($response);
         } 
         $sql2 = "DELETE FROM temp_scan_save WHERE user_id='$user_id' "; $result2 = mysqli_query($conn, $sql2); } 
         else { }
mysqli_close($conn);

错误根源 #

批量插入时，temp_scan_save表会返回多个barcode值，但你用了只能匹配单个值的=运算符，导致子查询返回多行时触发报错。需要把=替换成支持多值匹配的IN。

另外原代码的判断逻辑有漏洞：如果不需要执行更新（无重复barcode），$updateResult变量未定义，会导致判断失败。

修改后的代码 #

if ($savec == "confirm") {
    // 用IN替代=，适配子查询返回多个barcode的情况
    $checkIfExistsQuery = "SELECT COUNT(*) as count FROM dr_scan WHERE barcode IN (SELECT barcode FROM temp_scan_save WHERE user_id='$user_id')";
    $checkResult = mysqli_query($conn, $checkIfExistsQuery);
    $rowCount = mysqli_fetch_assoc($checkResult)['count'];

    $updateResult = true; // 默认更新成功（无重复时无需执行更新）
    if ($rowCount > 0) {
        // 同样把=改成IN
        $updateSql = "UPDATE dr_scan SET scan_freq = 0 WHERE barcode IN (SELECT barcode FROM temp_scan_save WHERE user_id='$user_id')";
        $updateResult = mysqli_query($conn, $updateSql);
    }

    $insertSql = "INSERT INTO dr_scan (barcode, machine_type, line, date, user_id, device_id, center, mstatus, mstatus_changed, live_status, transfer_status, scan_freq) 
                  SELECT barcode, machine_type, line, date, user_id, device_id, center, mstatus, mstatus_changed, live_status, transfer_status, 1 as scan_freq 
                  FROM temp_scan_save WHERE user_id='$user_id'";
    $insertResult = mysqli_query($conn, $insertSql);

    // 优化判断逻辑：无重复时只校验插入结果；有重复时同时校验更新和插入结果
    if (($rowCount == 0 && $insertResult) || ($rowCount > 0 && $updateResult && $insertResult)) {
        $response = ["response" => "success"];
        echo json_encode($response);
    } else {
        $response = ["response" => "failure"];
        echo json_encode($response);
    }

    $sql2 = "DELETE FROM temp_scan_save WHERE user_id='$user_id'";
    $result2 = mysqli_query($conn, $sql2);
}

mysqli_close($conn);

额外优化建议 #

• 防SQL注入：直接拼接$user_id存在注入风险，建议改用预处理语句：

// 示例：预处理查询重复数量
$checkStmt = $conn->prepare("SELECT COUNT(*) as count FROM dr_scan WHERE barcode IN (SELECT barcode FROM temp_scan_save WHERE user_id=?)");
$checkStmt->bind_param("s", $user_id); // 若user_id是整数，把"s"改成"i"
$checkStmt->execute();
$checkResult = $checkStmt->get_result();
$rowCount = mysqli_fetch_assoc($checkResult)['count'];
$checkStmt->close();

• 保证操作原子性：如果需要更新、插入、删除三个操作要么全部成功要么全部回滚，可以用事务包裹：

$conn->begin_transaction();
try {
    // 执行更新、插入、删除逻辑
    $conn->commit();
    echo json_encode(["response" => "success"]);
} catch (Exception $e) {
    $conn->rollback();
    echo json_encode(["response" => "failure"]);
}

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