Most efficient way of checking for duplicate entries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
JJJJJJ
Forum Newbie
Posts: 8
Joined: Wed Jan 27, 2010 5:56 pm

Most efficient way of checking for duplicate entries

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Most efficient way of checking for duplicate entries

Post 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.
JJJJJJ
Forum Newbie
Posts: 8
Joined: Wed Jan 27, 2010 5:56 pm

Re: Most efficient way of checking for duplicate entries

Post by JJJJJJ »

Thanks John.

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