【MySQL】文字コード/照合順序の基礎

はじめに

業務でデータベースやテーブルを作成するときに、文字コードについてあまり
意識できておらずちょくちょくハマることがあるので整理したいと思います。
本記事で前提とする MySQL のバージョンは8.0.32です。

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.32    |
+-----------+

文字コード

文字コードとは符号化文字集合と符号化方式のセットのことを指す。
※本記事では符号化文字集合を文字集合と記載します。

文字集合(Character set)

文字や記号に一意な番号(コードポイント)を割り当てたもので、
具体的には以下のような文字集合があります。

文字集合の例

文字集合	説明
ASCII	英字、数字、記号を含む128文字
JIS X 0208	漢字、ひらがな、カタカナなどの日本語、数字、 記号、ギリシャ文字、キリル文字、ラテン文字等
JIS X 0213	JIS X 0208 の拡張として漢字第3水準/第4水準、 ASCII 互換のための文字、鼻濁音表記等を追加
Unicode	世界中の文字を1つの文字集合で利用できるようにするという 思想のもと開発され、漢字、ひらがな、カタカナ、ラテン文字、 ギリシャ文字、アラビア文字、ハングル、カンナダ文字、 絵文字(🍣🍻)など世界中の文字や記号が含まれる

符号化方式(Encoding)

文字集合をコンピュータで扱うバイト列に変換するルールのことで、
具体的には以下のような符号化方式があります。

符号化方式の例

符号化方式	文字集合	利用例
EUC-JP	ASCII, JIS X 0208, JIS X 0201	UNIX系OS
ISO-2022-JP	ASCII, JIS X 0201(ラテン文字), JIS X 0208	メール
Sfhift_JIS	JIS X 0201, JIS X 0208	Windows
UTF-32	Unicode	自然言語処理等に使われることがあるが一般的にほとんど使われない
UTF-16	Unicode	Java
UTF-8	Unicode	Webページやプログラミング言語において一般的に広く使用

使用可能な文字コードの確認方法

MySQLで使用可能な照合順序を確認するには、以下の方法があります。

1. SHOW CHARACTER SET ステートメントを使用する
2. INFORMATION_SCHEMA.CHARACTER_SETS テーブルを参照する

以下では 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 |
| ...      | ...                             | ...                 |    ... |
| 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 |
| utf8mb3  | UTF-8 Unicode                   | utf8mb3_general_ci  |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+

照合順序

照合順序とは文字を比較する際のルールです。

これだけでは何のことか分かりづらいと思うので、
MySQL ドキュメントの例を以下に示します。

文字セットとは、記号とエンコーディングのセットです。 照合順序とは、文字セット内の文字を比較するためのルールを集めたものです。 架空の文字セットを例にして、文字セットと照合順序の違いを見てみましょう。

4 文字のアルファベットがあるとします: A, B, a, b。 各文字に数字を付けます: A = 0, B = 1, a = 2, b = 3。 文字 A は記号、数字 0 は A 用の encoding で、4 文字すべてとそのエンコーディングの組合せは文字セットです。

A と B の 2 つの文字列値を比較するとします。 これを行う最も簡単な方法は、エンコーディングを確認することです: 0 (A の場合)、1 (B の場合)。 0 は 1 より小さいため、A は B より小さいと言います。 今ここで行なったのは、文字セットに対する照合順序の適用です。 照合順序はルールの集まりであり、この場合、ルールは「エンコーディングの比較」の 1 つだけになります。 これは可能な照合順序のうちでもっとも単純なものであり、バイナリ照合順序と呼ばれています。

先に記載した用語と若干異なっていますが、
以下のような理解でよいかと思います。

• 文字セット = 文字コード
• 記号 = 文字集合
• encoding = 符号化方式

後に触れますが、照合順序には大文字/小文字の区別の有無や、
アクセントの区別の有無などに応じて様々な種類があります。

使用可能な照合順序の確認方法

照合順序も同じように以下で確認することができます。

1. SHOW COLLATION ステートメントを使用する
2. INFORMATION_SCHEMA.COLLATIONS テーブルを参照する

以下では SHOW COLLATION を使用して照合順序の一覧を取得しています。

mysql> SHOW COLLATION WHERE Charset = 'utf8mb4';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci         | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci         | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_cs         | utf8mb4 | 278 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_bin           | utf8mb4 | 309 |         | Yes      |       1 | NO PAD        |
| ...                        |     ... | ... |         | ...      |     ... | ... ...       |
| utf8mb4_unicode_520_ci     | utf8mb4 | 246 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_unicode_ci         | utf8mb4 | 224 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_vietnamese_ci      | utf8mb4 | 247 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_vi_0900_ai_ci      | utf8mb4 | 277 |         | Yes      |       0 | NO PAD        |
| utf8mb4_vi_0900_as_cs      | utf8mb4 | 300 |         | Yes      |       0 | NO PAD        |
| utf8mb4_zh_0900_as_cs      | utf8mb4 | 308 |         | Yes      |       0 | NO PAD        |
+----------------------------+---------+-----+---------+----------+---------+---------------+

※_ai や _ci といったサフィックスは以下のような意味があります。

サフィックス	正称	意味
_ai	Accent-insensitive	アクセントを区別しない
_as	Accent-sensitive	アクセントを区別する
_ci	Case-insensitive	大文字小文字を区別しない
_cs	Case-sensitive	大文字小文字を区別する
_ks	Kana-sensitive	カナを区別する
_bin	Binary	バイナリ

確認方法

現在設定されている文字コードおよび照合順序は
SHOW VARIABLES ステートメントで確認可能です。

文字コード

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

今回関連するシステム変数の説明は以下のとおりです。

システム変数名	意味
character_set_client	クライアントからサーバへのデータ送信時に 使用される文字コード
character_set_connection	クエリを実行時に使用される文字コード
character_set_database	デフォルトデータベースの文字コード
character_set_results	サーバからクライアントへ返す結果の文字コード
character_set_server	サーバのデフォルト文字コード

照合順序

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb3_general_ci |
| collation_database   | utf8mb4_bin        |
| collation_server     | utf8mb4_bin        |
+----------------------+--------------------+

システム変数の説明は以下のとおりです。

システム変数名	意味
collation_connection	コネクションのデフォルト照合順序
collation_database	データベースのデフォルト照合順序
collation_server	サーバのデフォルト照合順序

設定方法

文字コードと照合順序は以下の単位で設定可能です。
それぞれのケース別に設定方法を見ていきます。

1. サーバ
2. データベース
3. テーブル
4. カラム
5. クライアント
6. サーバ ⇔ クライアント間での通信

サーバ

• オプションファイルで指定する
  オプションファイルの [mysqld] セクションに character-set-server
  および collation-server を指定することで、 MySQL サーバ起動時に
  設定した内容がオプションとして読み込まれます。

my.cnf / my.ini

[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_bin

オプションファイルに設定できる項目の詳細についてはサーバシステム変数を参照。

• サーバ起動時にオプションで指定する
  サーバ起動時に --character-set-server および
  --collation-server オプションを使用することで設定可能です。

$ mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_0900_ai_ci

サーバ起動時に指定可能なオプションの詳細については
サーバコマンドオプションを参照。

データベース

• データベース作成時に指定する
  CREATE DATABASE 実行時に CHARSET、COLLATEを指定することで設定可能です。

CREATE DATABASE foo_db 
    CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

CHARSET は、CHARACTER SET のシノニムとして
使用できるので、どちらの記載方法でもOK

• データベース定義変更時に指定する
  既存のデータベースは ALTER DATABASE 実行時にCHARSET、COLLATEを指定することで設定可能です。

ALTER DATABASE foo_db 
    CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

テーブル

• テーブル作成時に指定する
  CREATE TABLE 実行時に CHARSET と COLLATE を指定することで設定可能です。

CREATE TABLE bar_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
) CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

• テーブル定義変更時に指定する
  既存のテーブルは ALTER TABLE 実行時に
  CHARSET、COLLATEを指定することで設定可能です。

ALTER TABLE bar_table
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

カラム

• テーブル作成時に指定する
  CREATE TABLE 実行時にカラムに CHARSET と
  COLLATE を指定することで設定可能です。

CREATE TABLE hoge_table (
    col1 VARCHAR(5)
      CHARSET latin1
      COLLATE latin1_german1_ci
);

• テーブル定義変更時に指定する
  既存のカラムは ALTER TABLE 実行時に
  CHARSET、COLLATEを指定することで設定可能です。

ALTER TABLE hoge_table MODIFY
    col1 VARCHAR(5)
      CHARSET latin1
      COLLATE latin1_swedish_ci;

クライアント

• オプションファイルで指定する
  オプションファイルの [client] セクションに default-character-set を
  指定することで MySQL サーバ起動時に設定した内容がオプションとして読み込まれます。

my.cnf / my.ini

[client]
default-character-set = utf8mb4

• サーバ接続時にオプションで指定
  クライアント接続時に --default-character-set
  オプションを指定することで設定可能です。

一部の文字セットは、クライアントの文字セットとして使用できません。
使用しようとするとエラーとなるため注意。

$ mysql -u root -p --default-character-set=utf8mb4

サーバ接続時に指定可能なオプションの詳細については
mysql クライアントオプションを参照。

• サーバ接続後に設定
  MySQL サーバ接続後には SET NAMES ステートメントを使用することで設定可能です。

SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;

なお、リファレンスによればSET NAMES 'charset_name' ステートメント
(COLLATE 指定なし)は次の 3 つのステートメント実行と同等とのこと。

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

サーバ ⇔ クライアント間での通信

サーバとクライアント間の通信では以下のシステム変数の
設定に従い文字コードが変換されていきます。

通信の段階	システム変数
クライアントからサーバに送信	character_set_client
サーバ受信後	character_set_connection (※)
サーバからクライアントに送信	character_set_results

一部例外あり。詳細は文字セットイントロデューサを参照

それぞれのシステム変数の設定方法はこれまでに
説明した内容と重複するため割愛します。

まとめ

ここまで MySQL の文字コード(+照合順序)について調べた内容を
まとめてみました。文字コードは様々な単位で設定できることが
わかりましたが、特段理由がない限りは設定を統一して
おかないと痛い目に遭いそうですね。

ではどの文字コードを選択すべきかという話になるのですが、
基本的に候補に挙がるのは Unicode(utf8mb4) かと思います。
理由は以下のようなものがあります。

• 多言語対応
• utf8mb3 では扱えない4バイト文字が扱える
  -> つまり utf8mb3(utf8 のエイリアス)は一般的にいう UTF-8 ではないということ
• 文字化けしづらい

次にどの照合順序を設定するかという問題ですが、どの文字を区別して
どの文字を区別しない、というのは要件次第になるかと思います。

今回、文字コードと照合順序について基本的なことを調べてみましたが、
調べていて個人的におもしろいと思ったのが、 Unicode には
以下のようなニッチすぎる記号が多数収録されているということです。

名称	例
錬金術記号	🜔☿🝪
マージャン記号	🀀🀇🀫
幾何学模様	▣▦◰
易経記号	䷀䷔䷖
光学的文字認識	⑄⑆⑉

こうしたよくわからない記号や文字をユーザに入力されても文字化けしないよう、
utf8mb4 で統一しておくのが吉ですね。今回は以上です。

参考

• MySQL 8.0 リファレンスマニュアル
• プログラマのための文字コード技術入門