MySQL - ignore insert error: duplicate record

Question

I work in PHP.

Please, what is the way to insert new records into a database that has a unique field. I am inserting a lot of records in batch mode, and I just want the new ones to be inserted, and I don't want any error for a duplicate record.

Is there only a way to make SELECT and see if there is a record there before INSERT - and INSERT only if SELECT does not return records? I hope no.

I would like to somehow tell MySQL to ignore these inserts without any errors.

thank

Answer 1

You can use the INSERT ... IGNORE syntax if you do not want to take any action if there is a duplicate record.

You can use REPLACE INTO if you want to overwrite an old record with a new one with the same key.

Or you can use the INSERT ... ON DUPLICATE KEY UPDATE syntax if you want to update the record instead when you encounter a duplicate.

Edit: Thought I'd add a few examples.

Examples

Say you have a table called tbl with two columns, id and value . There is one entry, id = 1 and value = 1. If you follow these statements:

 REPLACE INTO tbl VALUES(1,50); 

You still have one record with id = 1 value = 50. Note that the entire record was first deleted and then inserted again. Then:

 INSERT IGNORE INTO tbl VALUES (1,10); 

The operation is successful, but nothing is inserted. You still have id = 1 and value = 50. Finally:

 INSERT INTO tbl VALUES (1,200) ON DUPLICATE KEY UPDATE value=200; 

Now you have one entry with id = 1 and value = 200.

Answer 2

What does the database schema look like?

You can add an identifier column that will automatically increment for each insert to guarantee unique rows.

Answer 3

You can make sure that you do not insert duplicate information using the EXISTS clause.

For example, if you have a table with the name of clients with the primary key client_id, you can use the following statement:

 INSERT INTO clients (client_id, client_name, client_type) SELECT supplier_id, supplier_name, 'advertising' FROM suppliers WHERE not exists (select * from clients where clients.client_id = suppliers.supplier_id); 

This statement inserts multiple records with nested selections.

If you want to insert a single entry, you can use the following statement:

 INSERT INTO clients (client_id, client_name, client_type) SELECT 10345, 'IBM', 'advertising' FROM dual WHERE not exists (select * from clients where clients.client_id = 10345); 

Using a double table allows you to enter your values ​​in the select statement, even if these values ​​are not currently stored in the table.

from http://www.techonthenet.com/sql/insert.php

Answer 4

You can use triggers .

Also check out this trigger implementation guide .

Answer 5

Try creating a duplicate table, preferably a temporary table, without a unique constraint, and bulk upload to that table. Then select only unique (DISTINCT) items from the temporary table and paste into the target table.

Answer 6

 $duplicate_query=mysql_query("SELECT * FROM student") or die(mysql_error()); $duplicate=mysql_num_rows($duplicate_query); if($duplicate==0) { while($value=mysql_fetch_array($duplicate_query) { if(($value['name']==$name)&& ($value['email']==$email)&& ($value['mobile']==$mobile)&& ($value['resume']==$resume)) { echo $query="INSERT INTO student(name,email,mobile,resume)VALUES('$name','$email','$mobile','$resume')"; $res=mysql_query($query); if($query) { echo "Success"; } else { echo "Error"; } else { echo "Duplicate Entry"; } } } } else { echo "Records Already Exixts"; } 

Answer 7

 INSERT INTO tbl (col1,col2) SELECT val1,val2 FROM tbl WHERE (SELECT COUNT(*) FROM tbl WHERE col1 = val1 AND col2 = val2) = 0 LIMIT 1,1