MySQL better approach to check duplicate entry

Question

For My user table in MySQL, before entering new userid & avoid duplicate userid, I check if similar Id exist. To me there are 3 approach :

Query for new userid (SELECT ...) and check 0 row returned. If row exists then request for new userid, else (INSERT...)
Make userid column UNIQUE in user table and directly INSERT... if 0 rows affected then request new userid
Make userid column UNIQUE in user table and directly INSERT... if mysqli_errno ($link)==1062 then request new userid

I presently use 1st method but it results in 2 query and intend to switch to second method. I found 3rd approach in a book which confused me !

My question is

Is 2nd approach better for duplicate entry prevention ?
Are 2nd & 3rd aprroach same or different ?
Is there still any better approach to prevent duplicate entry with minimum DB Query ?

I just want to make sure that you are aware of AUTO_INCREMENT which will automatically assign an ID to a row that you insert that is unique. - http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html — David, Apr 27 '14 at 06:07
Thanks but I know about `AI`, just improving on Mysql and correcting ages old code I wrote as novice. Thanks again — jahajee.com, Apr 27 '14 at 06:44

score 1 · Accepted Answer · answered Apr 27 '14 at 05:37

Here are my answers:

Is 2nd approach better for duplicate entry prevention?

yes, in my opinion second approach is better, if you know that userid is going to be always unique, you should make it a unique field, MySQL will automatically handle it.
Are 2nd & 3rd approach same or different?

I think both are same and MySQL throws error in these cases.
Is there still any better approach to prevent duplicate entry with minimum DB Query?

You can put UNIQUE INDEX on this column, that will be faster.

score 1 · Answer 2 · answered Apr 27 '14 at 05:38

1) Duplication should be handled by your database by make unique fields.

2) When you try to insert the same data in a unique field, it'll give you an error. The error would be like -

ERROR 1062 (23000): Duplicate entry '2147483647' for key 'UNIQUE'

3) Hence to avoid such error, you have to check through your code that is the data already available in the table.

CONCLUSION:

You have to use 1 & 2 approaches parallely to be 100% ensure that, no duplicate entries would exist in your table.

2 Answers2