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

# はじめに

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

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

# 文字コード

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

## 文字集合(Character set)

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

<ins><i>_文字集合の例_</i></ins>

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

## 符号化方式(Encoding)

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

<ins><i>符号化方式の例</i></ins>

| 符号化方式 | 文字集合 | 利用例 |
|:-|:-|:-|
| 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` ステートメントを使用する
1. `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 ドキュメントの例](https://dev.mysql.com/doc/refman/8.0/ja/charset-general.html)を以下に示します。

> 文字セットとは、記号とエンコーディングのセットです。 照合順序とは、文字セット内の文字を比較するためのルールを集めたものです。 架空の文字セットを例にして、文字セットと照合順序の違いを見てみましょう。
> 
> 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` ステートメントを使用する
1. `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 | クライアントからサーバへのデータ送信時に<br />使用される文字コード  |
| 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. サーバ
1. データベース
1. テーブル
1. カラム
1. クライアント
1. サーバ ⇔ クライアント間での通信


## サーバ

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

```:my.cnf / my.ini
[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_bin
```

:::note info
オプションファイルに設定できる項目の詳細については[サーバシステム変数](https://dev.mysql.com/doc/refman/8.0/ja/server-system-variables.html)を参照。
:::

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

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

:::note info
サーバ起動時に指定可能なオプションの詳細については
[サーバコマンドオプション](https://dev.mysql.com/doc/refman/8.0/ja/server-options.html)を参照。
:::

## データベース

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

```sql
CREATE DATABASE foo_db 
    CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
```

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

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

```sql
ALTER DATABASE foo_db 
    CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
```

## テーブル

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

```sql
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`を指定することで設定可能です。

```sql
ALTER TABLE bar_table
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
```

## カラム

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

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

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

```sql
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` 
オプションを指定することで設定可能です。

:::note warn
[一部の文字セット](https://dev.mysql.com/doc/refman/8.0/ja/charset-connection.html#charset-connection-impermissible-client-charset)は、クライアントの文字セットとして使用できません。
使用しようとするとエラーとなるため注意。
:::

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

:::note info
サーバ接続時に指定可能なオプションの詳細については
[mysql クライアントオプション](https://dev.mysql.com/doc/refman/8.0/ja/mysql-command-options.html)を参照。
:::

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

```sql
SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;
```

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

```sql
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 |

:::note warn
一部例外あり。詳細は[文字セットイントロデューサ](https://dev.mysql.com/doc/refman/8.0/ja/charset-introducer.html)を参照
:::

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

# まとめ

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

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

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

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

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

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

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

# 参考

- [MySQL 8.0 リファレンスマニュアル](https://dev.mysql.com/doc/refman/8.0/ja/)
- [プログラマのための文字コード技術入門](https://amzn.to/40dOzYD)