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 Tags:
  1. No comments yet.
  1. No trackbacks yet.