39

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

Sam
  • 7,157
  • 15
  • 45
  • 65
nubela
  • 15,816
  • 22
  • 71
  • 119

5 Answers5

46

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
  • 5,278
  • 2
  • 28
  • 46
Tomasz Zieliński
  • 15,678
  • 7
  • 56
  • 78
  • 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
33

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
  • 23,013
  • 4
  • 50
  • 94
Thu 01 Jan 1970 000000 GMT
  • 499
  • 6
  • 5
15
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
  • 15,816
  • 22
  • 71
  • 119
  • 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
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
  • 5,918
  • 9
  • 33
  • 43
tippingpints
  • 1
  • 2
0

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
  • 730
  • 1
  • 12
  • 23