MySql, InnoDB and Null Values

Question

I used to use the MyISAM storage engine for MySql, and I defined a combination of three fields that would be unique.

Now I have switched to InnoDB, which I assume caused this problem, and now NULL! = NULL.

So for the following table:

ID (Auto) |  Field_A   | Field_B  | Field_C

I can insert (Field_A, Field_B, Field_C) values ​​(1,2, NULL) (1,2, NULL) (1,2, NULL) infinitely many times.

How can I prevent this behavior?

Answer 1

-, field_a field_b . AUTO_INCREMENT , auto_increment:

CREATE TABLE your_table (
  ID int auto_increment not null,
  Field_A VARCHAR(45),
  Field_B VARCHAR(45),
  Field_C VARCHAR(45),
  key(ID), --without this, you'll get MySQL ERROR #1075
  primary key(field_a, field_b)
);

(MySQL ):

CREATE UNIQUE INDEX blah_ind USING BTREE ON your_table(field_a, field_b)