欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL inserts duplicate records despite unique index | Lease_MySQL

程序员文章站 2022-05-03 12:59:38
...
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:

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. MySQL inserts duplicate records despite unique index | Lease_MySQL 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!