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?

Humanize

Answer 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

Humanize

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! – Azilian

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

Answer 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

Gravesend

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; – Sevik

It apply for a 125MB data base?' – Kt

Answer 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

Tonettetoney

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

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. – Bellerophon

Answer 4

To change the collation in phpMyAdmin just follow this simple steps:

Method 1

• open phpMyAdmin and select your database.

• After click on database click on operation tab.

• Next, Scroll down the page, you will see the collation section.

  • set the collation do you want and click the checkbox.
  • your collation will be updated.

Note: If you find any difficulty using method 1 follow method 2 using sql command line.

Method 2

Using command Line

• Open phpMyAdmin and click on SQL tab.
• Next, write command for changing the collation for your database.

# syntax command:

ALTER DATABASE your_db_name CHARACTER SET utf8 COLLATE write_collation;

# e.g:
ALTER DATABASE temprory CHARACTER SET utf8 COLLATE utf8_general_ci;

Buxom

Answer 5

Better yet, use Percona's tool kit. I'd audit your indices before updating to utf8mb4 as there are issues with key length.

SELECT CONCAT('pt-online-schema-change --alter "CONVERT TO CHARACTER SET utf8 
COLLATE utf8_unicode_ci" t=', TABLE_NAME, ',D=DB_NAME,u=USER_NAME --statistics --execute') 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_TYPE != 'VIEW' AND TABLE_COLLATION NOT LIKE '%utf8%';

Throughout

Answer 6

in my case I have several schemas due to a bad migration.

Joining the other answers I did an ALTER command generator in this case to charset=latin1 and collate=latin1_general_ci for all the tables of all the schemas that doesn`t match latin1_general_ci

Hope it helps.

    SELECT CONCAT('ALTER TABLE ', tbl.ts,'.',tbl.tn, ' CHARACTER SET latin1
    COLLATE latin1_general_ci, CHANGE COLUMN \`', tbl.tc, '\` \`'
    ,tbl.tc,'\` ', tbl.tct, ' CHARACTER SET latin1 COLLATE 
    latin1_general_ci;' ) command FROM (SELECT table_schema ts, table_name 
    tn, column_name tc, COLUMN_TYPE tct FROM information_schema.columns  
    WHERE collation_name != 'latin1_general_ci' AND table_schema not in
    ('information_schema','mysql','performance_schema','sys')) tbl;

Parve