php MySQL - 忽略插入错误：重复条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/812437/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - ignore insert error: duplicate entry
提问by neon
I am working in PHP.
我在 PHP 工作。
Please what's the proper way of inserting new records into the DB, which has unique field. I am inserting lot of records in a batch and I just want the new ones to be inserted and I don't want any error for the duplicate entry.
请问将新记录插入具有唯一字段的数据库的正确方法是什么。我正在批量插入大量记录，我只想插入新记录，我不希望重复条目出现任何错误。
Is there only way to first make a SELECT and to see if the entry is already there before the INSERT - and to INSERT only when SELECT returns no records? I hope not.
有没有办法先做一个 SELECT 并查看条目是否在 INSERT 之前已经存在 - 并且只有在 SELECT 没有返回记录时才插入？我希望不是。
I would like to somehow tell MySQL to ignore these inserts without any error.
我想以某种方式告诉 MySQL 忽略这些插入而没有任何错误。
Thank you
谢谢
回答by zombat
You can use INSERT... IGNOREsyntax if you want to take no action when there's a duplicate record.
如果您不想在有重复记录时采取任何行动，您可以使用INSERT... IGNORE语法。
You can use REPLACE INTOsyntax if you want to overwrite an old record with a new one with the same key.
如果您想用具有相同密钥的新记录覆盖旧记录，则可以使用REPLACE INTO语法。
Or, you can use INSERT... ON DUPLICATE KEY UPDATEsyntax if you want to perform an update to the record instead when you encounter a duplicate.
或者，如果您想在遇到重复记录时对记录执行更新，则可以使用INSERT... ON DUPLICATE KEY UPDATE语法。
Edit: Thought I'd add some examples.
编辑：以为我会添加一些示例。
Examples
例子
Say you have a table named tblwith two columns, idand value. There is one entry, id=1 and value=1. If you run the following statements:
假设您有一个以tbl两列命名的表，id并且value. 有一个条目，id=1 和 value=1。如果您运行以下语句：
REPLACE INTO tbl VALUES(1,50);
You still have one record, with id=1 value=50. Note that the whole record was DELETED first however, and then re-inserted. Then:
你还有一条记录，id=1 value=50。请注意，整个记录首先被删除，然后重新插入。然后：
INSERT IGNORE INTO tbl VALUES (1,10);
The operation executes successfully, but nothing is inserted. You still have id=1 and value=50. Finally:
操作成功执行，但没有插入任何内容。您仍然有 id=1 和 value=50。最后：
INSERT INTO tbl VALUES (1,200) ON DUPLICATE KEY UPDATE value=200;
You now have a single record with id=1 and value=200.
您现在有一个 id=1 和 value=200 的记录。
回答by gehsekky
You can make sure that you do not insert duplicate information by using the EXISTS condition.
您可以使用 EXISTS 条件确保不插入重复信息。
For example, if you had a table named clients with a primary key of client_id, you could use the following statement:
例如，如果您有一个名为 clients 的表，其主键为 client_id，则可以使用以下语句：
INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);
This statement inserts multiple records with a subselect.
此语句使用子选择插入多条记录。
If you wanted to insert a single record, you could use the following statement:
如果要插入单个记录，可以使用以下语句：
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);
The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.
双表的使用允许您在选择语句中输入您的值，即使这些值当前未存储在表中。
回答by Kirtan
回答by Jordan S. Jones
Try creating a duplicate table, preferably a temporary table, without the unique constraint and do your bulk load into that table. Then select only the unique (DISTINCT) items from the temporary table and insert into the target table.
尝试创建一个没有唯一约束的重复表，最好是一个临时表，然后批量加载到该表中。然后仅从临时表中选择唯一 (DISTINCT) 项并插入到目标表中。
回答by chandrasekar
$duplicate_query=mysql_query("SELECT * FROM student") or die(mysql_error());
$duplicate=mysql_num_rows($duplicate_query);
if($duplicate==0)
{
while($value=mysql_fetch_array($duplicate_query)
{
if(($value['name']==$name)&& ($value['email']==$email)&& ($value['mobile']==$mobile)&& ($value['resume']==$resume))
{
echo $query="INSERT INTO student(name,email,mobile,resume)VALUES('$name','$email','$mobile','$resume')";
$res=mysql_query($query);
if($query)
{
echo "Success";
}
else
{
echo "Error";
}
else
{
echo "Duplicate Entry";
}
}
}
}
else
{
echo "Records Already Exixts";
}
