Mysql – How to convert thesql database charset to utf-8

Yes, you can set the default character set and collation on various levels.

First, in your question, on the database level:

CREATE DATABASE IF NOT EXISTS foo 
 DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'

or

ALTER DATABASE foo
 DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'

All new tables in that database should be created with the new character set and database if it's not specified in the table creation command (if it is specified in table command, the specification will override the database default).

You can set the default at a server level (so that all tables are created with the default for all databases unless explicitly stated otherwise) by providing the --character-set-server and --collation-server options in your my.cnf and restarting the server.

And finally, verify the client connection settings. This doc explains how the client connection can fine-tune (and override) the default settings.

You need to incorporate the following into your my.cnf file

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8


[mysqld]
default-character-set = utf8    
collation-server = utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

You can find more information on the following manual pages

• http://dev.mysql.com/doc/refman/5.6/en/charset-configuration.html

• http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_character_set_system

• http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_character_set_server

In response to Mark's answer, if you need to convert existing tables/databases to the correct character set you will need a suitable ALTER query. Something similar to the below:

ALTER TABLE `table` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Alternatively there's a great PHP script here which does it for you.