Inserting records in Mysql with INSERT IGNORE and NULL values

Question

I have a partitioned table InnoDB with several fields. I'm trying to avoid duplicates on insert.

Let's say:

Field1 int null
Field2 int null
Field3 int null
Field4 int null
Field5 int null

I have created a UNIQUE index on those fields. I try to insert some records with NULL values and then try to reinsert them with IGNORE feature on MySql.

Unfortunately it seems to replicated the records when using NULL values. If I try with zeros instead of NULL cases everything works, but I do need the nulls there.

Any ideas?

Answer 1

This is how NULL works. Keep in mind that the result of equality comparison of two NULL values is NULL (which evaluates to FALSE in logical expressions). So even if you have a unique constraint on column Field1 that allows NULL value, the table can have more than 1 record where the value of Field1 is NULL. If you want to treat NULL differently, in addition to unique constraint you need to write BEFORE INSERT/UPDATE triggers which check if there is a record in the table where Field1 IS NULL (and raise an error to prevent updating).