django查询mysql 区分大小写,在Django中区分大小写的mysql搜索

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

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

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

When I do the search like this:

from appointments.models import customer, doctor, appointment

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

Output:

But when I do:

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

Output:

, ]>

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

解决方案

As pointed out in the django docs,

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.

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'))

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