检查MySQL是否已存在数据；如果不存在，则检查INSERT. [PHP + jQuery Ajax] [英] Check MySQL if data already exists, if not, INSERT. [PHP+jQuery Ajax]

问题描述

我无法创建可将值插入MySQL数据库的php代码，但前提是它们不存在.

I'm having trouble creating php code that would insert values into MySQL database but only if they don't already exist.

我使用$ .ajax类型POST将数组从javascript发送到PHP文件.

I send array from javascript to PHP file using $.ajax type POST.

我是否需要其他"SELECT"查询来检查值是否已经存在?

PHP文件(可以插入值):

<?php
SESSION_START();
include('config.php');
if(isset($_POST['predictedMatches'])&&$_SESSION['userid']){
    $predictedMatches=$_POST['predictedMatches'];
    $userid=$_SESSION['userid'];
}else die("ERROR");
$sql="";
foreach($predictedMatches as $predictedMatch){
    $sql.="INSERT INTO predictions(result,userFK,matchFK,tournamentFK) VALUES('".$predictedMatch['result']."','".$userid."','".$predictedMatch['id']."','".$predictedMatch['tourid']."');";
}
    if($conn->multi_query($sql) === TRUE){
        echo "OK";
    }else{
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
$conn->close();
?>

推荐答案

使用ON DUPLICATE KEY UPDATE功能.如果主键存在，则不会插入.但是您必须更新一些值，因此请使用没有索引或索引最少的列()(可能会导致这种情况).您的主键必须由以下三个FK堆肥而成:

Use the ON DUPLICATE KEY UPDATE feature. It won't insert, if the primary key exists. But you have to update some value, so use the column which is in no index or in the least indexes () in your case probably result). Your primary key has to be composted out of the three FKs:

ALTER TABLE `predictions` ADD PRIMARY KEY( `userFK`, `matchFK`, `tournamentFK`);

PHP代码，只是SQL语句(我是Java专家，所以我尽力了)

PHP-Code, just the SQL statment (I'm a Java Guy, so i tried my best)

$sql.="INSERT INTO predictions (result, userFK, matchFK, tournamentFK) "
."VALUES('".$predictedMatch['result'] ."','".$userid."','"
.$predictedMatch['id']."','".$predictedMatch['tourid']."') "
."ON DUPLICATE KEY UPDATE result = result ;";

要知道是否插入了查询，您必须查看受影响的行数:

To know if the query was inserted you have to look at the affected row count:

• 1行-插入
• 2行-更新

查询后看看$conn->affected_rows.

INSERT ... ON DUPLICATE KEY UPDATE绝对比SELECT和INSERT快，但比仅所需数据集的INSERT慢.即使是相同的值，更新也会在数据库中完成.不幸的是，没有 ON DUPLICATE KEY UPDATE INGNORE .如果插入很多，将导致更新，这比使用高速缓存，在数组中查找值并在插入之前将其与数组进行比较要好.仅将ON DUPLICATE KEY UPDATE用作后备广告.

INSERT ... ON DUPLICATE KEY UPDATE is definitively faster than a SELECT and INSERT but it's slower than an INSERT of just the needed datasets. The update is done in the database, even if it is the same value. Unfortunately there is no ON DUPLICATE KEY UPDATE INGNORE. If you have a lot of inserts, that will result in updates, than it may be better to use a cache, lookup values in an array and compare with the array before inserting. Only use the ON DUPLICATE KEY UPDATE as fallback.

这篇关于检查MySQL是否已存在数据；如果不存在，则检查INSERT. [PHP + jQuery Ajax]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！