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
Previous page: Mysql
Next page: Convert a MySQL database to UTF-8