Read more

Show and change MySQL default character set

Kim Klotz
October 24, 2011Software engineer at makandra GmbH

To show the MySQL default character set you have to login to the MySQL console and execute SHOW VARIABLES LIKE 'char%';

mysql> SHOW VARIABLES LIKE  'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | latin1                     |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
Illustration book lover

Growing Rails Applications in Practice

Check out our e-book. Learn to structure large Ruby on Rails codebases with the tools you already know and love.

  • Introduce design conventions for controllers and user-facing models
  • Create a system for growth
  • Build applications to last
Read more Show archive.org snapshot

If you want to change it for example to uft8 you have to add this

default-character-set = utf8

to the client, mysqld_safe, mysqld and mysqldump section (may differ depending on configuration) in your my.cnf and restart the mysql daemon.

Note: For MySQL > 5.6 default-character-set is not valid and you need to use character-set-server instead. Check you error log if mysql start leads to a timeout.

After this check if the default character set changed

mysql> SHOW VARIABLES LIKE  'char%';
+--------------------------+----------------------------+
| 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.01 sec)

It should all changed to utf8 or you have forgotten something.

You can check the default collation with SHOW CHARACTER SET WHERE CHARSET = 'utf8';

mysql> SHOW CHARACTER SET WHERE CHARSET = 'utf8';
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
+----------+-----------------------------+---------------------+--------+
36 rows in set (0.00 sec)

If your character set is utf8 your default collation is utf8_general_ci.

Posted by Kim Klotz to makandra dev (2011-10-24 17:28)