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?

Solution 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;

Solution 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;'),
    ]

Solution 3

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Solution 4

Note that if you really did want to change the collation for just one column (I can't think why you might, but who knows) then this is the syntax to alter a TEXT column called DESCRIPTION in the ITEMS table to UTF-8, binary, non-null:

ALTER TABLE ITEMS CHANGE DESCRIPTION DESCRIPTION TEXT CHARACTER SET utf8
    COLLATE utf8_bin NOT NULL;

There isn't a case-sensitive UTF-8 collation per se but the utf8_bin collation works for most cases.

View more solutions