44

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

Sam
  • 7,252
  • 16
  • 46
  • 65
nubela
  • 1
  • 24
  • 75
  • 123

6 Answers6

47

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

AymDev
  • 6,626
  • 4
  • 29
  • 52
Tomasz Zieliński
  • 16,136
  • 7
  • 59
  • 83
  • 22
    For those without command line access, you can quickly run `SHOW TABLES;` in your database, paste them into NimbleText at http://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;` – jocull Nov 05 '13 at 16:18
  • It apply for a 125MB data base?' – ValRob Jun 12 '18 at 09:05
37

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

Jonas Staudenmeir
  • 24,815
  • 6
  • 63
  • 109
Thu 01 Jan 1970 000000 GMT
  • 540
  • 6
  • 7
16
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
nubela
  • 1
  • 24
  • 75
  • 123
  • 12
    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! – Charlie Gorichanaz May 15 '14 at 07:06
  • after the sed: uconv --from-code latin1 --to-code utf8 dump_utf.sql > dump_utf_fixed.sql – scribe Jun 05 '15 at 01:10
  • @marcolopes The **s**tream **ed**itor. `man sed` or look it up on Wikipedia or the like. – Nick T Dec 04 '17 at 19:24
  • 1
    @marcolopes something she sed =) – Eli Apr 25 '19 at 20:14
1

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;
Neeraj Tangariya
  • 1,159
  • 1
  • 17
  • 29
0

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%';
Rich
  • 6,470
  • 15
  • 32
  • 53
tippingpints
  • 1
  • 2
0

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;
Guillermo
  • 159
  • 1
  • 3