How to change all the tables in my database to UTF8 character set?

Question

My database is not in UTF8, and I'd like to convert all the tables to UTF8, how can I do this?

Solution 1

mysqldump --user=username --password=password --default-character-set=latin1 --skip-set-charset dbname > dump.sql
sed -r 's/latin1/utf8/g' dump.sql > dump_utf.sql
mysql --user=username --password=password --execute="DROP DATABASE dbname; CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;"
mysql --user=username --password=password --default-character-set=utf8 dbname < dump_utf.sql

Solution 2

For single table you can do something like this:

ALTER TABLE tab CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

For the whole database I don't know other method than similar to this:

http://www.commandlinefu.com/commands/view/1575/convert-all-mysql-tables-and-fields-to-utf8

Solution 3

replace my_database_name with your database name

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'my_database_name' AND TABLE_TYPE != 'VIEW';

this will build lots of queries which you can run

Comments

[jocull]

For those without command line access, you can quickly run SHOW TABLES; in your database, paste them into NimbleText at nimbletext.com/live and then use this pattern to generate the change script: ALTER TABLE `$0` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

[Charlie Gorichanaz]

And hopefully your database doesn’t contain “latin1” inside any of the content, such as blog posts about character encoding… else your users may have grounds for a lawsuit if your post conversion website is suddenly offering erroneous database commands!

[scribe]

after the sed: uconv --from-code latin1 --to-code utf8 dump_utf.sql > dump_utf_fixed.sql

[Nick T]

@marcolopes The stream editor. man sed or look it up on Wikipedia or the like.

[Smith]

You should also add ` AND Table_Type != 'VIEW'`

[ValRob]

It apply for a 125MB data base?'

[Michael Scott Asato Cuthbert]

I would suggest at the same time going not to utf8 but utf8mb4 so that astral plane characters such as emoji can also be stored.

[Eli]

@marcolopes something she sed =)