Page 1 of 1

Most efficient way of checking for duplicate entries

Posted: Mon Feb 22, 2010 12:17 pm
by JJJJJJ
Hi there,

I have some queries where I am adding telephone numbers and some related info into a MySQL table.

I have made the telephone_num column the primary key because I do not want duplicate information relating to a telephone number.

I was trying to work out the best way to add telephone numbers to the table. I thought that as the telephone_num column was the primary key, then if just tried to add a record with the same telephone number then it simply wouldn't add it. This is fine as far as I'm concerned, I just wouldn't handle the error message.

The issue I'm having is that when I try to add more than one telephone numbers that have even one duplicate number, then the whole query fails and none of the numbers get added.

I would like to have a query that ignores duplicate records, but adds non-duplicate records.

The only way I can think of is to do something like:

Code: Select all

SELECT telephone_num FROM my_table;
and look for duplicates using php before I try to insert anything into the MySQL database.

I'm happy to do this, but if the database got to be large then I'm thinking it might just add an unnecessary step which would make the program slower.

So my question is, is there a way of inserting multiple rows into a table that will ignore any duplicate records while adding the unique ones?

Many thanks in advance

Joe

Re: Most efficient way of checking for duplicate entries

Posted: Mon Feb 22, 2010 1:11 pm
by John Cartwright
JJJJJJ wrote:I have made the telephone_num column the primary key because I do not want duplicate information relating to a telephone number.


Your better off having an internal id (auto incremented) and creating another field telephone_num as a unique key.
JJJJJJ wrote:So my question is, is there a way of inserting multiple rows into a table that will ignore any duplicate records while adding the unique ones?
You can use the ON DUPLICATE KEY clause, i.e.,

Code: Select all

INSERT INTO phonenumbers SET telephone_num = 123456 ON DUPLICATE KEY UPDATE 1=1
Which will basically ignore the INSERT argument if the unique key already exists.

Re: Most efficient way of checking for duplicate entries

Posted: Mon Feb 22, 2010 5:33 pm
by JJJJJJ
Thanks John.

Just what I wanted and it's working great now. 8)