Show and change MySQL default character set

Updated . Posted . Visible to the public.

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)

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.

Kim Klotz
Last edit
Emanuel
License
Source code in this card is licensed under the MIT License.
Posted by Kim Klotz to makandra dev (2011-10-24 15:28)