How do I create a Django migration to set charset and collation for all existing tables and all future ones?

Question

I'm using Django, Python 3.7 and MySql 5.7. I want to set the default charset and collation for all existing and all future tables to

DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I have already created some migrations,

(venv) localhost:maps davea$ ls web/maps/migrations/
0001_initial.py  __init__.py  __pycache__

Is there any way I can create such a migration? I'm happy to blow away from database and start from scratch if that's what it takes.

score 1 · Answer 1 · answered Mar 30 '20 at 00:50

1

You have to change the CHARSET of the database, django has nothing to do with it: How to convert an entire MySQL database characterset and collation to UTF-8?

answered Mar 30 '20 at 00:50

jTiKey

696
5
13

Perhaps I should ask a simpler question -- if I wanted to change the charset and collation of a single model (table) only, is that possible and how would that be done with a migration? – Dave Mar 30 '20 at 02:02
Incomplete -- Also change the default on each _table_ in case someone adds a column without specifying charset or collation. – Rick James Apr 09 '20 at 05:04

score 0 · Answer 2 · answered May 05 '21 at 11:57

I came across this problem now where I tried creating a new record where the value of a field_x of type django.db.models.TextField() contains emoticons. It resulted to error:

django.db.utils.OperationalError: (1366, "Incorrect string value: '\\xF0\\x9F\\x99\\x82 :...' for column `db_name`.`table_name`.`field_x` at row 1")

I set the charset to utf8mb4 in the proj/settings.py specifically under the DATABASES field:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.mysql",
        "NAME": "any",
        "USER": "any",
        "PASSWORD": "any",
        "HOST": "any",
        "PORT": "any",
        "OPTIONS": {
            "charset": "utf8mb4",
        },
    }
}

The creation of the record became successful. Also, fetching the created record shows that it correctly contains the saved emoticons.

>>> record = TableName.objects.get(id=93)
>>> record.field_x
'masters trickeds us!!!      MYYYYY PRECIOOOOOUS~!  :)  :) ^_^^__^'

Some further reference you could check:

http://blog.manbolo.com/2014/03/31/using-emojis-in-django-model-fields

How do I create a Django migration to set charset and collation for all existing tables and all future ones?

2 Answers2