Home > MySQL > MySQL convert to utf8

MySQL convert to utf8

A quick way to convert your database to utf8

# Dump the old database as latin1, mysqldump defaults to utf8
mysqldump -h host -u user -p --default-character-set=latin1 old_db > dump.sql

# Rewrite the dump to say 'utf8' and 'utf8_general_ci' 
sed -e 's/SET NAMES latin1/SET NAMES utf8/g' -i dump.sql
sed -e 's/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/g' -i dump.sql
sed -e 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/g' -i dump.sql

# Make sure your new database has the correct character set and collationĀ ( i.e utf8 )
# Import the converted database dump into MySQL.
mysql -h host -u user -p --default-character-set=utf8 new_db < dump.sql

If you want to convert only a certain column – first convert the column to binary and then convert to utf8

update table set column = CONVERT( (CAST(column) AS BINARY ) USING utf8 );

It is also good to make everything to utf8 in your my.ini file

init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'

Categories: MySQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: