Convert a MySQL database from latin to UTF-8

Question

I am converting a website from ISO to UTF-8, so I need to convert the MySQL database too.

On the Internet, I read various solutions, I don't know which one to choose.

Do I really need to convert my varchar columns to binary, then to UTF-8 like that:

ALTER TABLE t MODIFY col BINARY(150);
ALTER TABLE t MODIFY col CHAR(150) CHARACTER SET utf8;

It takes a long time to do that for each column, of each table, of each database.

I have 10 databases, with 20 tables each, with around 2 - 3 varchar columns (2 queries each column), this gives me around 1000 queries to write! How to do it?

Resolved : I post the code that I have used:

PASSWORD=""
db=$1

mysqldump --password=$PASSWORD --set-charset --skip-set-charset --add-drop-table --databases "$db" > /home/dev/backup/bdd.sql

QUERY="ALTER DATABASE \`$db\` DEFAULT CHARACTER SET utf8;"
mysql --password=$PASSWORD --database "$db" -e "$QUERY"

mysql --password=$PASSWORD --default-character-set=utf8 < /home/dev/backup/bdd.sql

See the answer below for more information.

Aren't --set-charset and --skip-set-charset directly competing flags? Docs say they affect the use of SET NAMES, but doesn't set the DEFAULT CHARSET=X parameter that is output via mysqldump per table. (mysql 5.5) — fideloper
– fideloper, Commented Jan 28, 2014 at 21:19

Peter Mortensen · Accepted Answer · 2010-05-03 10:13:05Z

19

You can do that very easily using a dump. Make a dump using

mysqldump --skip-opt --set-charset --skip-set-charset

Then create another database, set its default character set to UTF-8 and then load your dump back with:

mysql --default-character-set=<your iso encoding>

The main idea is to make a dump without any sign of data encoding.
So, at create time, the table's encoding would be inherit from the database encoding and set to UTF-8. And with --default-character-set we tell MySQL to recode our data automatically.

edited May 3, 2010 at 10:13

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Mar 30, 2010 at 9:21

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

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

9 Comments

Matthieu Napoli Over a year ago

Oh this seems nice ! Why isn't that on the documentation, or nobody talks about that anywhere, thanks I will test it

Your Common Sense Over a year ago

@Matthieu as it seems you're going to make it with --all-databases, please make it with precautions. Make a backup and test whole algorithm on one database first.

Matthieu Napoli Over a year ago

Well I just tested (on one database only, to be sure) and this doesn't change the encoding of the columns (they are still in "latin"). Just to be sure, for the mysqldump, the 3 parameters don't need values ?

Your Common Sense Over a year ago

@Matthieu this is actually 2 parameters, as far as I understand. --skip-opt --set-charset tell database not to add default charset clause to the table definitions. And --skip-set-charset remove SET NAMES query from the top of the dump. How do you know of data encoding? utf-8 is very similar to latin1 What does it say of table encoding if you run SHOW CREATE TABLE tablename for the new table? is there any character sets in the dump? Do you name utf8 properly (as it differ from usual utf-8)

Matthieu Napoli Over a year ago

Thanks I've got it working. What I had to do is dump, empty the database (drop tables, BUT KEEP DATABASE), set its encoding to UTF8, then re-import the content. Then the content is fully UTF8, else it doesn't work fully. I've posted the code above, in my question.

|

Michael J. Barber · Accepted Answer · 2012-04-05 13:49:52Z

6

I'm using mysqldump Ver 10.11 Distrib 5.0.77

For some reason create options Engine and auto_increment was omitted. This caused a lot of insert errors, because auto_increment was gone on primary key fields.

This worked for me. I'm using --opt and using sed to remove charset from sql file.

mysqldump -p --opt --skip-set-charset --add-drop-table dbname > /tmp/dbname.sql
sed -i 's/DEFAULT CHARSET=latin1//g' /tmp/dbname.sql 
ALTER DATABASE dbname DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_danish_ci;
mysql -p --default-character-set=utf8 db < /tmp/dbname.sql

edited Apr 5, 2012 at 13:49

Michael J. Barber

25.2k9 gold badges71 silver badges92 bronze badges

answered Apr 5, 2012 at 10:10

Klaus Hessellund

1693 silver badges6 bronze badges

1 Comment

fideloper Over a year ago

As Mysql 5.5 will output DEFAULT CHARSET=latin1, which the --default-character-set=utf8 won't overwrite on import, this step becomes necessary. I used sed -i 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/g' /tmp/dbname.sql to explicitly set it to utf8.

Max Zhuk · Accepted Answer · 2014-06-13 11:46:06Z

1

mysqldump --opt --skip-set-charset --default-character-set='latin1' -u root -p revive_adserver --result-file='dump.sql'

vim dump.sql

:%s/latin1/utf8/gi

:wq

mysql -u root -p

ALTER DATABASE revive_adserver CHARACTER SET utf8;

\q

mysql -D revive_adserver -u root -p < dump.sql

answered Jun 13, 2014 at 11:46

Max Zhuk

111 bronze badge

Collectives™ on Stack Overflow

Convert a MySQL database from latin to UTF-8

3 Answers 3

9 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related