mysql唯一索引失效原因分析

在开发中遇到一个mysql唯一索引失效的问题。
问题分析如下：
在给表创建唯一索引，想要约束数据不能重复时，需要注意，如果索引中有字段为空，则该条数据对应的唯一约束失效。

比如，如下一张表：

CREATE TABLE `people` (  `user_id` varchar(10) DEFAULT NULL,  `name` varchar(255) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1. 索引字段只有一个，该字段为null，则唯一索引失效。 设置user_id为唯一索引：

ALTER TABLE `people`ADD UNIQUE INDEX `user_id_idx` (`user_id`) USING BTREE ;

因为设置了唯一索引，所以如果想存两条相同user_id的数据，是不能成功的。

user_id name001     张三001     李四

但是，如果user_id为null，则此唯一索引失效，表中存入多条为null的数据。

user_id name001     张三null    李四null    李四

2. 索引字段有多个，只要有一个字段为null，则对应唯一索引失效。

ALTER TABLE `people`DROP INDEX `user_id_idx` ,ADD UNIQUE INDEX `user_id_idx` (`user_id`, `name`) USING BTREE ;

那么，如果想存入两条相同的数据，是不允许的。

user_id name001     张三001     张三

但是，如果有一个字段为空，则整个唯一索引失效，表中可以存储以下数据：

user_id name001     张三001     null001     null

经验教训：在表唯一索引设计时，尽量全部采用非空字段，且不要添加多余字段。
一些为了提高查询性能的可能为空的索引字段建议去单独创建索引。