みなさんはMySQLの文字化けに悩まされたことはありますか? MySQLではさまざまな文字コードと照合順序を扱うことができます。今回は、MySQLの文字コードの設定について見ていきたいと思います。
文字コードと設定
MySQLではデフォルトでlatin1という文字コードになっています。この文字コードは日本語を扱うことができないため、少し昔であればujisやsjis、いまでは多くの方が3バイトのutf8や、4バイト文字が扱えるutf8mb4に設定して利用されていると思います。
MySQLで設定できる文字コードは、SHOW CHARACTER SET
構文で確認することができます。また、SHOW VARIABLES
構文から現在設定されている文字コードを確認することができます。
各設定について
character_set_client
character_set_client
は、クライアント側で発行されたSQLをMySQLに送信する際の文字コードとしてセットします。
character_set_database
character_set_database
は、データベースのデフォルトの文字コードを設定します。バージョン5.7からはオンラインで実行することが非推奨となるようになりました。もし、この値がオンラインで設定された場合は、以下のワーニングが出力されます。
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を吐き出すためです。
これらの文字コードと照合順序の組み合わせは下記クエリで確認できます。
mysqldumpと文字コード
mysqldumpでSQLを取得する際、--default-character-set
というオプションをつけることで取得するdumpファイルの文字コードを変更することができます。オプションをつけずにdumpを取得した場合はdefaultのutf8になります。
もし、データベースにいろいろな文字コードを含んでいる場合は、文字コードをbinaryに指定することでbinaryの文字列としてdumpを取得することができます。
まとめ
今回は、文字コードの概要について簡単に説明しました。データベースにおいて文字コードの扱いは慎重になる部分でもあります。MySQLの文字コード関連の設定によってなにが変更されるのか把握し、適切な文字コードを設定していきましょう。