How do you change the collation type for a MySQL column?

Question

I'm having the utf-8 Vs. byte string problems mentioned here: Django headache with simple non-ascii string

I don't care about case sensitive matching in the MySQL columns, I just always want UTF-8 strings returned because I find it is impossible to deal with byte strings returned for character columns for non-ascii text.

How do I change my MySQL collation type so that UTF-8 strings are always returned through Django?

Answer 1

You need to be aware of the character-set/collation settings at the database/table/column levels. Column-level settings take precedence over the others. Because of this, I'm including commands you can use to perform these changes at each level of the db.

---

Inspect your current configuration (database):

SHOW CREATE DATABASE db_name;

Inspect your current configuration (table):

SHOW TABLE STATUS WHERE name='tbl_name'

Inspect your current configuration (columns):

SHOW FULL COLUMNS FROM tbl_name;

---

Change the character-set/collation (database):

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8;

Change the character-set/collation (table):

ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8;

Change the character-set/collation (columns):

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;

Answer 2

In django you must write your own migration:

./manage.py makemigrations --empty app_name

And fill empty migration with these sql command like this:

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations


class Migration(migrations.Migration):

    dependencies = [
        ('app', '0008_prev_migration'),
    ]

    operations = [
        migrations.RunSQL('ALTER DATABASE db_name DEFAULT CHARACTER SET utf8;'),
        migrations.RunSQL('ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8;'),
        migrations.RunSQL('ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;'),
    ]