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?

Answer 1

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

Answer 2

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

Answer 3

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

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;