避免重复进入mysql数据库的最佳方法 [英] Best way to avoid duplicate entry into mysql database

问题描述

我有一个包含 3 列的表格 - id (pk)、pageId (fk)、name.我有一个 php 脚本，它将大约 5000 条记录转储到表中，其中大约一半是重复的，具有相同的 pageId 和名称.pageId 和 name 的组合应该是唯一的.当我在 php 中循环脚本时，防止重复项保存到表中的最佳方法是什么?

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?

推荐答案

第一步是在表上设置唯一键:

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:

1. 忽略它?

1. 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)

这篇关于避免重复进入mysql数据库的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！