Question: Strange case insensitive select behavior Django + mysql

Question

Strange case insensitive select behavior Django + mysql

Answers 1
Added at 2016-12-28 13:12
Tags
Question

I Have simple model in Django:

class Tag(Model):
    name = CharField(unique=True, max_length=50)

When I do:

t = 'Ansible'
print("Want tag: " + t)
tg, created = Tag.objects.get_or_create(name=t)
print("Got tag: " + tg.name)
print("Query: {}".format(Tag.objects.filter(name=t).query))
print("Query result: {}".format(Tag.objects.filter(name=t).first().name)) 

I get result:

Want tag: Ansible
Got tag: ansible
Query: SELECT `main_tag`.`id`, `main_tag`.`slug`, `main_tag`.`name`, `main_tag`.`added_time`, `main_tag`.`public_tips_count`, `main_tag`.`private_tips_count` FROM `main_tag` WHERE `main_tag`.`name` = Ansible
Query result: ansible

I use Django==1.10.3 and

# mysql --version
mysql  Ver 14.14 Distrib 5.5.34, for debian-linux-gnu (armv7l) using readline 6.2

What I expect?

I expect from get_or_create that it will create new tag named Ansible, but it returns some existent tag named ansible

Answers
nr: #1 dodano: 2016-12-28 13:12

Mysql out of the box is case insensitive. This behaviour has nothing to do with django.

The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation:

You can make it case sensitive at system, database, table or even column levels.

Source Show
◀ Wstecz