Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use a unique index in MongoDB. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

We use MongoDB data to store user information, the user table used to register users through the mobile phone number, so it is only natural to add a unique index (Unique) to the mobile phone number, which is not a problem. At a later stage, we need to change. You can also imagine that you can register your mobile phone number and email. At this time, due to the addition of the Unique index to the mobile phone number, in fact, there will be problems at this time.

Func init () {phoneIndex: = mgo.Index {Key: [] string {"phone"}, Unique: true,} col: = db.Collection (& User {}) col.EnsureIndex (phoneIndex)}

Of course, this problem is also easy to think of, when the user registers through the mailbox when the mobile phone number is filled in the blank, there is no problem for the first time, and the next user registers in this way, it will prompt the index value established on the phone to repeat, which is normal, because two null values have been inserted, so note that it is an empty string, not null.

So we tried to modify, because MongoDB is a document-based and flexible database, so it will not affect to insert one or two more fields, so we try to modify the entry of the User entity Phone field, when the phone is an empty string, do not allow this field to be inserted. So we added the omitempty tag to the phone field (our microservice is written in the GE language). Here are some of the contents of User:

Type User struct {Email string `bson: "email" `Salt string `bson: "salt" `Phone string `bson: "phone,omitempty" `IDCard string `bson: "idcard" `RealName string `bson: "realname" `AuthStatus int `bson: "auth_status" `}

You can see that the phone field is tagged with an omitempty to indicate that it is not inserted when the field is blank. There will still be problems, so if there is still a problem, why would you want to solve it this way? This stems from experience with Mysql, which habitually assumes that, like MongoDB and Mysql, the value of null will not be indexed. That is, in Mysql, if the phone value is Null in multiple records, it is allowed.

The above approach will still report an error, indicating that a duplicate value has been inserted, but at this time it is not an empty string, but null. So sometimes don't bring Mysql. Mysql is fine, but Mongo is not. Mongo still indexes the record, even if the field is inserted.

I like to read the official documents. Here are the official documents of MongoDB:

If a document does not have a value for the indexed field in a unique

Index, the index will store a null value for this document. Because of

The unique constraint, MongoDB will only permit one document that

Lacks the indexed field. If there is more than one document without a

Value for the indexed field or is missing the indexed field, the index

Build will fail with a duplicate key error.

In fact, it has been made very clear, a little bit of English should be able to understand, the following is a translation:

If the document does not have the value of the index field in the unique index, the index stores null values for this document. Due to unique constraints, MongoDB allows only one document that is missing an index field. If there are multiple documents that do not have the value of the index field or are missing the index field, the index construction fails with a duplicate key error.

That is to say, even if this field is not in the document, there will be a null value in the field, and two null values cannot appear on the field at the same time, which is why the above approach still does not work. In fact, the above practice also breaks the data structure. Although the mobile phone number is not filled in, this field should not be missing in the database. Although it is a non-relational database, after all, you have to consider the business design.

Solution method

Is there no solution? Of course, Mongo provides Sparse Index, which is translated into sparse indexes. Here is an example of creating a sparse index:

Db.getCollection ("test") .createIndex ({"phone": 1}, {sparse: true})

After the above statement is executed, documents that do not have phone fields are not indexed. In other words, it is indexed only when it exists, so it can be used in combination with the Unique index at this time. Unqiue is unique, and Sparse is indexed only if it exists. So, when phone or email is empty, we don't have to insert it, which is possible.

Db.getCollection ("test") .createIndex ({"phone": 1}, {sparse: true,unique: true})

The above is the mongo shell syntax. We usually build an index in the code and modify it as follows (of course, we still need to have the omitempty tag for the Phone field in the User structure):

Func init () {phoneIndex: = mgo.Index {Key: [] string {"phone"}, Unique: true, Sparse: true,} col: = db.Collection (& User {}) col.EnsureIndex (phoneIndex)} about how to use a unique index in MongoDB is shared here. I hope the above content can be helpful and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.