你可以在Sqlite3（和Django）中实现不区分大小写的“唯一”约束吗？ [英] Can you achieve a case insensitive 'unique' constraint in Sqlite3 (with Django)?

问题描述

所以我们使用的是Python 2.5内置的默认sqlite3，我有一个Django模型类，其代码如下：

So let's say I'm using Python 2.5's built-in default sqlite3 and I have a Django model class with the following code:

class SomeEntity(models.Model):
    some_field = models.CharField(max_length=50, db_index=True, unique=True)

我有管理界面设置，一切似乎都工作正常，除了我可以创建两个SomeEntity记录，一个与some_field =一些值，一个与some_field ='Some Value'，因为some_field上的唯一约束似乎是区分大小写的。

I've got the admin interface setup and everything appears to be working fine except that I can create two SomeEntity records, one with some_field='some value' and one with some_field='Some Value' because the unique constraint on some_field appears to be case sensitive.

有没有办法强制sqlite在 >检查唯一性时的敏感比较？

Is there some way to force sqlite to perform a case insensitive comparison when checking for uniqueness?

我似乎没有在Django的文档中找到一个选项，我想知道是否有可以直接做的事情以sqlite来获得它的行为方式我想要的。 ： - ）

I can't seem to find an option for this in Django's docs and I'm wondering if there's something that I can do directly to sqlite to get it to behave the way I want. :-)

推荐答案

是的，可以通过使用以下命令向表添加唯一索引来轻松完成：

Yes this can easily be done by adding a unique index to the table with the following command:

CREATE UNIQUE INDEX uidxName on mytable（myfield COLLATE NOCASE）

CREATE UNIQUE INDEX uidxName ON mytable (myfield COLLATE NOCASE)

如果您对nonASCII信件需要不区分大小写，您将需要使用类似于以下命令注册您自己的COLLATION：

If you need case insensitivity for nonASCII letters, you will need to register your own COLLATION with commands similar to the following:

以下示例显示了排序错误方式的自定义归类：

The following example shows a custom collation that sorts "the wrong way":

import sqlite3

def collate_reverse(string1, string2):
    return -cmp(string1, string2)

con = sqlite3.connect(":memory:")
con.create_collation("reverse", collate_reverse)

cur = con.cursor()
cur.execute("create table test(x)")
cur.executemany("insert into test(x) values (?)", [("a",), ("b",)])
cur.execute("select x from test order by x collate reverse")
for row in cur:
    print row
con.close()

sqlite3的其他python文档显示为 here

Additional python documentation for sqlite3 shown here

这篇关于你可以在Sqlite3（和Django）中实现不区分大小写的“唯一”约束吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！