MySQL 文字コードと照合順序の選定と設定戦略

MySQL における文字表現の基礎体系

データベースシステムにおいて文字データを適切に扱うためには、文字コード（Character Set）と照合順序（Collation）の理解が不可欠です。これらはデータの保存形式と比較ルールを決定づける重要な要素です。

核心概念の定義

技術用語の定義を明確にすることで、設定時の混乱を防ぐことができます。

文字（Character）: 人間が認識可能な記号の最小単位。アルファベット、漢字、数字、記号などが含まれます。计算机内部では二进制データとして扱われます。
文字集合（Character Set）: 文字の集合と、それぞれに対応する二进制番号の定義です。ASCII、UTF-8、Shift_JIS などが代表例です。
文字エンコーディング（Encoding）: 文字集合の番号を二进制データに変換する規則です。多くの場合、文字集合名とエンコーディング名は同一ですが、Unicode においては UTF-8、UTF-16 などの複数のエンコーディング方式が存在します。
照合順序（Collation）: 同一文字集合内における文字の比較および排序ルールです。大文字小文字の区別有无、アクセント記号の扱いなどが含まれます。一つの文字集合に対して複数の照合順序が存在します。

簡潔に言えば、文字集合は「どのように保存するか」を定め、照合順序は「どのように比較するか」を定めます。MySQL ではこれらを独立して設定可能ですが、文字集合のみを指定した場合、対応するデフォルトの照合順序が自動的に適用されます。

現在の設定状況の確認

サーバー、データベース、接続レベルで現在適用されている設定を確認するには、以下の変数を参照します。

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8mb4                          |
| character_set_connection | utf8mb4                          |
| character_set_database   | utf8mb4                          |
| character_set_results    | utf8mb4                          |
| character_set_server     | utf8mb4                          |
+--------------------------+----------------------------------+

同様に、照合順序の設定は以下のコマンドで確認可能です。

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

主要な変数の意味は以下の通りです。

character_set_server: サーバー全体のデフォルト設定。
character_set_database: 現在選択中のデータベースの設定。
character_set_client: クライアントから送信されるクエリのエンコーディング。
character_set_connection: クエリ受信後の内部変換エンコーディング。
character_set_results: クライアントへ返却される結果セットのエンコーディング。

各レベルでの設定方法

文字コードと照合順序は、サーバー、データベース、テーブル、列、接続の各レベルで階層的に設定できます。

1. サーバーレベルの設定

MySQL サーバー起動時のデフォルト値を定義します。設定ファイル（my.cnf または my.ini）の [mysqld] セクションに記述します。

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

あるいは、起動コマンドラインオプションで指定することも可能です。

mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

2. データベースレベルの設定

データベース作成時に指定するか、既存のデータベースに対して変更を行います。既存のデータ自体は変換されませんが、新規作成されるテーブルのデフォルト値として継承されます。

作成時の例:

CREATE DATABASE IF NOT EXISTS inventory_db
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

変更時の例:

ALTER DATABASE inventory_db
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

3. テーブルレベルの設定

テーブル作成時または変更時に、特定のテーブルのみ異なる文字集合を適用できます。

作成時の例:

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

既存テーブルの変換（データも変換される）:

ALTER TABLE products
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

4. 列レベルの設定

同一テーブル内でも、特定の列のみ異なる文字集合を必要とする場合に使用します。頻度は低いですが、柔軟な設定が可能です。

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
    content TEXT
) DEFAULT CHARSET=utf8mb4;

既存列の変更:

ALTER TABLE articles
MODIFY title VARCHAR(200)
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

5. 接続レベルの設定

クライアントアプリケーションとサーバー間の通信における文字コードです。character_set_client、character_set_connection、character_set_results の 3 つを一括設定するのが一般的です。

SET NAMES utf8mb4;

これは以下の 3 つの命令と同等です。

SET character_set_client = utf8mb4;
SET character_set_results = utf8mb4;
SET character_set_connection = utf8mb4;

永続的に設定する場合は、クライアント設定ファイル（[mysql] セクション）に default-character-set = utf8mb4 を記述します。

適切な文字集合の選定基準

システム要件に応じて最適な文字集合を選択することが、パフォーマンスと互換性に影響します。

多言語対応: 国際化が必要な場合、Unicode 互換の utf8mb4 が必須です。MySQL の utf8 は最大 3 バイトまでしか扱えないため、絵文字などが保存できません。
既存データの互換性: 移行元システムが Shift_JIS や GBK などのレガシーエンコーディングを使用している場合、データ欠損を防ぐために互換性を考慮する必要があります。
ストレージと性能: 英数中心のアプリケーションでは、1 バイト文字が多い ASCII 互換セットの方が容量効率が良い場合があります。しかし、日本語を含む場合、utf8mb4（最大 4 バイト）は固定長ではないため、インデックスサイズに注意が必要です。一方、GBK などの 2 バイト固定長に近い体系は、特定の条件下で I/O 効率が向上することがあります。
演算処理: 大量の排序や比較処理を行う場合、固定長文字集合の方が処理速度が速い傾向がありますが、現代のハードウェアでは微差であることが多いです。

照合順序の違いによる影響

照合順序が異なると、排序結果や一致判定の挙動が変化します。例えば、_ci (Case Insensitive) を付与した規則は大文字小文字を区別せず、_bin は二进制値で厳密に比較します。

異なる照合順序を持つ列を比較演算や結合（JOIN）で使用すると、以下のエラーが発生する可能性があります。

Error 1271: Illegal mix of collations for operation

データ移行や環境構築の際は、移行元と移行先で文字集合および照合順序が完全に一致していることを確認する必要があります。特に、デフォルト設定に依存せず、明示的に定義することがトラブル回避の鍵となります。

タグ: MySQL character-set collation utf8mb4 database-architecture

5月14日 05:47 投稿

異端開発室