Django 3.2+: implementing case-sensitive string comparisons within MySQL tables

Question

Django documentation (3.2 to 4.0) states:

In MySQL, a database table’s collation determines whether string comparisons (such as the expression and substring of this function) are case-sensitive. Comparisons are case-insensitive by default.

from 3.2 on, there is mention that it is now possible to define at the level of the CharField the collation to use: https://docs.djangoproject.com/en/3.2/ref/databases/#collation-settings

but I find nowhere what value I should use for db_collation to enable case-sensitive string comparisons for a given CharField:

case_sensitive_collation = ???    
name = models.CharField(max_length=50, db_collation=case_sensitive_collation)

Just found this gist to do the contrary: CreateCollation and db_collation to implement a case-insensitive Charfield with Postgres > 12 https://gist.github.com/hleroy/2f3c6b00f284180da10ed9d20bf9240a

can somebody help me out?

Edit: here is the SHOW CREATE TABLE. the goal would be to have the field pieces case sensitive:

MariaDB [FormApp_db]> show create table puzzles_puzzlepieces; 

    +----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table                | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
    +----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | puzzles_puzzlepieces | CREATE TABLE `puzzles_puzzlepieces` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `pieces` varchar(255) NOT NULL,
      `uncolored_id` int(11) DEFAULT NULL,
      `piece_setup_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `pieces` (`pieces`),
      KEY `puzzles_puzzlepieces_uncolored_id_8afb6603_fk_puzzles_p` (`uncolored_id`),
      CONSTRAINT `puzzles_puzzlepieces_uncolored_id_8afb6603_fk_puzzles_p` FOREIGN KEY (`uncolored_id`) REFERENCES `puzzles_puzzlepieces` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3094388 DEFAULT CHARSET=utf8mb4 |
    +----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.003 sec)

and the generated SELECT:

>>> x = PuzzlePieces.objects.all()
>>> x[0]
SELECT `puzzles_puzzlepieces`.`id`,
       `puzzles_puzzlepieces`.`pieces`,
       `puzzles_puzzlepieces`.`uncolored_id`,
       `puzzles_puzzlepieces`.`piece_setup_id`
  FROM `puzzles_puzzlepieces`
 LIMIT 1

Execution time: 0.002870s [Database: default]

Please provide `SHOW CREATE TABLE` for the table that DJango generated. And the generated SQL for `SELECT`. That way we can discuss whether DJango translated into the 'correct' stuff in MySQL. — Rick James, Apr 21 '22 at 20:51
I added those info. I discovered while inquiring about a bug in my code that the filtering was not working as espected. I found out browsing on internet that the root cause was that CharField default case sensitivity was database dependant. As stated, I didn't changed anything yet, so the behaviour is the default one for MariaDB (as far as I understood). And I need to have the field `pieces` case sensitive — Skratt, Apr 27 '22 at 08:19
Modify the column to be COLLATE ascii_bin . (utf8mb4 is probably overkill, but if not, then use utf8mb4_bin.) — Rick James, Apr 28 '22 at 15:37
Thanks a lot @RickJames, it worked ! `db_collation='ascii_bin'` worked fine — Skratt, May 10 '22 at 07:15
Does this answer your question? [Case Sensitive collation in MySQL](https://stackoverflow.com/questions/4558707/case-sensitive-collation-in-mysql) — Abdul Aziz Barkat, May 10 '22 at 07:30
Thanks Abdul. No it did not at the time. I read it at the time, but it added to the confusion I was in. I was really laking the information and a practical example of what needed to be put inside `db_collation`. How to implement it in real world when you don't know about collation — Skratt, May 13 '22 at 06:03

score 0 · Accepted Answer · answered May 10 '22 at 07:24

To validate the solution proposed by @rickjames, I'll document an implementation of the answer here:

in settings.py:

if app_version == 'prod':
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.mysql',
            'OPTIONS': {
                'read_default_file': '/home/src/my.cnf'
            }
        }
    }
    DB_IS_MYSQL = True
else:
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.sqlite3',
            'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
        }
    }
    DB_IS_MYSQL = False

MYSQL_SENSITIVE_COLLATION = 'ascii_bin'  
DB_CASE_SENSITIVE_COLLATION  = {'db_collation': MYSQL_SENSITIVE_COLLATION} if DB_IS_MYSQL else {}
# Use: 
# from django.conf import settings
# pieces = models.CharField(max_length=255, default='empty', unique=True, **settings.DB_CASE_SENSITIVE_COLLATION)

in model.py:

name = models.CharField(max_length=50, **settings.DB_CASE_SENSITIVE_COLLATION)

Django 3.2+: implementing case-sensitive string comparisons within MySQL tables

1 Answers1