MySQL Insert: Test first?

Question

As an example, when inserting a record into a table with a unique index, is it best to test first? e.g.,

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

then make sure 0 rows are returned, then do the insert?

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

Or is it better to just skip the test and handle the error in the event there's a duplicate entry?

THANKS!

Answer 1

It's better to insert and handle any duplicate key errors.

The reason is that if you test first, some other client can still insert the value in the brief moment between your test and your insert. So you'd need to handle errors anyway.

Answer 2

It depends on if you want to ensure that the values you are inserting don't exist or not. If you have a unique key on the file then it is going to be important that you do not create a duplicate key (which will throw an error). A lot of times too you want to test to see if a record exists, if so returning the primary key of the record so you can update the record and if not then inserting the record.

But if you have no unique keys and don't care if information is duplicated across a field or combination of fields then it isn't necessary and can save a little time. It just depends on the situation.

HTH

Answer 3

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

Just make sure there's a unique index on email in tblUser and do

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

Answer 4

Often depends on what rules about data duplication apply.

In your example, does your app permit more than one user to have the same email address? If not then you'd need to perform that check.

Answer 5

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

For example I just finished a job where a user needed a unique username and a unique email address.

Answer 6

Broadly speaking, there are three ways to handle this situation with a single query (fewer queries is usually a good thing to shoot for), but none of them is a universal "best way". Which you should use depends on your needs.

The first is, as you mention, running the INSERT … blindly and handling any errors PHP. This is the best approach when a duplicate key indicates a procedural problem (a bug in the software, a user trying to register a name that's already been used, etc.), as it allows you to perform additional operations before committing to a database update.

Second, there is the INSERT IGNORE … syntax. I would tend to call this the least commonly-useful approach, as it discards your INSERT completely if the key already exists. Primarily useful when a row (or rows) may or may not have been added to the table previously, but the data is known not to have changed.

Lastly, you can use an INSERT … ON DUPLICATE KEY UPDATE … statement. These can get rather verbose, but are very handy, as they allow you to insert data into your table without worrying about whether older data exists. If so, the existing row is updated. If not, a new one is inserted. Either way, your table will have the latest data available.