How Convert MySQL Latin1 to utf8mb?

OmegaBiz
OmegaBiz used Ask the Experts™
on
Greetings,

What is the best method to convert an existing MySQL 5.5 Database with Latin1 language format to UTF8MB?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
we want the best option to convert this from latin1 to unicode language format
Tahir QureshiSystem Analyst

Commented:
How do I make all new tables utf8mb4

It can be done (for one database) while creating a database:

CREATE DATABASE dbname
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE       utf8mb4_unicode_ci;


How do I convert existing latin1 tables

If you have a table declared to be latin1 and correctly contains latin1 bytes, and you would like to change all the char/text columns to utf8...

ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;
This changes the definition and actively changes the necessary bytes in the columns.

ALTER TABLE tbl MODIFY col1 ... CHARACTER SET utf8mb4;
is similar to the above, but works only one column at a time, and needs exactly the right stuff in the MODIFY clause. Hence, it would be quite tedious. However, it is useful if you want to change only some of the columns.
Distinguished Expert 2017

Commented:
Test, backup before.

Backup, redifine charcter set, restore as another db...

It might be simpler/straight forward to backup/modify the schema, restore..... MySQL backup is a text file.........

Backup schema only, alter restore to new dbname restore backup of data only.
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Author

Commented:
Tahir will your recommended solution below convert the existing data from latin1 data to UTF8mb4 data?

ALTER TABLE tbl MODIFY col1 ... CHARACTER SET utf8mb4;

Author

Commented:
Would this work as well in regards to converting existing Latin1 data into UTF8 Character set?

Variant: Using mysqldump to fix strange characters

[original source: http://pastebin.com/iSwVPk1w]

ALWAYS BACK UP YOUR DATABASE FIRST!

Rather than using MySQL/MariaDB functions over phpMyAdmin, if you have console access you can do something along the lines of:

mysqldump -u root -p --opt --quote-names --skip-set-charset --default-character-set=latin1 wordpress > wordpress.sql

This creates a file with all your blog content. If you don't have latin1 characters (from the standard MySQL default before MySQL version 5.0), change latin1 to the character set your data is in. If you don't have a root user on MySQL/MariaDB you can change "root" for the DB user you want, as long as that user has access to the wordpress database. If your database is not called wordpress change that word in the above line. You will be prompted for the password after executing this command.

Then issue the command: (same provisos apply)

mysql -u root -p --default-character-set=utf8 wordpress < wordpress.sql

Performing the actual conversion on your live database

Once you have all the SQL prepared and have tested each query you need to update your actual live site. This should be done with care as, like any direct manipulation of the database, it is very dangerous and could corrupt your data.

ALWAYS BACK UP YOUR DATABASE FIRST!
Putting it all together

To summarize the steps outlined above, your SQL file should account for the following elements:

If necessary, change the default charset of the database as a whole, e.g.

ALTER DATABASE MyDb CHARACTER SET utf8;

Change the default charset of any tables that are using the old one, e.g.

ALTER TABLE wp_posts CHARACTER SET utf8;

Use the 2 or 3-step process to convert each individual column to the new character set, e.g.

alter table wp_posts change post_content post_content LONGBLOB;
alter table wp_posts change post_content post_content LONGTEXT

and

alter table wp_posts change post_status post_status VARBINARY(20);
alter table wp_posts change post_status post_status VARCHAR(20) character set utf8;
Distinguished Expert 2017

Commented:
try the following portion of the suggested link



mysqldump -u root -p --opt --quote-names --skip-set-charset  wordpress > wordpress.sql
to see wether the output/backup will now be in utf8 as that is the default...

the alter tables work, it just goes through and updates all records on the change, the backup/restore with the changed parameters are often faster and done in one shot..

If you have a workstation on which you can install mysql, and test the backup of live and restore or test the various options, you should consider testing to make sure that no updates in the interim are being done, and to test .......

The reference you posted should work. the backup is important, not setting the character-set as latin1 should store the backup as though the character-set is utf8 or alternatively, you can explicitly set the character-set for the backup as UTF8.

Perform two backups out of caution, one latin1 and one utf8 .....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial