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

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."')");

    }