如何检查值是否已存在以避免重复? 如果存在则不执行任何操作，否则将记录插入数据库 [英] How to check if a value already exists to avoid duplicates?If it exist then do nothing otherwise insert record into database

问题描述

在插入重复项之前，我正在尝试检查表中是否已存在 Id.

I'm trying to check if an Id already exists in table before I insert a duplicate.

所以我需要检查数据库表中是否存在 Id，如果存在则什么都不做，否则我想将记录插入表中.我怎样才能做到这一点?

So I need to check the table of database for the presence of the Id and if it exists then do nothing Otherwise I want to insert the record into the table. How can i do this?

除了这个还有别的办法吗:

Sql_query->Select id from table where key="something";

If(true) 
{
  do nothing;
}
else
{
  Insert record in database;
}

我想避免这种情况，因为它需要时间搜索整个表然后插入. 那么有什么可行的方法吗?

I want to avoid this because it requires time to search entire table and then insert. So is there any feasible way?

推荐答案

两种策略:

1. 让数据库完成这项任务.更改您的表格，以便您想要唯一的字段实际上是 唯一索引.当您想要插入数据并且它是重复的时，您将收到一个错误，可用于决定下一步做什么.或者您可以使用 ON DUPLICATE KEY 如果您想执行另一个表操作.
2. 查询该表以查明该 id 是否已经存在.如果没有，请生成您的插入.

1. Let the database do this task. Alter your table so the field you want to be unique is actually a unique index. When you want to insert the data, and it is a duplicate, you'll receive an error which can be used to decide what to do next. Or you might use an ON DUPLICATE KEY if you want to do another table operation instead, if needed.
2. Query the table to find out if there the id is already present. If not, generate your insert.

示例:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$result = $mysqli->query("SELECT id FROM yourtable");
if ($result->num_rows > 0) {
    /* do your insert */
}
$result->close();

我更喜欢让数据库来完成这项工作.

I prefer letting the database do the job.

更新:

根据要求，这里有两个关于如何向表中添加唯一索引的示例.SQL 如下所示:

On request here are two examples on how to add an unique index to your table. The SQL looks like this:

CREATE UNIQUE INDEX your index_name
ON your_table (your_id_field);

插入数据时，如果key已经存在，mysql会报错.要处理此错误，请执行以下操作:

When inserting data, mysql will throw an error if the key already exists. To work with this error, do something like this:

$SQL = 'INSERT INTO yourtable (yourfield1, yourfield2) 
        VALUES ( 1, "Hello")';    
$result = $mysqli->query($SQL);

if (!mysqli_query($link, $SQL)) {
    if(mysqli_errno($link) == 1062) {
         /* duplicate ... whatever you want in this case */
    }
}

当然，如果您不想在这种情况下做任何事情，则不需要所有这些错误处理.

You don't need all this error handling if you don't want to do anything in this situation of course.

这篇关于如何检查值是否已存在以避免重复? 如果存在则不执行任何操作，否则将记录插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！