有效查询不区分大小写的MongoDB（通过pymongo） [英] Querying MongoDB (via pymongo) in case insensitive efficiently

问题描述

我当前正在使用python（金字塔）创建一个网站，该网站要求用户注册并登录。系统允许用户选择用户名，该用户名可以由大写字母，小写字母和数字组成。 / p>

当确保两个用户不会意外共享同一用户名时出现问题，即在我的系统中， randomUser应与 RandomUser或 randomuser相同'。

不幸的是（在这种情况下）由于Mongo将字符串区分大小写，因此可能会有许多用户使用相同的用户名。

我知道查询mongo区分大小写的字符串的方法：

  db .stuff.find_one（{ foo：/ bar / i}）; 
  

但是，在使用pymongo的查询方法中，这似乎不起作用：

 用户名='/'+ str（用户名）+'/ i'
响应= request.db ['用户'] .find_one（ { username：username}，{ username：1}）
  

这是正确的方法吗构造查询pymongo的查询（我假设不是）？

此查询将在创建或登录用户帐户时使用（因为它必须检查如果系统中存在用户名）。我知道这不是最有效的查询，因此仅用于登录或帐户创建是否应该很重要？是否更需要执行诸如强制用户仅选择小写用户名（完全不需要区分大小写的查询）之类的事情？

解决方案

PyMongo使用本地python正则表达式，就像mongo shell使用本地javascript正则表达式一样。要编写与您在上面的shell中编写的内容等效的查询，可以使用：

  db.stuff.find_one（{ 'name'：re.compile（username，re.IGNORECASE）}）
  

请注意，这将避免但是使用 name 字段上可能存在的任何索引。不区分大小写的搜索或排序的常见模式是在文档中有第二个字段，例如 name_lower ，该字段总是在 name 更改（在这种情况下，更改为 name 的小写版本）。然后，您将查询这样的文档：

  db.stuff.find_one（{'name_lower'：username.lower（） }）
  

I'm currently creating a website in python (pyramid) which requires users to sign up and log in. The system allows for users to choose a username which can be a mixture of capital letters, lowercase letters, and numbers.

The problem arises when making sure that two users don't accidentally share the same username, i.e. in my system 'randomUser' should be the same as 'RandomUser' or 'randomuser'.

Unfortunately (in this case) because Mongo stores strings as case sensitive, there could potentially be a number of users with the 'same' username.

I am aware of the method of querying mongo for case insensitive strings:

db.stuff.find_one({"foo": /bar/i});

However, this does not seem to work in my query method using pymongo:

username = '/' + str(username) + '/i'
response = request.db['user'].find_one({"username":username},{"username":1})

Is this the correct way of structuring the query for pymongo (I'm assuming not)?

This query will be used whenever a user account is created or logged in to (as it has to check if the username exists in the system). I know it's not the most efficient query, so should it matter if it's only used on log ins or account creation? Is it more desirable to instead do something like forcing users to choose only lowercase usernames (negating the need for case-insensitive queries altogether)?

解决方案

PyMongo uses native python regular expressions, in the same way as the mongo shell uses native javascript regular expressions. To write the equivalent query of what you had written in the shell above, you would use:

db.stuff.find_one({'name': re.compile(username, re.IGNORECASE)})

Note that this will avoid using any index that may exist on the name field, however. A common pattern for case-insensitive searching or sorting is to have a second field in your document, for instance name_lower, which is always set whenever name changes (to a lower-cased version of name, in this case). You would then query for such a document like:

db.stuff.find_one({'name_lower': username.lower()})

这篇关于有效查询不区分大小写的MongoDB（通过pymongo）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！