使用MySQL或PHP防止重复条目的正确方法 [英] Proper way to prevent duplicate entries using MySQL or PHP

问题描述

我有 song 表，其中有 songCategory 和 songName 列。 songCategory 和 songName 的组合在数据库级别设置为唯一。因此，基本上即使php尝试插入重复的条目数据库也不允许。

I have song table which has songCategory and songName columns. The combination of songCategory and songName is set to unique in database level. So basically even if php tries to insert duplicate entries database will not allow.

问题是，项目具有用户可以一次插入一堆歌曲的功能。如您所知，对大数据的重复记录检查需要很长时间：对于每首歌曲，我必须查询是否有任何歌曲的 categoryID = x 和 songName = y 。因此，我需要您的建议来解决此问题，而无需进行额外的重复检查。

The problem is, project has functionality where user can insert bunch of songs at once. And as you know, duplicate record check for big data takes very long: For each song I must query if there is any song with categoryID=x and songName=y. So I need your suggestions to solve this problem without extra duplicate check.

对此我没有什么想法，但是我不确定它们是否会起作用：

I have few ideas about this but I'm not quite sure if they will work:

• 我们仍然可以插入记录，如果发生数据库错误，我们只需 continue foreach循环


• 我们可以在每次插入后触发数据库中的某些功能来检查和删除重复的行（我对自定义mysql函数没有经验）

• We can insert records anyway, if there will be database error, we simply continue foreach loop.
• We can trigger some function in database to check and delete duplicate rows after each insert (I have no experience with custom mysql functions)

顺便说一句，我正在将Yii Framework与MySQL数据库一起使用。

Btw, I'm using Yii Framework with MySQL database.

您的建议？

推荐答案

您可以使用临时表。让php应用将所有内容插入temp表中，然后调用具有这种逻辑的查询。

You could use a temporary table. Have the php app insert everything into the temp table and then call a query that with this sort of logic.

insert into mainTable 
(field1, field2, etc)
select field1, field2, etc
from tempTable 
where (subquery to check for existing records goes here)

或者您可以使用try / catch。我不知道php语法，但是由于许多其他语言都可以做这种事情，所以我希望php也能够做到。

Or you could use try/catch. I don't know php syntax but since many other languages can do this sort of thing, I would expect php to be able to as well.

try
code to insert record
catch
if the error code is the one for duplicate records, do nothing.
if it's something else, handle it accordingly.

这篇关于使用MySQL或PHP防止重复条目的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！