While coding an application with MySQL as database, you might come into this situation:
You want to insert a new record into a table, if and only if there is no existing record in the table with certain matching fields.
- The matching field(s) need not has a unique constrain in your table structure design.
- Your insert statement does not include value definition for the table's autoincremented primary key (the "id"). As usual, you just let the database engine to determine the value of the autoincremented field for you upon successful insert action.
- You only want to check for duplication in certain field(s). The rest of the fields can be the same or different. Meanings, as long as all those fields you want to check are completely matching, it is considered a duplicated record, regardless the rest of the fields are matching or not.
- In the event you have found a duplication, you just want to do nothing and skip the insert. This is different from another situation whereby you want to update the existing record with current one.
- You don't want to mess up with the primary key (the "id") sequence. Meanings, if there is a match, you just abort the insert action without touching the table. The "id" sequence can continue with next insert, without jumping autoincrement number.