停止在我的数据库中插入重复输入

Question

我们使用下面的代码来避免在我们的数据库中插入重复的输入，但在某些情况下它不起作用

  $device_id = $_REQUEST['device_id'];

$fcm_id = $_REQUEST['fcm_id'];

$model_number = $_REQUEST['model_number'];

$os_version = $_REQUEST['os_version'];

$app_version = $_REQUEST['app_version'];

$created = round(microtime(true) * 1000);

if(isset($device_id)){

    $exist = mysqli_query($con, "SELECT * FROM `user` WHERE `device_id`='$device_id'");

    if(mysqli_num_rows($exist)>0){

        mysqli_query($con, "UPDATE `user` SET `fcm_id`='$fcm_id', `topics`=0 WHERE `device_id`='$device_id'");

    }

    else{

        mysqli_query($con, "INSERT INTO `user` (`device_id`, `fcm_id`, `model_number`, `os_version`, `created`) VALUES ('".$device_id."', '".$fcm_id."', '".$model_number."', '".$os_version."', '".$created."')");

    }

Answer 1

尝试这个  $device_id = $_REQUEST['device_id'];  $fcm_id = $_REQUEST['fcm_id'];  $model_number = $_REQUEST['model_number'];  $os_version = $_REQUEST['os_version'];  $app_version = $_REQUEST['app_version'];  $created = round(microtime(true) * 1000);  if(isset($device_id)){    $stmt = $con->prepare("INSERT IGNORE INTO `user` (`device_id`, `fcm_id`,       `model_number`, `os_version`, `created`) VALUES (?, ?, ?,?,?)");    $stmt->bind_param($device_id, $fcm_id, $model_number, $os_version, $created );    if ($stmt->execute()) {    }    $stmt->close();  }更新：添加到您的 tabekl 以下索引中，但是您应该考虑在索引中放置的内容。因为只有当所有 4 列都相同时，才会创建新条目。    CREATE UNIQUE INDEX idx_insert_user     ON user (`device_id`, `fcm_id`,       `model_number`, `os_version`);insert ignore into 不会在表中输入重复项

Answer 2

试试这个$device_id = $_REQUEST['device_id'];$fcm_id = $_REQUEST['fcm_id']; $model_number = $_REQUEST['model_number'];$os_version = $_REQUEST['os_version'];$app_version = $_REQUEST['app_version'];$created = round(microtime(true) * 1000);if(isset($device_id)){    $qry="UPDATE `user` SET `fcm_id`='$fcm_id', `topics`=0 WHERE EXISTS (SELECT * FROM `user` WHERE `device_id`='$device_id')";    if(!mysqli_query($con,$qry)){ mysqli_query($con, "INSERT INTO `user` (`device_id`, `fcm_id`, `model_number`, `os_version`, `created`) VALUES ('".$device_id."', '".$fcm_id."', '".$model_number."', '".$os_version."', '".$created."')");    }