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

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

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

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

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%';

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;

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;