INSERT IGNORE Inserting 0 rows

Question

Hi Everyone:

When I am attempting to run "INSERT IGNORE ..." in MYSQL to add only one variable to a table of several options, after the first insert, it refuses to work. It says "Inserted rows: 0" and doesn't insert my new value into the database. I believe this is because there is already an entry with a "nothing" value and MYSQL doesn't allow the empty field to be duplicated. This seems to be odd behavior (as long as the two inserts are not exactly the same), so I am wondering if there is some way to avoid this.

Thanks for any hep!

Answer 1

The two INSERT do not have to be exactly the same, they just have to be the same for the primary key columns.

INSERT IGNORE will ignore an insert if there is already a row with the same primary key. If you did INSERT instead of INSERT IGNORE, you would be getting an error (duplicate primary key).

If you want to instead update the existing row, you can use REPLACE.

Either way, there can be only one row for each primary key.