Rumah > pangkalan data > tutorial mysql > MySQL inserts duplicate records despite unique index | Lease_MySQL

MySQL inserts duplicate records despite unique index | Lease_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Lepaskan: 2016-05-31 08:47:05
asal
1196 orang telah melayarinya

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;
Salin selepas log masuk

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);
Salin selepas log masuk

I expected to get the following result:

mysql> select * from `table`;+------+------+------+| a| b| c|+------+------+------+|1 | NULL |6 |+------+------+------+1 row in set (0.00 sec)
Salin selepas log masuk

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)
Salin selepas log masuk

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!

Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan