MySQL Paste: check first?

Question

As an example, when inserting a record into a table with a unique index, is it better to test first? eg,

$mysqli->query('SELECT email FROM tblUser WHERE email = ' [e-mail] '); 

then make sure 0 rows are returned, then insert?

 $mysqli->query('INSERT INTO tblUser ...'); 

Or is it better to just skip the test and handle the error in case of duplicate entries?

THANKS!

Answer 1

It is better to insert and handle any duplicate key errors.

The reason is that if you test first, another client will still be able to insert the value in the short amount of time between your test and your insert. Therefore, you still have to handle errors.

Answer 2

Generally speaking, there are three ways to deal with this situation with a single request (fewer requests are usually a good thing to shoot), but none of them is a universal “best way”. What you should use depends on your needs.

First, as you noticed, by running INSERT … blindly and handle any PHP errors. This is the best approach when a duplicate key indicates procedural problems (a software bug, a user trying to register a name that has already been used, etc.), because it allows you to perform additional operations before performing a database update.

Secondly, there is INSERT IGNORE … syntax. I would call this the least common approach, since it completely discards INSERT if the key already exists. It is primarily useful when a row (or rows) can be added or not added to the table earlier, but the data, as you know, has not changed.

Finally, you can use the INSERT … ON DUPLICATE KEY UPDATE … . They can be quite detailed, but very convenient, because they allow you to insert data into a table without worrying about whether older data exists. If so, the existing row is updated. If not, then a new one. In any case, your table will have the most recent data available.

Answer 3

MySQL supports ignore insertion if you want to ignore an insertion that creates a row with a key value that already exists for another row.

Just make sure there is a unique email index in tblUser and

 $mysqli->query('INSERT IGNORE INTO tblUser ...'); 

Answer 4

It depends on whether you want the values ​​you insert to not exist or not. If you have a unique key in the file, it is important that you do not duplicate the key (which will cause an error). Many times you also want to check if a record exists, if so returns the primary key of the record so that you can update the record, and if not, insert the record.

But if you do not have unique keys, and still, if the information is duplicated by field or combination of fields, then this is not necessary and can save a little time. It just depends on the situation.

NTN

Answer 5

It often depends on which data duplication rules apply.

In your example, can your application allow multiple users to have the same email address? If not, you need to perform this check.

Answer 6

You definitely want to check first, and you can test a few things so you can tell the user what went wrong.

For example, I just finished a job where the user needs a unique username and unique email address.