唐突にMySQLの文字コードの話

Author:

taketsuru

2023-10-20

#mysql

はじめに

こんにちは。株式会社 Belong で Data Platform チーム所属の taketsuru です。

たまに Docker 環境で MySQL8 を立ち上げるのですが、MySQL の charset とか collation とか気になりました。

以前は charset のデフォルトが latin1 で文字化けしては my.cnf を編集していたのですが、最近なくなったのでふと思い出したのがきっかけです。

結論から入ると MySQL8 ではデフォルトで選択されているもので問題なかったです。

日常では気になりませんがこういう設定項目もあるよというメモ書きです。

以降、特記ない限り MySQL8.0.32 で確認した内容です。

MySQL における charset 設定箇所

以下の種類があります。

character_set_server
- データの保存
character_set_client
- client 側で使用する charset
- client データの入力
character_set_connection
- client 側から転送されるデータに使用する charset
character_set_results
- クエリ結果の出力
character_set_database
- デフォルト値
- データのファイル保存、クエリ読み出しなどにも使用される
character_set_system
- システム設定
- サーバー側のファイル名などに使用
character_set_filesystem
- サーバー側のログなどに使用

テーブル(カラム)単位で charset を設定することも可能です。

とは言え個別に設定が必要なシーンはほとんどなく、デフォルトの設定で問題ないと思います。

手元で確認したところ、デフォルトの設定は以下の通りでした。

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	utf8mb3
character_sets_dir	/usr/share/mysql-8.0/charsets/

system だけ utf8mb3 になってて気になりますが仕様とのことです。

MySQL における collation 設定箇所

以下の種類があります。

接尾語の意味合いについては charset と同じなので省略します。

collation_connection
collation_database
collation_server

collation もテーブル(カラム)単位に加え検索時にも設定できます。

また、異なる collation のテーブルを join するとエラーになります。

その際は collation を揃えるような追記がクエリに必要です。

手元で確認したところ、デフォルトの設定は以下の通りでした。

SHOW VARIABLES LIKE 'collation%';

Variable_name	Value
collation_connection	utf8mb4_0900_ai_ci
collation_database	utf8mb4_bin
collation_server	utf8mb4_0900_ai_ci

スシビール問題

一時有名になったやつです。

そもそも日本語圏でデータに emoji を使用しない＆検索しない限りは無害なので自分は関わることはありませんでしたが、インパクトのある名称だったので覚えています。

当時は問題が 2 つあって、

当時の MySQL のデフォルトの charset が utf8mb4 でなかった
- デフォルトは utf8(今は utf8mb3 の alias)であり 3byte 文字まで扱える
  - 手元で検証した際はエラーで保存できなかったが保存できる際は 4byte 文字以降は切り捨てられていたらしい
  - なので問題の根本として違う気もするが一応記載しておく
  - 意識して utf8mb4 を設定すれば回避可能
- emoji は 4byte 文字なので正しく保存できない
同じくデフォルトの collation が utf8mb4_general_ci であった
- これは emoji を区別できない collation

MySQL8.0.1 以降ではデフォルトの charset が utf8mb4 になり emoji を保存できます。

同様にデフォルトの collation も utf8mb4_0900_ai_ci という emoji を区別できるものになります。

collation による再現

あなたは友人 DB を持っています。

CREATE TABLE friends (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name TEXT,
    description TEXT
) COLLATE utf8mb4_general_ci;
INSERT INTO friends (name, description) VALUES ('ito', '🍣が好き'), ('tanaka', '🍺が好き');

🍺 が飲みたくなったので 🍺 が好きな友人を探したいと思います。

SELECT name FROM friends WHERE description LIKE '%🍺%';

name
ito
tanaka

すると ito と tanaka がヒットしました。

しかし ito は 🍺 が好きとは言っていないと返事が来ました。

勘の鋭い ito は collation が utf8mb4_general_ci であることを疑い、クエリに COLLATE utf8mb4_bin を付与するよう提案してきました。

SELECT name FROM friends WHERE description LIKE '%🍺%' COLLATE utf8mb4_bin;

name
tanaka

これで tanaka だけがヒットし、ito は 🍣 を食べに行くことができました。

charset による再現

こちらは再現できませんでした。

4byte 文字を保存しようとするとエラーになります。

CREATE TABLE friends_mb3 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name TEXT,
    description TEXT
) CHARSET utf8mb3;
INSERT INTO friends_mb3 (name, description) VALUES ('ito', '🍣が好き'), ('tanaka', '🍺が好き');

-> Error Code: 1366. Incorrect string value: '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'description' at row 1

collation の種類と使い分け

MySQL8 ではデフォルトで utf8mb4_0900_ai_ci が選択されています。

ユースケース次第ではありますが、自分は utf8mb4_bin が直感的かなと思います。

utf8mb4_0900_ai_ci は大文字小文字や全角半角を同一視しますが、utf8mb4_bin は区別します。

また、collation による比較の内部挙動は、

collating weight を持っていれば使用
- 文字を比較用の byte 表現に変換したもの
- collation のポリシー(大文字小文字同一視か区別か、など)によって異なる
- 参考
持っていなければ collation ごとの独自計算を使用
この時一部の collation において emoji は全て 0xfffd に変換される

という流れのようです。参考

collation の挙動比較はこちらがわかりやすいです。

collation によって比較速度の差はあるようなのですが、それよりも比較挙動の違いの方が選択理由として大事なように思います。

完全に区別したい場合は utf8mb4_bin を使用し、絵文字以外は曖昧でいいなら utf8mb4_0900_ai_ci などでしょうか。

まとめ

MySQL の文字コード設定について調べてみました。

設定項目の多さと細かさに驚きました。と同時に使い分ける場面があるのかと疑問に思いました。

後方互換性のために残されているのかもしれません。

まずないと思いますが、5byte 文字が出てきたり革命的な collation が出てきたら再考する必要があるかもしれません。

逆にそれまではデフォルトでよさそうという結論も得られました。

弊社 Belong では一緒にサービスを育てる仲間を募集しています。もし弊社に興味を持っていただけたら https://entrancebook.belonginc.dev/ をご覧いただけたら幸いです。