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?

I find the case insensitive collations weird; I *expect* my string matches to be case sensitive unless I explicitly ask them not to be... Anyhow, all the UTF8 collations have case sensitive and case insensitive variations, so you can still keep whichever behaviour you prefer. — El Yobo, May 19 '11 at 00:33

Eron Villarreal · Accepted Answer · 2011-05-19T00:35:54.720

26

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;

edited May 19 '11 at 00:35

answered May 19 '11 at 00:29

Eron Villarreal

995
10
15

Comment @user1450663: As officially stated [here](https://docs.djangoproject.com/en/dev/ref/databases/#collation-settings), Django doesn't support any way for collation manipulation through ORM. – Damon Jan 23 '13 at 13:34
2

What's the difference between table and columns options? Seems both of them changing collation for whole table – sunprophit Nov 22 '16 at 13:20

alexey_efimov · Answer 2 · 2016-08-31T06:28:47.967

5

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

edited Aug 31 '16 at 06:28

answered Jul 11 '16 at 10:01

alexey_efimov

1,541
2
12
16

This was helpful in conjunction with the SQL below in David G's post. – Nostalg.io Aug 30 '16 at 23:32
1

When you rollback to some migration, it gives `... is not reversible`. How do I use `reverse_code=migrations.RunPython.noop` with `RunSQL` operation? – Hussain Jan 09 '17 at 11:29
1

@Hussain you can use `reverse_sql` as second param in `RunSQL`. Elsewere can pass `RunSQL.noop` as `sql` or `reverse_sql` param. [documentation](https://docs.djangoproject.com/en/1.10/ref/migration-operations/#runsql) – alexey_efimov Jan 09 '17 at 11:43
Yes. That's it I think. But I read here here that I don't need to change the charset at all. http://stackoverflow.com/a/1198655/1637867 – Hussain Jan 09 '17 at 11:45

score 4 · Answer 3 · answered Dec 31 '14 at 20:21

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.

score 4 · Answer 4 · answered May 18 '11 at 19:43

4

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

answered May 18 '11 at 19:43

MBarsi

2,417
1
18
18

3

While useful, this only changes the default for new tables, it doesn't alter the collation for existing ones (which is what the OP needs). – El Yobo May 19 '11 at 00:31

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

4 Answers4

Linked