使用MySQL或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.

我有一个歌曲表，它有songCategory和songName列。将songCategory和songName组合设置为unique在数据库级别。因此，即使php试图插入重复的条目数据库也不允许。

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.

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

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

我对此没有什么想法，但我不确定这些想法能否奏效:

We can insert records anyway, if there will be database error, we simply continue foreach loop.
我们可以插入记录，如果出现数据库错误，我们只需继续foreach循环。
We can trigger some function in database to check and delete duplicate rows after each insert (I have no experience with custom mysql functions)
我们可以在数据库中触发一些函数，在每次插入后检查和删除重复的行(我没有使用自定义mysql函数的经验)

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

顺便说一下，我正在使用Yii框架和MySQL数据库。

Your suggestions?

你的建议吗?

4 个解决方案

#1

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.

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

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

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/catch。我不知道php语法，但是由于许多其他语言都能做这种事情，我希望php也能做到。

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.

#2

I would use REPLACE INTO instead of INSERT INTO

我将使用REPLACE INTO而不是INSERT INTO

This way, if it's already there, it will just get overwritten.

这样，如果它已经存在，它就会被覆盖。

Disclaimer: Use this with caution. You do not want one person's song to write over another person's song.

免责声明:谨慎使用。你不需要一个人的歌来写别人的歌。

Link to REPLACE INTO on *

链接到*上进行替换

#3

why you can't use INSERT IGNORE construction?

为什么不能使用插入忽略结构?

#4

You can handle this in your Yii model, by creating a custom validation rule.

通过创建自定义验证规则，您可以在您的Yii模型中处理这个问题。

public function rules()
{
    return array(
       array('song_name', 'checkUniqueSongInCat','on'=>'insert'),  //check only on insert 
    );
}

public function checkUniqueSongInCat($attribute, $params)
{
   if (my_check_for_unique_song() === false)
   {
      $this->addError('song_name','Song is not unique');  
   }
}

#1