Proper way to prevent duplicate entries using MySQL or PHP

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?

insert anyway , thne you can check the inserted count, which will tell you if the song was a duplicate. mysql_affected_rows() or equivalent will tell you if a raw was actually inserted or was reject as it was a duplicate — , Aug 31 '14 at 02:22
Create a field called like `md5_name` with `UNIQUE` attribute and when you add the song: `md5_name` = `md5($song_cat . $song_name)` and just add the song and catch any `Exception` or check for the `last_inserted_id` — tttony, Aug 31 '14 at 02:25
if you're using native sql you can try `insert ignore` but I don't think yii's active record supports it — FuzzyTree, Aug 31 '14 at 02:36

score 2 · Accepted Answer · answered Aug 31 '14 at 02:41

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.

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

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

edited May 23 '17 at 11:49

Community

1
1

answered Aug 31 '14 at 03:11

Chemdream

618
2
9
25

score 0 · Answer 3 · answered Aug 31 '14 at 06:05

0

why you can't use INSERT IGNORE construction?

answered Aug 31 '14 at 06:05

Denis Rudov

833
8
16

score 0 · Answer 4 · answered Aug 31 '14 at 13:10

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

Proper way to prevent duplicate entries using MySQL or PHP

4 Answers4

Linked