How to change the character set of an existing database

Using Django
1

We have a Django application with historical data with the character set of “latin1” as default. We now need to change the character set to “utf8mb4” for multi-culture compatibility.

I followed a post on Stack Overflow (https://stack overflow.com/a/55516531/7903749) and added the below piece of code into the DATABASES = { ... } segment of the project’s settings.py file. And, it seems that this code change defines the mode of database connection when starting up the Django application, and it does change the character set in the database. Am I right on this point?

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'my_db',
        'USER': 'my_user',
        'PASSWORD': 'my_pass',
        'HOST': 'my.host',
        'OPTIONS': {
            'charset': 'utf8mb4'  # This is the important line
        }
    }
}

If my understanding was correct above, my question is:

What is the best way to change the character set of the existing database?

  • Do I need to run SQL statements to change the character set in all the tables, views, and columns?
  • Or it is better to dump the data, re-create the database with the character set in “utf8mb4” then import the data again?

I am also worried about whether there changing from “latin1” to “utf8mb4” would affect the indices on the text columns because the new character set takes longer space. My database is 10.5.11-MariaDB.

Any hints will be highly appreciated, and just let me know if you need more information.

2

Hi simpleopen,

I took a cursory look at the PR that attempted to handle this within django, the ticket referenced and ended up at adamchainz’ django-mysql project. I believe this page describing the check for utf8mb4 will be most helpful as it mentions most of your questions.

Let us know if there’s any confusion or further questions.