Case Insensitive Indexes¶
On this page
3.4 新版功能.
Case insensitive indexes support queries that perform string comparisons without regard for case.
You can create a case insensitive index with db.collection.createIndex() by specifying the collation parameter as an option. For example:
db.collection.createIndex( { "key" : 1 },
{ collation: {
locale : <locale>,
strength : <strength>
}
} )
To specify a collation for a case sensitive index, include:
- locale: specifies language rules. See Collation Locales for a list of available locales.
- strength: determines comparison rules. A value of 1 or 2 indicates a case insensitive collation.
For additional collation fields, see Collation.
Behavior¶
Using a case insensitive index does not affect the results of a query, but it can increase performance; see Indexes for a detailed discussion of the costs and benefits of indexes.
To use an index that specifies a collation, query and sort operations must specify the same collation as the index. If a collection has defined a collation, all queries and indexes inherit that collation unless they explictly specify a different collation.
Examples¶
Create a Case Insensitive Index¶
To use a case insensitive index on a collection with no default collation, create an index with a collation and set the strength parameter to 1 or 2 (see Collation for a detailed description of the strength parameter). You must specify the same collation at the query level in order to use the index-level collation.
The following example creates a collection with no default collation, then adds an index on the type field with a case insensitive collation.
db.createCollection("fruit")
db.fruit.createIndex( { type: 1},
{ collation: { locale: 'en', strength: 2 } } )
To use the index, queries must specify the same collation.
db.fruit.insert( [ { type: "apple" },
{ type: "Apple" },
{ type: "APPLE" } ] )
db.fruit.find( { type: "apple" } ) // does not use index, finds one result
db.fruit.find( { type: "apple" } ).collation( { locale: 'en', strength: 2 } )
// uses the index, finds three results
db.fruit.find( { type: "apple" } ).collation( { locale: 'en', strength: 1 } )
// does not use the index, finds three results
Case Insensitive Indexes on Collections with a Default Collation¶
When you create a collection with a default collation, all the indexes you create subsequently inherit that collation unless you specify a different collation. All queries which do not specify a different collation also inherit the default collation.
The following example creates a collection called names with a default collation, then creates an index on the first_name field.
db.createCollection("names", { collation: { locale: 'en_US', strength: 2 } } )
db.names.createIndex( { first_name: 1 } ) // inherits the default collation
Insert a small collection of names:
db.names.insert( [ { first_name: "Betsy" },
{ first_name: "BETSY"},
{ first_name: "betsy"} ] )
Queries on this collection use the specified collation by default, and if possible use the index as well.
db.names.find( { first_name: "betsy" } )
// inherits the default collation: { collation: { locale: 'en_US', strength: 2 } }
// finds three results
The above operation uses the collection’s default collation and finds all three documents. It uses the index on the first_name field for better performance.
It is still possible to perform case sensitive searches on this collection by specifying a different collation in the query:
db.names.find( { first_name: "betsy" } ).collation( { locale: 'en_US' } )
// does not use the collection's default collation, finds one result
The above operation finds only one document, because it uses a collation with no strength value specified. It does not use the collection’s default collation or the index.