MySql set default character set to UTF-8 (utf8_unicode_ci)

Dabian 6; As default mysql is installed with character-set latin1
 

utf8, a UTF-8 encoding of the Unicode character set using one to three bytes per character.
utf8mb4, a UTF-8 encoding of the Unicode character set using one to four bytes per character. (utf8mb4 from MySQL version 5.5.3)

utf8: utf8_unicode_ci is more accurate for all scripts but is a little bit slower than utf8_general_ci

You can specify character settings per database. To create a database such that its tables will use a given default character set and collation for data storage, use a CREATE DATABASE statement like this:

CREATE DATABASE your_database
...
DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;


NOTE; Be sure to make backup from all databases on the server, before you make any changes.

You can change character set from your database and table with ALTER command

ALTER DATABASE your_database CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE database_tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;


To set the default character-set UTF-8 on your mysql server, add the following lines to my.cnf
default-character-set=utf8
 

login in mysql shell

mysql  -p -u root

type password and then type

mysql> show variables like 'char%';



Check also your collation set

mysql> show variables like 'collation%';




now edit your my.cnf file

pico /etc/mysql/my.cnf

and add or change

[client]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init-connect='SET NAMES utf8'
collation-server = utf8_unicode_ci
character-set-server = utf8

[mysql]
default-character-set=utf8

close mysql shell and then restart mysql

/etc/init.d/mysql restart

type again

mysql> show variables like 'char%';



now is everything set to utf8

Check again your collation set, to fix eventuel conflict with mix of collations 
and type again

mysql> show variables like 'collation%';



edit now again your my.cnf file

pico /etc/mysql/my.cnf


and add one extra line under [mysqld]

skip-character-set-client-handshake


this will change the collation_connection utf8_general_ci entry to utf8_unicode_ci
now in total your new lines will looks as

[client]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init-connect='SET NAMES utf8'
collation-server = utf8_unicode_ci
character-set-server = utf8
skip-character-set-client-handshake

[mysql]
default-character-set=utf8


close mysql shell and then restart mysql

/etc/init.d/mysql restart


one last time type

mysql> show variables like 'collation%';


 



Links:
Unicode Character Sets - mysql.com
The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding) - mysql.com
Collation Charts - unicode.org