(MySQL) 文字コードと照合順序を理解する

本記事では、MySQLの文字コードと照合順序について解説をしていく。

MySQLを触ると、SQLを実行する上で特に問題は無いものの、utf8_general_ciとか訳の分からない設定値が存在していて、これって何だろう？と思った初見の方が結構多いのではないのかなと思い執筆に至った。

照合順序(collation)とは

MySQLの照合順序(collation)とは、
データベース内のデータの文字同士を比較するときのルールのことだ。

例えば、「aとAを区別する？」と言った具合に、文字の比較の際のルールが、MySQLには複数存在する。この文字の比較のルールのことを照合順序と読んでいるのである。

照合順序は「utf8_general_ci」のような形で表され、
意味は「文字コード_言語名_比較方法」となる（※詳細は後述）。

MySQLの照合順序(collation)とは

データベース内のデータの文字同士を比較するときのルールのこと。

意味は「文字コード_言語名_比較方法」（※詳細は後述）。

では次に、①文字コード、②言語名、③比較方法について、それぞれがどんなものかを解説していく。

①文字コード

utf8mb4 とか utf8 とか cp932 とかのこと。
比較するときにはこの文字コードも見て判定している。

②言語名

japanese とか chineseとかgeneralとかのこと。
generalやunicodeはマルチリンガルの事(utf8にjapaneseはない)。

③比較方法

MySQLの照合順序を理解する上で最も覚えておきたいことは、この③比較方法だ。

理由は、①②は種類を指定しているだけなので見ればなんとなく分かるが、この③比較方法に関しては、記述内容の意味を理解しておく必要があるからだ。

比較方法とは何かと言うと、簡単に言うと、冒頭でも説明した通り、
「aとAを区別する？」と言った具合に、文字の比較の際のルールを決めている部分のことだ。

下記は比較方法の種類と、その意味の対応表。

_ci	大文字小文字を区別しない	Case Insensitive
_cs	大文字小文字を区別する	Case Sensitive
_bin	バイナリ比較	binary

ちなみに日本語のWebサイトを作る場合は、 utf8_general_ci にしておくのが無難な様子。

おまけ：照合順序の確認方法

指定できる照合順序の一覧が知りたい場合は下記クエリで一覧が確認可能

show collation;

LIKE句を使うことで、特定の照合順序一覧を見ることも可能

show collation like 'utf8mb4%';

MySQLにおける文字コードの取り扱い

もう一点合わせて覚えておきたい点として、
MySQLでは、クライアント側文字コードとサーバー側文字コードを別々に設定すると言う点があげられる。

以下はそれぞれの意味の対応表だ。

クライアント側文字コード	クライアントがデータの送受信に使用する文字コード
サーバー側文字コード	文字データを内部的に表現するための文字コード

クライアント側、サーバ側で異なる文字コードを指定している場合は、
MySQLの機能により文字コードの変換処理が行われる。

現在の文字コードの設定を確認

文字コードの設定は、英語では「character_set」になる。
現在の文字コードの設定は下記のクエリで確認できる

#確認するDBを選択
use db_example;

#文字コード設定を出力
show variables like "chara%";

+--------------------------+------------------------------
| 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/local/mysql/charsets/  |
+--------------------------+-----------------------------+

各文字コード設定の分類と役割

上記８つの文字コード設定(character_set~)のうち、次の６つが、
クライアント側とサーバ側それぞれの文字コードの設定になる

＜クライアント側文字コード設定＞

character_set_client
character_set_connection
character_set_results

＜サーバ側文字コード設定＞

character_set_database
character_set_server
character_set_system

文字コード設定 :	説明
character_set_client :	クライアント側で発行したsql文はこの文字コードになる
character_set_connection :	クライアントから受け取った文字をこの文字コードへ変換する
character_set_database :	現在参照しているDBの文字コード
character_set_results :	クライアントへ送信する検索結果はこの文字コードになる
character_set_server :	DB作成時のデフォルトの文字コード
character_set_system :	システムの使用する文字セットで常にutf8が使用されている
character_sets_dir :	キャラクタセットを扱う上で必須となるファイルを配置しているディレクトリ