A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. – MySQL manual
I added the unique index to my table that made it look like this:
CREATE TABLE `table` (`a` int(11) DEFAULT NULL,`b` int(11) DEFAULT NULL,`c` int(11) DEFAULT NULL,UNIQUE KEY `a_b_c` (`a`,`b`,`c`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
I was trying to insert or increment values in it like this:
INSERT INTO `table` (a,b,c) VALUES (1,NULL,3),(1,NULL,3) ON DUPLICATE KEY UPDATE c=c+VALUES(c);
I expected to get the following result:
mysql> select * from `table`;+------+------+------+| a| b| c|+------+------+------+|1 | NULL |6 |+------+------+------+1 row in set (0.00 sec)
But it did not do that! I got this result instead:
mysql> select * from `table`;+------+------+------+| a| b| c|+------+------+------+|1 | NULL |3 ||1 | NULL |3 |+------+------+------+2 rows in set (0.00 sec)
I was clueless until I read the documentation of MySQL a little better:
This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. – MySQL manual
Especially the part where it says: “does not apply to NULL values” makes things much, much clearer.
