MySQL - 忽略插入错误:重复条目 [英] MySQL - ignore insert error: duplicate entry

问题描述

我在 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.

有没有唯一的方法可以首先进行 SELECT 并查看条目是否在 INSERT 之前已经存在 - 并且仅在 SELECT 没有返回记录时才插入?我希望不会.

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.

我想以某种方式告诉 MySQL 在没有任何错误的情况下忽略这些插入.

I would like to somehow tell MySQL to ignore these inserts without any error.

谢谢

推荐答案

您可以使用 INSERT... IGNORE 语法，如果您想在有重复记录时不采取任何行动.

You can use INSERT... IGNORE syntax if you want to take no action when there's a duplicate record.

如果你想，你可以使用 REPLACE INTO 语法用具有相同密钥的新记录覆盖旧记录.

You can use REPLACE INTO syntax if you want to overwrite an old record with a new one with the same key.

或者，您可以使用 INSERT... ONDUPLICATE KEY UPDATE 语法，如果您想在遇到重复记录时执行更新.

Or, you can use INSERT... ON DUPLICATE KEY UPDATE syntax if you want to perform an update to the record instead when you encounter a duplicate.

我想我会添加一些例子.

Thought I'd add some examples.

假设您有一个名为 tbl 的表，其中有两列，id 和 value.有一个条目，id=1 和 value=1.如果您运行以下语句:

Say you have a table named tbl with two columns, id and value. There is one entry, id=1 and value=1. If you run the following statements:

REPLACE INTO tbl VALUES(1,50);

您还有一条记录，id=1 value=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:

INSERT IGNORE INTO tbl VALUES (1,10);

操作成功执行，但没有插入任何内容.您仍然有 id=1 和 value=50.最后:

The operation executes successfully, but nothing is inserted. You still have id=1 and value=50. Finally:

INSERT INTO tbl VALUES (1,200) ON DUPLICATE KEY UPDATE value=200;

您现在有一个 id=1 和 value=200 的记录.

You now have a single record with id=1 and value=200.

这篇关于MySQL - 忽略插入错误:重复条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！