第65回　MySQLと文字コード

みなさんはMySQLの文字化けに悩まされたことはありますか？ MySQLではさまざまな文字コードと照合順序を扱うことができます。今回は、MySQLの文字コードの設定について見ていきたいと思います。

文字コードと設定

MySQLではデフォルトでlatin1という文字コードになっています。この文字コードは日本語を扱うことができないため、少し昔であればujisやsjis、いまでは多くの方が3バイトのutf8や、4バイト文字が扱えるutf8mb4に設定して利用されていると思います。

MySQLで設定できる文字コードは、SHOW CHARACTER SET構文で確認することができます。また、SHOW VARIABLES構文から現在設定されている文字コードを確認することができます。

> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+

> SHOW VARIABLES LIKE 'char%';
+--------------------------+--------------------------------------------------------------------+
| Variable_name            | Value                                                              |
+--------------------------+--------------------------------------------------------------------+
| character_set_client     | utf8mb4                                                            |
| character_set_connection | utf8mb4                                                            |
| character_set_database   | utf8mb4                                                            |
| character_set_filesystem | binary                                                             |
| character_set_results    | utf8mb4                                                            |
| character_set_server     | utf8mb4                                                            |
| character_set_system     | utf8                                                               |
| character_sets_dir       | /data/mysqlbin/mysql-5.7.19-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------------------+
8 rows in set (0.06 sec)

各設定について

character_set_client

character_set_clientは、クライアント側で発行されたSQLをMySQLに送信する際の文字コードとしてセットします。

character_set_database

character_set_databaseは、データベースのデフォルトの文字コードを設定します。バージョン5.7からはオンラインで実行することが非推奨となるようになりました。もし、この値がオンラインで設定された場合は、以下のワーニングが出力されます。

> SET SESSION character_set_database = utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                         |
+---------+------+-------------------------------------------------------------------------------------------------+
| Warning | 1681 | Updating 'character_set_database' is deprecated. It will be made read-only in a future release. |
+---------+------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

character_set_filesystem

ファイルの文字コードを設定できます。LOAD DATA INFILEなどを利用する場合はこちらの文字コードが有効となります。デフォルトはbinaryで扱われます。特別なことがなければbinaryのままで問題ありません。

character_set_results

MySQLからクライアントへ結果セットを返すときの文字コードになります。

character_set_server

MySQLで扱うデフォルトの文字コードです。

特別なことが無い限りは、character_set_clientとcharacter_set_serverに同じ文字コードを設定しておくことをおすすめします。

照合順序

MySQLでは照合順序（collation）を文字コードとは別に設定することができます。照合順序とはMySQLの結果セットを取得した時の並び順のことで、この設定を変更すると、同じSQLでも違う順番になって結果が返ってきます。

各文字コードはデフォルトの照合順序を持っており、設定されていない場合はデフォルトの照合順序を、設定されている場合はその照合順序を利用します。詳細については、MySQLでサポートされる文字セットと照合順序をご確認ください。

skip-character-set-client-handshakeについて

古くからバージョンアップを繰り返し使ってきたMySQLに関しては、skip-character-set-client-handshakeというオプションをつけて起動する必要がある場合があります。

こちらのオプションは、client側で指定した文字コードを無視してMySQLサーバー側で設定されたcharacter_set_serverの文字コードを使用します。MySQL4.0では使用する文字コードはMySQL側でのみ決定されていました。しかし、現在では各接続時の文字コードはそれぞれのシステム変数によって決まるため、MySQL4.0の動作が望ましい場合はこちらのオプションをつけて実行する必要があります。

現在の仕様の詳細については接続文字セットおよび照合順序を御覧ください。

レプリケーションと文字コード

レプリケーションが組まれているマスターとスレーブのcharacter_set_serverの文字コードが違う場合でも、原則マスターの文字コードとしてテーブルやデータは作成されます。これは、マスターがbinary logにクエリを書き出す際に下記のような文字コードのIDを吐き出すためです。

SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=46/*!*/;

これらの文字コードと照合順序の組み合わせは下記クエリで確認できます。

SELECT id, character_set_name, collation_name FROM information_schema.collations;

> SELECT id, character_set_name, collation_name FROM information_schema.collations ORDER BY id LIMIT 3;
+----+--------------------+-----------------+
| id | character_set_name | collation_name  |
+----+--------------------+-----------------+
|  1 | big5               | big5_chinese_ci |
|  2 | latin2             | latin2_czech_cs |
|  3 | dec8               | dec8_swedish_ci |
+----+--------------------+-----------------+
3 rows in set (0.00 sec)

mysqldumpと文字コード

mysqldumpでSQLを取得する際、--default-character-setというオプションをつけることで取得するdumpファイルの文字コードを変更することができます。オプションをつけずにdumpを取得した場合はdefaultのutf8になります。

もし、データベースにいろいろな文字コードを含んでいる場合は、文字コードをbinaryに指定することでbinaryの文字列としてdumpを取得することができます。

まとめ

今回は、文字コードの概要について簡単に説明しました。データベースにおいて文字コードの扱いは慎重になる部分でもあります。MySQLの文字コード関連の設定によってなにが変更されるのか把握し、適切な文字コードを設定していきましょう。