在Django中区分大小写的mysql搜索 [英] Case insensitive search in django for mysql

问题描述

我正在尝试对模型中的字段内的子字符串进行不区分大小写的搜索。

I'm trying to do a case insensitive search for a substring within a field in my model.

我的模型：

class doctor(models.Model):
    docid = models.AutoField(primary_key=True, unique=True) # Need autoincrement, unique and primary
    name = models.CharField(max_length=35)
    username = models.CharField(max_length=15)
    regid = models.CharField(max_length=15, default="", blank=True)
    photo = models.CharField(
        max_length=35, default="", blank=True)
    email = models.EmailField(default="", blank=True)
    phone = models.CharField(max_length=15)
    qualifications = models.CharField(
        max_length=50, default="", blank=True)
    about = models.CharField(
        max_length=35, default="", blank=True)
    specialities = models.CharField(
        max_length=50, default="", blank=True)
    department = models.CharField(max_length=50, default="ENT", blank=True)
    fees = models.FloatField(default=300.0)
    displayfee = models.IntegerField(default=0, blank=True)
    slotrange = models.CharField(max_length=50, blank=True)
    slotdurn = models.IntegerField(default=10)
    breakrange = models.CharField(
        max_length=50, default="", blank=True)
    slotsleft = models.CharField(
        max_length=50, default="", blank=True)
    def __str__(self):
        return self.name
    def Range(self):
        return self.slotrange
    def listslots(self):
        SlotRange = self.slotrange
        SlotDurn = self.slotdurn
        startime = SlotRange.split('-')[0]
        endtime = SlotRange.split('-')[1]
        sthr, stmin = SplitTimeString(startime)
        enhr, enmin = SplitTimeString(endtime)
        print(stamptoday(sthr, stmin))
        print(stamptoday(enhr, enmin))
        startstamp = stamptoday(sthr, stmin)
        endstamp = stamptoday(enhr, enmin)
        secdurn = SlotDurn*60
        slotlist = []
        for sec in range(startstamp, endstamp, secdurn):
            enttime = sec + secdurn
            myrange = ("%s - %s" % (HumanTime(sec),
                                    HumanTime(enttime)))
            slotlist.append(myrange)
        return slotlist

在我的MySQL数据库的名称字段下，是两行，其名称分别为 Joel和 Jaffy Joel'。

Under the field 'name' in my mysql database, are two rows with values for name as 'Joel' and 'Jaffy Joel'.

当我这样搜索时：

from appointments.models import customer, doctor, appointment
doctor.objects.filter(name__icontains='joel')

输出：

<QuerySet []>

但是当我这样做时：

doctor.objects.filter(name__icontains='Joel')

输出：

<QuerySet [<doctor: Joel>, <doctor: Jaffy Joel>]>

不区分大小写的搜索为什么不适合小写搜索？

Why isnt the case insensitive search working for a lowercase search?

推荐答案

如 Django文档，

在MySQL中，数据库表的排序规则设置确定
精确比较区分大小写。这是一个数据库设置，而不是
是Django设置。可以将MySQL表配置为使用区分大小写的
比较，但是需要权衡取舍。有关
的更多信息，请参见数据库
文档中的归类部分。

In MySQL, a database table’s "collation" setting determines whether exact comparisons are case-sensitive. This is a database setting, not a Django setting. It’s possible to configure your MySQL tables to use case-sensitive comparisons, but some trade-offs are involved. For more information about this, see the collation section in the databases documentation.

我用django.db.models import Q，CharField
从django.db.models.functions

In my case, I solved it with

from django.db.models import Q,CharField
from django.db.models.functions import Lower
CharField.register_lookup(Lower, "lower")
doctor.objects.filter(Q(name__lower__contains='joel'))

这方面的提示是django用户邮件列表中的Matthew Pava提供的。

The tip in this regard was provided by Matthew Pava from the django-users mailing list.

这篇关于在Django中区分大小写的mysql搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！