在Django中区分大小写的搜索,但在Mysql中被忽略 [英] Case sensitive search in Django, but ignored in Mysql
问题描述
我在Django模型中有一个用于存储唯一(哈希)值的字段.事实证明,数据库(MySQL/inno)不会对此类型(VARCHAR)进行区分大小写的搜索,即使我明确告诉Django进行区分大小写的搜索 Document.objects.get(hash__exact ="abcd123)
.因此,我都不需要返回"abcd123"和"ABcd123".
I have a field in a Django Model for storing a unique (hash) value. Turns out that the database (MySQL/inno) doesn't do a case sensitive search on this type (VARCHAR), not even if I explicitly tell Django to do a case sensitive search Document.objects.get(hash__exact="abcd123")
. So "abcd123" and "ABcd123" are both returned, which I don't want.
class document(models.Model):
filename = models.CharField(max_length=120)
hash = models.CharField(max_length=33 )
我可以将'hash field'更改为BinaryField,因此在数据库中它变成了LONGBLOB,并且确实进行了区分大小写的搜索(并且有效).但是,这对我来说似乎不是很有效.有没有更好的方法(在Django中),例如添加'utf8 COLLATE'?或在这种情况下正确的Fieldtype是什么?(是的,我知道我可以改用PostgreSQL..)
I can change the 'hash field' to a BinaryField , so in the DB it becomes a LONGBLOB , and it does do a case-sensitive search (and works). However, this doesn't seem very efficient to me. Is there a better way (in Django) to do this, like adding 'utf8 COLLATE'? or what would be the correct Fieldtype in this situation? (yes, I know I could use PostgreSQL instead..)
推荐答案
As @dan-klasson mentioned, the default non-binary string comparison is case insensetive by default; notice the _ci
at the end of latin1_swedish_ci
, it stands for case-insensetive.
You can, as Dan mentioned, create the database with a case sensitive collation and character set.
您可能还想知道您始终可以创建一个表,甚至只设置一个列以使用不同的排序规则(用于相同的结果).您还可以在创建后更改这些归类,例如每个表:
You may be also interested to know that you can always create a single table or even set only a single column to use a different collation (for the same result). And you may also change these collations post creation, for instance per table:
ALTER TABLE documents__document CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
此外,如果您不想更改数据库/表字符集/排序规则,则Django允许
Additionally, if you rather not change the database/table charset/collation, Django allows to run a custom query using the raw method. So you may be able to work around the change by using something like the following, though I have not tested this myself:
Document.objects.raw("SELECT * FROM documents__document LIKE '%s' COLLATE latin1_bin", ['abcd123'])
这篇关于在Django中区分大小写的搜索,但在Mysql中被忽略的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!