CODEWITHSUNDEEP
mysqlutf-8collationalter
nubela
nubela

Reputation: 17284

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

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

Upvotes: 45

Views: 63477

Answers (6)

Guillermo
Guillermo

Reputation: 179

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;

Upvotes: 0

Neeraj Tangariya
Neeraj Tangariya

Reputation: 1407

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. enter image description here

  • 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. enter image description here

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. enter image description here
  • 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;

Upvotes: 3

tippingpints
tippingpints

Reputation: 1

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

Upvotes: 0

Thu 01 Jan 1970 000000 GMT
Thu 01 Jan 1970 000000 GMT

Reputation: 557

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

Upvotes: 39

Tomasz Zieliński
Tomasz Zieliński

Reputation: 16346

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

Upvotes: 47

nubela
nubela

Reputation: 17284

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

Upvotes: 16

Related Questions