Proper way to prevent duplicate entries using MySQ

Question

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.

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:

• 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)

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

Your suggestions?

Answer 1

I would use REPLACE INTO instead of 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 stackoverflow

Answer 2

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

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');  
   }
}

Answer 3

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)

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.

Answer 4

why you can't use INSERT IGNORE construction?