php 避免重复进入mysql数据库的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2219786/
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
Best way to avoid duplicate entry into mysql database
提问by Michelle
I have a table with 3 columns - id (pk), pageId (fk), name. I have a php script which dumps about 5000 records into the table, with about half being duplicates, with same pageId and name. Combination of pageId and name should be unique. What is the best way to prevent duplicates being saved to the table as I loop through the script in php?
我有一个包含 3 列的表 - id (pk)、pageId (fk)、name。我有一个 php 脚本，它将大约 5000 条记录转储到表中，其中大约一半是重复的，具有相同的 pageId 和名称。pageId 和 name 的组合应该是唯一的。当我在 php 中循环脚本时，防止重复项保存到表中的最佳方法是什么？
回答by nickf
First step would be to set a unique key on the table:
第一步是在表上设置一个唯一键：
ALTER TABLE thetable ADD UNIQUE INDEX(pageid, name);
Then you have to decide what you want to do when there's a duplicate. Should you:
然后你必须决定当有重复时你想做什么。你应该：
ignore it?
INSERT IGNORE INTO thetable (pageid, name) VALUES (1, "foo"), (1, "foo");Overwrite the previously entered record?
INSERT INTO thetable (pageid, name, somefield) VALUES (1, "foo", "first") ON DUPLICATE KEY UPDATE (somefield = 'first') INSERT INTO thetable (pageid, name, somefield) VALUES (1, "foo", "second") ON DUPLICATE KEY UPDATE (somefield = 'second')Update some counter?
INSERT INTO thetable (pageid, name) VALUES (1, "foo"), (1, "foo") ON DUPLICATE KEY UPDATE (pagecount = pagecount + 1)
忽略它？
INSERT IGNORE INTO thetable (pageid, name) VALUES (1, "foo"), (1, "foo");覆盖之前输入的记录？
INSERT INTO thetable (pageid, name, somefield) VALUES (1, "foo", "first") ON DUPLICATE KEY UPDATE (somefield = 'first') INSERT INTO thetable (pageid, name, somefield) VALUES (1, "foo", "second") ON DUPLICATE KEY UPDATE (somefield = 'second')更新一些计数器？
INSERT INTO thetable (pageid, name) VALUES (1, "foo"), (1, "foo") ON DUPLICATE KEY UPDATE (pagecount = pagecount + 1)
回答by mardala
You can also ignore the error with mysql: INSERT IGNORE INTO TABLE ... it will ignore the key error, skip over that insert and move on to the next.
您也可以使用 mysql 忽略错误：INSERT IGNORE INTO TABLE ...它将忽略关键错误，跳过该插入并继续下一个。
回答by Ryan
From a mysql point you can do
从 mysql 点你可以做
alter table YOURTABLE add unique index(pageId, name);
If your wording is correct and you want to do it from php you can do
如果您的措辞是正确的，并且您想从 php 中做到这一点，您可以这样做
$already_done = array();
foreach ($records as $record)
{
$unique_hash = md5($record['name'].$record['pageId']);
if (!in_array($unique_hash, $already_done))
{
$already_done[] = $unique_hash;
// sql insert here
}
}
either way those should do you just fine.
不管怎样，那些应该对你很好。
回答by Tyler Carter
You can set the PageID and Name to a Unique index in the MySQL database. This way when you insert the rows, it will cause an error, which can be ignored by PHP, and you can just go to the next row.
您可以将 PageID 和 Name 设置为 MySQL 数据库中的唯一索引。这样当你插入行时，它会导致一个错误，PHP可以忽略它，你可以直接转到下一行。
This assumes you are inserting rows individually. AKA:
这假设您单独插入行。又名：
foreach($large_data as $fields)
{
mysql_query("INSERT INTO TABLE (`Something`) VALUES('".$fields['something']."');
}
