MongoDB unique index实战

Part1:写在最前

MongoDB的 unique index索引这里有个坑，主要体现在对NULL值的处理上，本文加以复现。

整体环境：

MongoDB3.2.5

Part2:集合内容

PRIMARY>db.helei.find(){
"_id":ObjectId("58b7ea9544e98b24a5bdcef5"),"i":0,"username":"user0","age":8,"created":"ThuMar02201717:49:09GMT+0800(CST)"}
{
"_id":ObjectId("58b7ea9544e98b24a5bdcef6"),"i":1,"username":"user1","age":9,"created":"ThuMar02201717:49:09GMT+0800(CST)"}
{
"_id":ObjectId("58b7ea9544e98b24a5bdcef7"),"i":2,"username":"user2","age":82,"created":"ThuMar02201717:49:09GMT+0800(CST)"}
{
"_id":ObjectId("58b7ea9544e98b24a5bdcef8"),"i":3,"username":"user3","age":48,"created":"ThuMar02201717:49:09GMT+0800(CST)"}
{
"_id":ObjectId("58b7ea9544e98b24a5bdcef9"),"i":4,"username":"user4","age":27,"created":"ThuMar02201717:49:09GMT+0800(CST)"}
{
"_id":ObjectId("58b7ea9544e98b24a5bdcefa"),"i":5,"username":"user5","age":53,"created":"ThuMar02201717:49:09GMT+0800(CST)"}
{
"_id":ObjectId("58b7ea9544e98b24a5bdcefb"),"i":6,"username":"user6","age":42,"created":"ThuMar02201717:49:09GMT+0800(CST)"}
{
"_id":ObjectId("58b7ea9544e98b24a5bdcefc"),"i":7,"username":"user7","age":56,"created":"ThuMar02201717:49:09GMT+0800(CST)"}
{
"_id":ObjectId("58b7ea9544e98b24a5bdcefd"),"i":8,"username":"user8","age":5,"created":"ThuMar02201717:49:09GMT+0800(CST)"}
{
"_id":ObjectId("58b7ea9544e98b24a5bdcefe"),"i":9,"username":"user9","age":56,"created":"ThuMar02201717:49:09GMT+0800(CST)"}
{
"_id":ObjectId("58b8da80d8509e8f46fd9042"),"i":"10","age":50,"create":ISODate("2017-03-03T02:52:48.834Z")}
这里可以看到，我在helei集合中生成了i从0到10这些数据，而i:10文档中我故意漏写username这一个键值

Part3:集合索引情况

PRIMARY>db.helei.getIndexes()[{
"v":1,"key":{
"_id":1}
,"name":"_id_","ns":"helei.helei"}
,{
"v":1,"key":{
"age":1}
,"name":"idx_age","ns":"helei.helei"}
,{
"v":1,"unique":true,"key":{
"username":1}
,"name":"uk_username","ns":"helei.helei","background":true}
]这里可以看出在name列添加了unique index： uk_usernamePart4:验证当再有一个不包含username键值的文档被插入时，会抛出错误PRIMARY> db.helei.insert({
i:"11",age:51,create:new Date()}
)WriteResult({
"nInserted" : 0,"writeError" : {
"code" : 11000,"errmsg" : "E11000 duplicate key error collection: helei.helei index: uk_username dup key: {
: null }
"}
}
)

Warning:警告

如果一个文档没有对应的键，索引会将其作为null存储。

——总结——

如果对某个键建立了唯一索引，但插入了多个缺少该索引键的文档，由于集合已经存在一个该索引键值的值为null而导致插入失败。