2024.10.07
MySQLの文字コード変換処理を追いかけてみよう – 文字コード入門〜実装まで
こんにちは，S.T.です。MySQLの文字コード周りの紹介記事です。MySQLの実装や，ちょっと変わった化け方を解説しています。実際に何か問題が起きていて，理由を調べている方が検索から辿り着いた場合は，まずは「5.文字コードを間違えたときの挙動」を見るのが良いと思います。
1.符号化文字集合と符号化方式
MySQLの話をする前に，文字コードの話をしましょう。
多くの人がコンピュータで「文字」を扱う際に意識するのは「文字コード」です。この記事を読んでいる方の多くは「UTF-8」や「Shift-JIS」というキーワードと，ソフトウェアでそれらを取り違えるとうまく表示できない，ということをご存知でしょう。
このことからもわかる通り，文字コードの正体は「文字をコンピュータの内部で扱うために0/1で表現したもの」というわけですが，文字コードの裏側には「符号化文字集合」と「符号化方式」という2つの概念が存在します。
符号化文字集合（CCS：Coded Character Set）とは，文字の集合を定めて，その中の文字とビット列の対応を決める規則です。UnicodeやJIS X0208などが，符号化文字集合です。
例えば，Unicodeは文字の集合として，アルファベットや日本語，韓国語，アラビア語，記号などを定め，「あ」という文字には「0x3042」というコードを割り当てる，というような規則を決めています。
一方で，符号化方式（CES：Character Encoding Scheme）とは，符号化文字集合で決めたビット列を，コンピュータで利用しやすいデータに変換するための規則です。UTF-8やShift-JISなどが符号化方式にあたります。現在，「文字コード」や「Charset」と言われるのはこちらを指すことが多いです。例えば，UTF-8は符号化文字集合であるUnicodeに対して1〜4バイトの符号を対応付けています。また，Shift-JISはJIS X 0201とJIS X 0208に対して1〜2バイトの符号を対応付けています。
つまり，誤解を恐れずに言ってしまうと，符号化文字集合は言語や目的に応じて決められた文字の一覧表で，符号化方式は1つ以上の一覧表を組み合わせて，各行に一意のIDを振ったもの，というイメージです。符号化方式は，一般的に符号化文字集合で割り当てられた符号に対してシフト等のビット演算を行ったり，プレフィックスを付与したりすることにより，符号化を行います。
ところで，符号化文字集合を直接使って文字を表現しないのは，複数の符号化文字集合の間で重複があるためです。例えば，JIS X 0201は1バイトでASCII文字を表し，JIS X 0208は2バイトで日本語を表現しますが，JIS X 0208の第一バイトはJIS X 0201に含まれるコードと重複しています。符号化文字集合のみを使うと，このような問題により多数の文字を表現することができません。
また，Unicodeは単一の符号化文字集合で多くの文字を表現できるものの，そのまま利用するにはデータ長の点で不利であり，効率的に表現するためにも符号化方式が必要です。
2.MySQLと文字コード
MySQLは日本語をはじめとする様々な言語を含むデータを扱うことができますが，これはMySQLがそれらの文字を含む符号化方式に対応しているためです（MySQLではこちらをキャラクタセットと呼びます）。MySQLが対応しているキャラクタセットは，「SHOW CHARACTER SET」というステートメントを実行することで確認が可能です。
mysql> SHOW CHARACTER SET; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | binary | Binary pseudo charset | binary | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | cp866 | DOS Russian | cp866_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.03 sec)
このように，様々なキャラクタセットに対応していますが，古いバージョンのMySQLはデフォルトがlatin1になっており，そのままでは日本語を扱うことはできません。そのため，私たちがMySQLを使用する際は，utf8mb4などを利用することがほとんどでしょう（現在ではデフォルト）。といっても，これから新規に使う場合は，特に理由がなければutf8mb4を選ぶのが無難でしょう。
MySQLでは，テーブルやカラムごとに使用するキャラクタセットを設定することができます。例えば，以下のtestテーブルはutf8mb4が設定されており，messageカラムのデータはutf8mb4になります。
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int NOT NULL,
`cnt` int DEFAULT NULL,
`message` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.02 sec)
また，テーブル上のデータだけでなく，クライアントとの通信やクエリの処理に用いるキャラクタセットを指定することも可能です。この設定は「SHOW VARIABLES like ‘%character_set%’」というステートメントで確認可能です。
mysql> SHOW VARIABLES like '%character_set%'; +--------------------------+--------------------------------+ | 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 | utf8mb3 | | character_sets_dir | /usr/share/mysql-8.0/charsets/ | +--------------------------+--------------------------------+ 8 rows in set (0.02 sec)
character_set_clientは，クライアントがデータを送ってくるキャラクタセットを指定する変数です。例えば上記ではutf8mb4になっています。つまり，このMySQLは接続してくるアプリケーショが文字列をutf8mb4で送信してくる前提で処理を行います。
character_set_connectionは，クエリの処理を行うキャラクタセットを指定する変数です。MySQLはクライアントから受信したデータをcharacter_set_clientからcharacter_set_connectionに変換します。その上で，テーブルにアクセスする場合はそのテーブルやカラムのキャラクタセットに応じて適宜変換しながら処理を行います。
つまり，MySQLのテーブルに格納されているデータと，アプリケーションが使用しているキャラクタセットが異なっていても，character_set_clientを正しく設定しておけば，MySQLが内部で変換して処理を行ってくれるわけです。
3.3.MySQL上での文字コード変換
MySQLはクライアントとの通信とテーブル上のデータのキャラクタセットが異なっても，適宜変換して処理を行ってくれるのでした。まずは，実際にこの挙動を見てみましょう。
今回は，以下のようなJavaScriptのコードを作成し，「UTF-8で保存」しました。処理の内容は単純で，Localに建てたDBに接続した後，character_set_client変数を設定し，SELECTを実行するのみです。
const mysql = require("mysql2");
const db = mysql.createConnection({
user: "root",
host: "localhost",
password: "password",
database: "test",
port: 3306,
multipleStatements: true,
});
const query = "SET character_set_client='utf8mb4'; SELECT COUNT(1) FROM test WHERE message='あ'";
db.query(query, (err, result) => {
console.log(result[1]);
db.close();
});
先ほど出てきたtestテーブルに，message=’あ’ というレコードを1行INSERTした状態でこのコードを実行すると，以下のようにCOUNTの結果は1件となります。
% node check_mysql_char.js
[ { 'COUNT(1)': 1 } ]
では，次にqueryを以下のように変更し，Shift-JISとしてSELECTを実行してみます。
const query = "SET character_set_client='sjis'; SELECT COUNT(1) FROM test WHERE message='あ'";
結果は以下の通りです。
% node check_mysql_char.js
[ { 'COUNT(1)': 0 } ]
このスクリプトはUTF-8で作成されているため，SELECT文に含まれる「あ」はUTF-8で符号化したバイト列で送信されます。MySQLは，character_set_clientがSJISなので，送られてきたデータをShift-JISとして解釈をして，utf8mb4へ変換を試みます。
アルファベットの部分はUTF-8もShift-JISも同じコードが利用されていますので，問題なく変換できます。しかし，「あ」はShift-JISでは「0x82a0」，UTF-8では「0xe38182」です。MySQLは受信したデータが正しいShift-JISであると仮定してUTF-8に変換します。つまり，データ中に存在する「0xe38182」というバイナリを無理やりUTF-8に変換することになります。当然，変換結果は「あ」にならず，COUNTの条件に引っかからないため，件数が0件になります。
この挙動を，mysql-clientでも再現してみましょう。MySQLはCASTによりキャラクタセットを変換することができます。そのままでは，MySQLが気を利かせて再度character_set_clientに合わせて変換してしまいますので，BINARYでバイナリ文字列にキャストして確認しましょう。
まず，utf8mb4で’あ’を確認してみます。
mysql> select binary 'あ'; +----------------------------+ | binary 'あ' | +----------------------------+ | 0xE38182 | +----------------------------+ 1 row in set (0.01 sec)
UTF-8の’あ’である0xe38182が得られました。
次に，SJISにキャストしてみます。
mysql> select binary cast('あ' as char character set sjis) as result;
+----------------+
| result |
+----------------+
| 0x82A0 |
+----------------+
1 row in set (0.01 sec)
こちらも正しく0x82a0を得ることができました。
では，少し捻って，先ほどの「0xe38182」をShift-JISと解釈してUTF-8に変換してしまった場合にどのような値になるかを見てみましょう。
mysql> select binary cast(cast(0xe38182 as char character set sjis) as char character set utf8mb4) as result; +----------------+ | result | +----------------+ | 0xE7B8BA | +----------------+ 1 row in set (0.00 sec)
‘あ’ではない別のコードに変換されてしまいました。
このように，MySQLはキャラクタセットを変換する機能を持っており，設定や明示的なキャストにより変換してくれます。
4.実装を見てみる
アプリケーションで符号化方式の設定を間違えて，全く読めない文字に化けて表示された，という経験があるかと思います。このように，符号化方式ごとに文字に割り当てられるコードが大きく異なります。MySQLは，これらのコードをどのように変換しているのか，実装を見てみましょう。
まずはドキュメントを確認します。8.0日本語ドキュメントでは，「10.13.3 複雑な文字セットのマルチバイト文字のサポート」が対応します。このページを要約すると，「コードを読め」です。幸い，読むべき場所がなんとなく記載されていますので，GitHubでソースツリーを参照してみます。まずは，mysql-server/string/CHARSET_INFO.txtを見てみましょう。
CHARSET_INFO.txtは，キャラクタセット用の構造体に含まれる変数や関数を解説しています。stringsディレクトリには，このCHARSET_INFO.txtに記載されている仕様に従って実装された，様々なキャラクタセットの実装が含まれています。CHARSET_INFO.txtを読み進めると，ソートや比較等，面白そうなものがたくさんありますが，今回注目するのは，「Unicode conversion routines」に記載されている「mb_wc」と「wc_mb」の2つの関数です。これらは，それぞれマルチバイト対応のキャラクタセットで用いられる「元のコードからUnicodeへ変換する関数」と「Unicodeから元の文字コードに変換する関数」です。
逆に各キャラクタセットを直接相互に変換するための関数はありません。つまり，どのキャラクタセットも一度Unicodeに変換して，Unicodeから変換対象のキャラクタセットに変換する，というフローを経ることになります。
例として，UTF-8系のキャラクタセットを担当しているctype-utf8.ccを見てみましょう。my_charset_utf8mb4_handlerでmb_wc・wc_mbの関数ポインタを格納しています。mb_wcはmy_mb_wc_utf8mb4_thunkという関数ですが，この内部で呼び出されるものを辿っていくとmb_wc.hのmy_mb_wc_utf8_prototypeに処理の中身を見つけることができます。
この処理の詳細の解説は省略しますが，コードを読み込むと実装はunicodeとUTF-8を変換するものであることがわかります（参考：とほほの文字コード入門）。このように，MySQL内部はキャラクタセットをUnicodeに変換する処理と，Unicodeをキャラクタセットに変換する処理を持っていて，キャラクタセット間の変換を実現していることがわかりました。
5.文字コードを間違えたときの挙動
MySQLで設定と異なるキャラクタセットでデータを送ってしまった場合の挙動を見てみましょう。
3章の最後に取り上げた，「0xe38182」をShift-JISと解釈してUTF-8に変換してしまった場合の例では，以下のように’あ’ではない別のコードに変換されてしまいました。
mysql> select binary cast(cast(0xe38182 as char character set sjis) as char character set utf8mb4) as result; +----------------+ | result | +----------------+ | 0xE7B8BA | +----------------+ 1 row in set (0.00 sec)
もし，これが単純なSELECTではなくテーブルへのINSERTで，重要なデータだった場合，どうなるでしょうか。逆方向にキャストして同じデータを得ることができれば，アプリケーションから復元することができそうです。確認してみましょう。
mysql> select binary cast(cast(cast(0xe38182 as char character set sjis) as char character set utf8mb4) as char character set sjis) as result; +----------------+ | result | +----------------+ | 0xE381 | +----------------+ 1 row in set (0.01 sec) -- binaryやめてみる mysql> select cast(cast(cast(0xe38182 as char character set sjis) as char character set utf8mb4) as char character set sjis) as result; +--------+ | result | +--------+ | 縺 | +--------+ 1 row in set (0.00 sec)
残念ながら，このケースでは元のデータに戻りませんでした。このような間違いをしてしまうと，正しいデータを復元するのに苦労しそうです。
ただ，この例はutf8mb4とsjisの間違いなので，格納されたデータを表示すれば，すぐにおかしいことに気が付きます。アプリケーションであれば，開発中に気がついて，本番環境で問題が起きる可能性は低いでしょう。
もう1つ，ありそうなケースを考えてみます。
MySQLはEUC-JP系のキャラクタセットとしてujisとeucjpmsの2つがあります。これらのキャラクタセットは，それぞれ一般に広く使われている符号化方式であるEUC-JPとeucJP-MSに対応しています。これらは，eucJP-MSはEUC-JPを拡張した亜種で，採用している符号化文字集合が異なりますが，アルファベットやひらがな・かたかな，一般的な漢字までの符号が一致しています。そのため，取り違えても，ほとんどの文字を正しく表示できます。
しかし，外字（はしご高，立つ崎など）を含む符号化文字集合への対応が異なります。eucJP-MSでは，外字を利用することができますが，EUC-JPでは利用できません。このような違いがありますが，正しく使っていれば，もちろん問題はありません。
厄介なのは，EUC-JPにはマイクロソフトが拡張したCP51932という亜種が存在し，こちらも広く使われている，という点です（しかも名前が紛らわしい）。CP51932でも外字を使うことが可能ですが，eucJP-MSの外字とコードが異なります。例えば，「立つ崎」はeucJP-MSでは「0x8ff4bd」であり，CP51932では「0xf9f5」です。
そして，環境やプログラミング言語，ライブラリによっては，EUC-JPの実体がCP51932であり，eucJP-MSには対応していない，ということがあります。このような環境で，もし「MySQLとの通信はEUC-JPで行う（本当はeucjpmsの意図）」という仕様を作成してソフトウェアを開発すると，外字がCP51932のコードになってしまう（しかも外字以外は普通なので気が付きづらい），という状況が発生します。
このとき，MySQL上ではどのようなデータが記録されて，mysql-clientからどのように見えるのか，確認してみましょう。まずは0xf9f5（CP51932のコード）をeucjpmsとして表示してみます。
mysql> select cast(0xf9f5 as char character set eucjpms) as result; +--------+ | result | +--------+ | | +--------+ 1 row in set (0.01 sec)
このように，[？]となってしまい，表示できませんでした。しかし，表示はできなくても，このデータをINSERTするケースでは，テーブルの中のデータには0xf9f5というバイナリが格納されます。そのため，このデータをアプリケーションから参照するときは，CP51932のコードとして正しく解釈されます。不整合なデータとはなっているものの，大問題にはならなさそうです（あくまでも，この文字の場合です）。
では，別の機能のためにutf8mb4のテーブルが作成され，アプリケーションからそのテーブルにINSERTすることになった場合はどうでしょうか。0xf9f5をeucjpmsとして解釈したあと，utf8mb4にキャストしてみます。
mysql> select binary cast(cast(0xf9f5 as char character set eucjpms) as char character set utf8mb4) as result; +----------------+ | result | +----------------+ | 0xEE878C | +----------------+ 1 row in set (0.00 sec) mysql> select cast(cast(0xf9f5 as char character set eucjpms) as char character set utf8mb4) as result; +--------+ | result | +--------+ | | +--------+ 1 row in set (0.00 sec)
表示できない文字になりました。しかし，この0xee878cを，再度eucjpmsに戻して0xf9f5に戻れば，アプリケーションからは「立つ崎」であると判断できます。確認してみます。
mysql> select binary cast(cast(cast(0xf9f5 as char character set eucjpms) as char character set utf8mb4) as char character set eucjpms) as result; +----------------+ | result | +----------------+ | 0xF9F5 | +----------------+ 1 row in set (0.01 sec)
0xf9f5に戻りましたね。このように，eucJP-msとCP51932を取り違えてしまっても，外字の情報は残るため，アプリケーションからは問題なく参照できます。もちろん，この変換を積極的に使う設計は避けるべきですが，調査やリカバリの際には役立つ知識でしょう。
最後に，MySQLの実装を確認して，本来eucJP-msに存在しないコードであるはずの0xf9f5を，utf8mb4に変換しても正しく元に戻せる理由を見てみましょう。確認すべき内容は，「0xf9f5をeucjpmsとして解釈してunicodeに変換したらどのような値になるのか」と，「そのunicodeの値をutf8mb4に変換したらどのような値になるのか」です。これらが明確になれば，utf8mb4に変換しても元のデータが0xf9f5という情報を保てることを確認できます。
eucjpmsに関わる処理はctype-eucjpms.ccに実装されています。unicodeに変換する処理の本体はmy_mb_wc_eucjpms関数です。0xf9f5は以下の分岐で処理されます（注：hiは第一バイトの0xf9，ビッグエンディアンであることに注意）。
if (hi >= 0xA1 && hi <= 0xFE) /* JIS X 0208 code set: [A1..FE][A1..FE] */ { if (s + 2 > e) return MY_CS_TOOSMALL2;
return (*pwc = jisx0208_eucjpms_to_unicode[(hi << 8) + s[1]])
? 2
: (s[1] < 0xA1 || s[1] > 0xFE) ? MY_CS_ILSEQ : -2;
}
jisx0208_eucjpms_to_unicodeは，uint16_tの1次元配列であり，unicodeへの変換テーブルになっています。[(hi << 8) + s[1]]のIndex計算を実際に行うと，0xf9f5になります。実際にjisx0208_eucjpms_to_unicodeの0xf9f5番目の要素を見ると，0xe1ccになっています。つまり，0xf9f5はMySQLではunicodeの0xe1ccに割り当てられる，ということがわかりました。
0xe1ccをunicodeからUTF-8に変換します。元のビット列を「yyyyyyyy xxxxxxxx」とすると，UTF-8では「1110yyyy 10yyyyxx 10xxxxxx」の3バイトで表現できます（参考：とほほの文字コード入門）。このルールで変換すると，「0xee878c」が得られます。これで，0xf9f5をunicode介してUTF-8に変換すると0xee878cが得られ，逆を辿ると0xf9f5に復元できる，ということが確かめられました。
unicodeでは，0xe000～f8ffが私用領域となっており，文字が割り当てられていません。この領域を活用することで，立つ崎のような「外字」を扱う際に発生する問題の影響を減らすような工夫がなされているのでした。DBMSによっては，この変換はエラーとして処理されることもあるため，注意が必要でしょう。
6.まとめ
MySQLと文字コードについて，実際の変換例を交えて紹介しました。MySQLは，内部でunicodeを介してキャラクタセットの変換を行なっています。キャラクタセットや外字はその複雑さから問題が生じやすいですが，unicodeの私用領域を活用して問題が起きても影響が小さくなるように工夫されているのでした。
次世代システム研究室では，グループ全体のインテグレーションを支援してくれるアーキテクトを募集しています。アプリケーション開発者の方，次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら，ぜひ募集職種一覧からご応募をお願いします。
参考
MySQL – 10.13.3 複雑な文字セットのマルチバイト文字のサポート
Unicode Private Use Area（PDF）
とほほの文字コード入門
tmtmsのメモ – MySQLの文字コード事情
グループ研究開発本部の最新情報をTwitterで配信中です。ぜひフォローください。
Follow @GMO_RD
