Changing character set and collation in MySQL.

8:41 PM

MySQL allows configuring character set for the following:
mysql> show variables like "%character%";
+------------------------+--------------------------+
| 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/ |
+------------------------+--------------------------+
Also for the collation to sort the text:
mysql> show variables like "%collation%";
+--------------------+-------------+
| Variable_name        | Value           |
+--------------------+-------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+--------------------+-------------+
Requirement for character set change:
1) If we want existing data to be converted to utf8 from say Latin1, we need to backup the database into a dump file. The dump file will have create database and create table statements using character set (for eg: Latin1), replace the Latin1 to utf8 in the dump file and import the file back.
So configuring the server by changing character set and collation to utf8 and converting existing data to utf8 should make server utf8 ready. But we also need to test the application just in case it is using other libraries which then also need to be utf8 compiled.
2) If we do not want existing data converted but need server configured for utf8 and new tables to use utf8, we can configure the character set variables and use using character set utf8 in the create table command and set names utf8 in the insert statements. But we should take a good full backup before we do that just in case any garbled data gets stored.

In both of the requirements above the implementation should be done in a maintenance window.

You Might Also Like

0 comments

Contact Form

Name

Email *

Message *

Translate

Wikipedia

Search results