Telling MySQL connection to use UTF-8 with Django

Question

I've uploaded some data to a MySQL (5.5.15 for osx10.6) database using UTF8 encoding, though for some reason I had to specify its encoding as latin1 when I LOADed it.

I reckon this part is good because when I write to an OUTFILE, my unicode 'nu' characters come out OK in a terminal and in Vim.

However, when I look at them within a MySQL session, and when I try to edit the fields from Django admin, I get mangled characters (latin1?).

So, my question is: how to I tell a MySQL client and (especially) Django to read my database as UTF-8, the way it oughta?

At the command line, I tried

--default_character_set=utf8

and also

'SET NAMES UTF8;'

at the MySQL prompt, but they do not work.

When I look at VARIABLES LIKE 'char%', they're all set to utf8 apart from character_set_server which is latin1. If I set it to utf8.... that doesn't work either.

I'd be grateful if someone could give me some pointers here, especially about how to configure Django to talk to my database properly.

Thanks!

What do you mean you had to specify latin1 when you loaded it? Normally you should have specified UTF-8 at the creation of the database with that: "CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;" — Geoffroy CALA
– Geoffroy CALA, Commented Aug 23, 2011 at 16:59
The database itself is utf-8, collation utf_unicode_ci, but if I LOAD DATA LOCAL INFILE '<filename>' INTO TABLE <tablename> CHARACTER SET utf8 I get question marks, and as per various posts elsewhere on the internet, it only seems to upload with the correct encoding if I specify the (incorrect) 'CHARACTER SET latin1'. Very confusing. — xnx
– xnx, Commented Aug 23, 2011 at 23:26

sanmai · Accepted Answer · 2011-08-23 09:09:15Z

2

Add in your .cnf:

[mysqld]
character-set-server = utf8
collation-server     = utf8_general_ci
skip-character-set-client-handshake

answered Aug 23, 2011 at 9:09

sanmai

31.2k12 gold badges72 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

xnx Over a year ago

I have this (with utf_unicode_ci for collation_server) in /etc/my.cnf but it doesn't seem to work (though it does set character_set_server to utf8). I'm very confused...

sanmai Over a year ago

How did you install MySQL? I suspect it uses other that /etc/my.cnf configuration file.

Collectives™ on Stack Overflow

Telling MySQL connection to use UTF-8 with Django

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related