django extend query condition `COLLATION`

Question

django==1.11.9

mysql version 5.6.38

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+

I have an accent field in my data, and I set the unique field. However, multiple results will appear in the query. Although I know you can by ALTER TABLE test DEFAULT COLLATE utf8_bin; set the table properties. But I think the better way is through the query to add conditions SELECT * FROM test WHERE name = 'a' collate utf8_bin;.

Now I don't know how to use it in the queryset method. When I was in inserting data usually use update_or_create() method, which will directly lead to some other form (with accent) data is not inserted into it.

Can this query condition be extended in Django's queryset method?

https://stackoverflow.com/questions/22841132/django-making-a-query-with-custom-collation maybe this will help. — Sachin, Aug 09 '18 at 13:00

score 0 · Answer 1 · answered Aug 18 '18 at 05:30

ALTER TABLE test DEFAULT COLLATE ... only changes the "default". That is, when you later add a column, it will get that collation.

Instead:

ALTER TABLE test MODIFY xyz ... COLLATE ... for each column you need to change.

Or you could blindly change all the varchar/text columns:

`ALTER TABLE test CONVERT TO CHARACTER SET ... COLLATE ...;

Note that no index can be used when you have WHERE name = 'a' COLLATE ... -- It will have to check all rows even if you have INDEX(name).

..._bin collations check the bits; that is, they are case and accent sensitive sensitive. ..._ci collations are case and accent insensitive.

I would put the collation in the WHERE clause only when you need to vary the collation. Otherwise, build it into the column definition.

django extend query condition `COLLATION`

1 Answers1