MySQL inserts duplicate records despite unique index | Lease_MySQL

okay, so i thought i knew quite a bit about databases and so i added a unique index. for those who do not know:

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:

Vinteo AI

利用人工智能在逼真的室内环境中创建产品可视化。无需设计师和产品照片拍摄

83

查看详情

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. Note that Microsoft SQL Server behaves different (they way I expected). So keep this is mind when using a unique index in MySQL, because I certainly did not expect this behavior!