如何防止重复插入表 [英] How to prevent duplicate inserts into a table

问题描述

我想将数据插入到Mysql中，并且当数据已经在表中时，就不应再将其插入.如何防止第二次插入?

I want to insert data into Mysql, and when the data is already in the table, it should not be inserted again. How to prevent the second insert?

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "INSERT INTO MyGuests(firstname, lastname, email)
    VALUES ('John', 'Doe', '[e-mail]')";

    // use exec() because no results are returned
    $conn->exec($sql);
    echo "Table MyGuests created successfully";
} catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;

结果是这样的:

推荐答案

您在电子邮件地址上没有唯一的密钥.这是您最大的希望，因为系统中可能有许多John Doe.因此，对全名进行唯一的复合索引绝不是一个好主意.

You do not have a unique key on the email address. That is your best hope, as many John Doe's can be in the system. So a unique composite index on full name is not ever a good idea.

一个人可以发出INSERT IGNORE，在发生重复违规后便沉没，好像什么也没发生(这是极不推荐的做法，在您的情况下是不可能的，因为您对电子邮件的唯一约束不存在).这样做的主要问题是您的趋势(阅读:可能会)失去对正在发生的一切流的控制，并留下了不希望的数据.因此，除非您是专业人士，否则请勿使用它.

One can issue an INSERT IGNORE which plods along after a duplicate violation as if nothing happens (this is highly unrecommended, and in your case not possible because your unique constraint on email is not present). The major problem with doing this is that your tend (read: probably will) lose all control of flow of what is happening and be left with undesired data. So don't use it, unless you are a pro.

可以发布并在重复的密钥更新上插入(也称为IODKU)，它也需要唯一的密钥(也可以是唯一的组合).这样，将不会出现新行，而是对该行进行更新.例如，可以插入一个包含4列的新行，但是如果违反复制规定，则仅对其中两列进行更新，例如

One can issue and INSERT ON DUPLICATE KEY UPDATE (a.k.a. IODKU) that also requires a unique key (can be a unique composite too). As such, a new row would not occur but an update to the row. As an example, a new row with 4 columns could be inserted, but on duplication violation, an update could be performed on just two of the columns, such as

假定在未见列id int上的PK下方，并在emailAddr

assuming in below a PK on an unseen column id int, and a unique key on emailAddr

insert person(firstName,lastName,emailAddr) values 'Joe','Smith','[e-mail]' 
on duplicate key update firstName='Joe',lastName='Smith'

这是emailAddr上的唯一密钥，可实现此目的.以前，每个emailAddr只剩下1行.

It is the unique key on emailAddr that makes this work. Left with only 1 row per emailAddr, ever.

因此，您可以有无限个Joe Smiths，但每个电子邮件地址只能有1行.

So, you can have unlimited Joe Smiths, but only 1 row per email address.

上表的创建表看起来像

create table person
(  id int auto_increment primary key,
   firstName varchar(50) not null,
   lastName varchar(50) not null,
   emailAddr varchar(150) not null,
   unique key(emailAddr)  -- without this, IODKU will not work
);

在创建表之后，您还可以发出ALTER TABLE命令来添加约束.根据其中的数据，调用可能会失败.

You can also issue ALTER TABLE commands to add constraints after the table is created. Depending on the data therein, the call may fail.

Mysql 在重复密钥更新中插入 ，以及更改表手册页.

Mysql Insert on Duplicate Key Update, and Alter Table manual pages.

我会再说一遍，如果没有正确的模式设置，IODKU将无法正常工作，并且您将得到不希望有的行.所以不要偷懒.设置架构首先使其成功(使用唯一的键)，然后继续使用IODKU.

I will say it again, without the proper schema setup, IODKU will not work and you will be left with undesired rows. So don't be lazy about it. Setup schema first allowing it to success (with unique keys), then move on toward using IODKU.

这篇关于如何防止重复插入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！