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不返回任何记录时才插入INSERT?我希望不会.
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.
谢谢
推荐答案
You can use INSERT... IGNORE syntax if you want to take no action when there's a duplicate record.
如果您愿意，可以使用替换成语法用相同的密钥用新记录覆盖旧记录.
You can use REPLACE INTO syntax if you want to overwrite an old record with a new one with the same key.
或者，您可以使用 INSERT ... ON如果您想对记录执行更新，而在遇到重复项时则使用DUPLICATE 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值= 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且值为200的记录.
You now have a single record with id=1 and value=200.
这篇关于MySQL-忽略插入错误:重复输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
