Mysql change default character set

From Wiki
Jump to: navigation, search

Changing the default character set for mysql requires a few changes in /etc/my.cnf. Add these lines to the "mysqld" section and then restart mysql.

collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

To verify run the following query inside of mysql.

show variables like 'char%';

This should produce output similar to the following.

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

This setting will *not* change encoding for databases or tables that have already been created. To change these use the following script.

DB="dbname"
(
    echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'
    mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names \
    | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;'
) \
| mysql "$DB"