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

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.