在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..)

推荐答案

如@ dan-klasson所述，

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屋！