MySQL - 文字コードと照合順序

概要

MySQLにおける文字コード (CHARACTER SET) と照合順序 (COLLATION) は、データベースにおけるテキストデータの保存方法、比較方法、ソート方法を制御する重要な設定である。

文字コードは、文字をバイト列としてどのように表現するかを定義し、照合順序は文字列の比較・ソート規則を定義する。

MySQL 8.0以降では、デフォルトの文字コードが utf8mb4 に変更され、デフォルトの照合順序が utf8mb4_0900_ai_ci となった。
これにより、絵文字を含む全Unicode文字の正しい保存・比較が可能となった。

文字コードと照合順序は、サーバレベル、データベースレベル、テーブルレベル、カラムレベルでそれぞれ設定でき、下位レベルが設定されていない場合は上位レベルの設定が継承される。

また、既存のテーブルやカラムの文字コード変換、utf8mb3から utf8mb4への移行手順、文字化けやコード変換エラーのトラブルシューティングについても理解する必要がある。

文字コードの基本概念

文字コードとは

文字コード (CHARACTER SET または CHARSET) は、文字をコンピュータ内部でバイト列として表現するための規則である。
MySQLでは、各カラム、テーブル、データベース、サーバに対して文字コードを設定できる。

文字コードは、以下の要素を定義する。

• 文字の表現方法

  文字をバイト列に変換する方法

• 最大バイト長

  1文字を表現するために必要な最大バイト数

• 文字のレパートリー

  表現可能な文字の集合

MySQLでは、各文字コードに対して1つ以上の照合順序が定義されており、文字コードを設定すると同時にデフォルトの照合順序も決定される。

MySQLでサポートされる文字コード

MySQLは、多数の文字コードをサポートしている。
下表に、主要な文字コードを示す。

MySQLでサポートされる主要な文字コード

文字コード	説明	最大バイト長	備考
utf8mb4	4バイトUTF-8 全Unicode文字をサポート	4	MySQL 8.0以降のデフォルト 絵文字・拡張文字対応
utf8mb3 (utf8)	3バイトUTF-8 BMP (基本多言語面) のみ	3	MySQL 8.0.28以降は非推奨 絵文字は保存不可
utf8	utf8mb3のエイリアス	3	将来的にutf8mb4に変更予定
utf16	UTF-16エンコーディング	4	BOM不要、ビッグエンディアン
utf16le	UTF-16LE (リトルエンディアン)	4	リトルエンディアン版
utf32	UTF-32エンコーディング	4	全文字が4バイト固定
latin1	ISO-8859-1 (西欧言語)	1	MySQL 5.7以前のデフォルト
latin2	ISO-8859-2 (中欧言語)	1	チェコ語、ハンガリー語等
ascii	US-ASCII	1	7ビット文字セット
binary	バイナリデータ	1	文字コード変換なし
cp932	Windows-31J (Shift_JIS)	2	日本語Windows環境
eucjpms	EUC-JP (拡張版)	3	MySQL拡張EUC-JP
ujis	EUC-JP	3	標準EUC-JP
sjis	Shift_JIS	2	日本語Shift_JIS
gb18030	GB18030 (中国語)	4	中国国家標準
gbk	GBK (中国語)	2	GB2312の拡張
gb2312	GB2312 (簡体字中国語)	2	簡体字中国語
big5	Big5 (繁体字中国語)	2	台湾・香港で使用
euckr	EUC-KR (韓国語)	2	韓国語KS X 1001
tis620	TIS-620 (タイ語)	1	タイ語
ucs2	UCS-2 (BMP範囲のみ)	2	BMP範囲のみの2バイト固定
armscii8	ARMSCII-8 (アルメニア語)	1	アルメニア語
greek	ISO-8859-7 (ギリシャ語)	1	ギリシャ語
hebrew	ISO-8859-8 (ヘブライ語)	1	ヘブライ語

現在の推奨は utf8mb4 である。
utf8mb4 は、全Unicode文字を正しく保存でき、絵文字や拡張漢字も扱える。

utf8mb4とutf8 (utf8mb3) の違い

MySQLには、utf8 (utf8mb3) と utf8mb4 という2つのUTF-8文字コードが存在する。
両者の違いを理解することは重要である。

utf8mb3 vs utf8mb4 比較

項目	utf8mb3 (utf8)	utf8mb4
最大バイト長	3バイト	4バイト
Unicode範囲	BMP (U+0000 - U+FFFF) のみ	全Unicode (U+0000 - U+10FFFF)
絵文字サポート	不可 (ほとんどの絵文字は保存不可)	可 (全絵文字対応)
サロゲートペア	未対応	対応
MySQL 8.0以降の扱い	非推奨 (8.0.28以降)	デフォルト文字コード
utf8エイリアス	utf8 = utf8mb3 (現在)	将来的にutf8 = utf8mb4に変更予定
ストレージサイズ	3バイト/文字 (最大)	4バイト/文字 (最大)
インデックス長制限	少ない制限	より大きい制限 (考慮が必要)
パフォーマンス	わずかに高速	わずかに低速 (実用上は無視可能)
推奨度	非推奨 (レガシー環境以外)	推奨 (新規開発は必須)

MySQL 8.0.28以降では、utf8mb3 の使用時に非推奨警告が表示される。
新規開発では、必ず utf8mb4 を使用すべきである。

utf8 というエイリアスは、現在は utf8mb3 を指すが、将来的には utf8mb4 を指すように変更される予定である。
そのため、明示的に utf8mb4 または utf8mb3 を指定することが推奨される。

照合順序の基本概念

照合順序とは

照合順序 (COLLATION) は、文字列の比較・ソート規則を定義する。
同じ文字コードでも、照合順序によって比較結果やソート順が異なる。

照合順序は、以下の動作に影響する。

• WHERE 句の文字列比較
• ORDER BY 句のソート順
• GROUP BY 句のグルーピング
• DISTINCT の重複判定
• インデックスの並び順
• 文字列関数の動作 (LIKE、UPPER、LOWER等)

照合順序の命名規則は、以下の形式である。

• charset_language_suffix

サフィックスの意味を以下に示す。

照合順序のサフィックス

サフィックス	説明
_ci	Case Insensitive (大文字小文字を区別しない)
_cs	Case Sensitive (大文字小文字を区別する)
_bin	Binary (バイナリ比較、厳密な比較)
_ai	Accent Insensitive (アクセント記号を区別しない、MySQL 8.0以降)
_as	Accent Sensitive (アクセント記号を区別する、MySQL 8.0以降)
_ks	Kana Sensitive (ひらがな・カタカナを区別する、MySQL 8.0以降)

例として、utf8mb4_0900_ai_ci は、以下を意味する。

utf8mb4_0900_ai_ciの命名規則

要素	説明
utf8mb4	文字コード
0900	Unicode Collation Algorithm (UCA) 9.0.0準拠
ai	Accent Insensitive (アクセント記号を区別しない)
ci	Case Insensitive (大文字小文字を区別しない)

主要な照合順序の一覧

MySQLでは、各文字コードに対して複数の照合順序が用意されている。
下表に、utf8mb4 の主要な照合順序を示す。

utf8mb4の主要な照合順序

照合順序	説明	MySQL	備考
utf8mb4_general_ci	シンプルな大文字小文字非区別比較	5.5以降	高速だが、一部の言語で不正確 デフォルト (MySQL 5.7以前)
utf8mb4_unicode_ci	Unicode標準に基づく比較	5.5以降	より正確だが、やや低速 UCA 4.0.0準拠
utf8mb4_unicode_520_ci	Unicode 5.2.0標準に基づく比較	5.6以降	UCA 5.2.0準拠
utf8mb4_0900_ai_ci	Unicode 9.0標準、アクセント・大文字小文字非区別	8.0以降	デフォルト (MySQL 8.0以降) UCA 9.0.0準拠 高速かつ正確
utf8mb4_0900_as_cs	Unicode 9.0標準、アクセント・大文字小文字区別	8.0以降	厳密な比較が必要な場合
utf8mb4_0900_as_ci	Unicode 9.0標準、アクセント区別、大文字小文字非区別	8.0以降	アクセントのみ区別
utf8mb4_0900_ai_cs	Unicode 9.0標準、アクセント非区別、大文字小文字区別	8.0以降	大文字小文字のみ区別
utf8mb4_ja_0900_as_cs	日本語用照合順序、アクセント・大文字小文字区別	8.0以降	JIS X 4061準拠 日本語のソート順
utf8mb4_ja_0900_as_cs_ks	日本語用照合順序、カナも区別	8.0以降	ひらがな・カタカナを区別
utf8mb4_bin	バイナリ比較	5.5以降	バイト値による厳密な比較
utf8mb4_german2_ci	ドイツ語電話帳順序	5.5以降	ドイツ語特有のソート順
utf8mb4_croatian_ci	クロアチア語順序	5.5以降	クロアチア語特有のソート順
utf8mb4_polish_ci	ポーランド語順序	5.5以降	ポーランド語特有のソート順
utf8mb4_vietnamese_ci	ベトナム語順序	5.5以降	ベトナム語特有のソート順

MySQL 8.0以降では、utf8mb4_0900_ai_ciがデフォルトの照合順序である。
この照合順序は、高速かつ正確で、多くの言語に対応している。

日本語データを扱う場合は、utf8mb4_ja_0900_as_cs または utf8mb4_ja_0900_as_cs_ks の使用を検討する。

照合順序の比較動作

照合順序によって、文字列比較の結果が異なる。
具体的な例を以下に示す。

• 大文字小文字の区別

   -- utf8mb4_general_ci (大文字小文字を区別しない)
   
   SELECT 'ABC' = 'abc' COLLATE utf8mb4_general_ci;
   -- 結果: 1 (true)
   
   -- utf8mb4_bin (バイナリ比較、区別する)
   
   SELECT 'ABC' = 'abc' COLLATE utf8mb4_bin;
   -- 結果: 0 (false)
   
   -- utf8mb4_0900_as_cs (大文字小文字を区別する)
   
   SELECT 'ABC' = 'abc' COLLATE utf8mb4_0900_as_cs;
   -- 結果: 0 (false)
  

  
  

• アクセント記号の区別

   -- utf8mb4_0900_ai_ci (アクセント非区別)
   
   SELECT 'café' = 'cafe' COLLATE utf8mb4_0900_ai_ci;
   -- 結果: 1 (true)
   
   -- utf8mb4_0900_as_ci (アクセント区別)
   
   SELECT 'café' = 'cafe' COLLATE utf8mb4_0900_as_ci;
   -- 結果: 0 (false)
  

  
  

• ひらがな・カタカナの区別

   -- utf8mb4_ja_0900_as_cs (カナ非区別)
   
   SELECT 'あいうえお' = 'アイウエオ' COLLATE utf8mb4_ja_0900_as_cs;
   -- 結果: 1 (true)
   
   -- utf8mb4_ja_0900_as_cs_ks (カナ区別)
   
   SELECT 'あいうえお' = 'アイウエオ' COLLATE utf8mb4_ja_0900_as_cs_ks;
   -- 結果: 0 (false)
  

  
  

• ソート順の違い

   -- utf8mb4_general_ci でのソート
   
   SELECT name FROM users ORDER BY name COLLATE utf8mb4_general_ci;
   -- 結果: ABC, Abc, abc (アルファベット順、大文字小文字混在)
   
   -- utf8mb4_bin でのソート
   
   SELECT name FROM users ORDER BY name COLLATE utf8mb4_bin;
   -- 結果: ABC, Abc, abc (バイト値順、大文字が先)
  

サーバレベルの設定

サーバの文字コードと照合順序

MySQLサーバ全体のデフォルト文字コードと照合順序は、システム変数で設定される。

主要なシステム変数を以下に示す。

主要なシステム変数

変数名	説明
character_set_server	サーバのデフォルト文字コード
collation_server	サーバのデフォルト照合順序
character_set_database	現在のデータベースの文字コード
collation_database	現在のデータベースの照合順序

サーバの文字コード設定を確認する。

 SHOW VARIABLES LIKE 'character_set_server';
 SHOW VARIABLES LIKE 'collation_server';

MySQL 8.0以降のデフォルト値を以下に示す。

MySQL 8.0以降のデフォルト値

変数名	デフォルト値
character_set_server	utf8mb4
collation_server	utf8mb4_0900_ai_ci

MySQL 5.7以前のデフォルト値を以下に示す。

MySQL 5.7以前のデフォルト値

変数名	デフォルト値
character_set_server	latin1
collation_server	latin1_swedish_ci

my.cnfでの設定

サーバのデフォルト文字コードと照合順序は、設定ファイル my.cnf (または my.ini) で設定できる。

 # my.cnf

 [mysqld]
 character-set-server=utf8mb4
 collation-server=utf8mb4_0900_ai_ci
 
 [client]
 default-character-set=utf8mb4

[mysqld] セクションは、MySQLサーバの設定である。
[client] セクションは、MySQLクライアント (mysql コマンド等) の設定である。

設定ファイルを変更した後は、MySQLサーバを再起動する必要がある。

# systemd環境
sudo systemctl restart mysqld
## または
sudo systemctl restart mysql

レガシー環境からの移行時には、以下に示す設定も追加する。

 # my.cnf - レガシークライアント対策
 
 [mysqld]
 character-set-server=utf8mb4
 collation-server=utf8mb4_0900_ai_ci
 
 # クライアントハンドシェイクを無視 (全接続をutf8mb4で処理)
 skip-character-set-client-handshake

skip-character-set-client-handshake オプションは、クライアントの文字コード設定を無視し、サーバの文字コードを強制する。
レガシーアプリケーションが誤った文字コードを要求する場合に有効である。

接続時の文字コード

クライアントがMySQLサーバに接続する際、文字コード関連の変数が設定される。

下表に、接続時の文字コード関連変数を示す。

接続時の文字コード関連変数

変数名	説明	影響範囲
character_set_client	クライアントが送信するSQLステートメントの文字コード	クライアント → サーバ
character_set_connection	サーバがSQLステートメントを解釈する際の文字コード	サーバ内部処理
character_set_results	サーバがクライアントに返す結果セットの文字コード	サーバ → クライアント
character_set_database	現在のデータベースの文字コード	データベース単位
character_set_server	サーバのデフォルト文字コード	サーバ全体

これらの変数を確認する。

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

SET NAMES ステートメントは、3つの変数を一括設定する。

 SET NAMES 'utf8mb4';
 
 -- 上記は、以下と同等
 
 SET character_set_client = 'utf8mb4';
 SET character_set_connection = 'utf8mb4';
 SET character_set_results = 'utf8mb4';

照合順序も同時に指定できる。

 SET NAMES 'utf8mb4' COLLATE 'utf8mb4_ja_0900_as_cs';

SET CHARACTER SET ステートメントとの違いを以下に示す。

 SET CHARACTER SET 'utf8mb4';
 
 -- 上記は、以下と同等
 
 SET character_set_client = 'utf8mb4';
 SET character_set_results = 'utf8mb4';
 SET character_set_connection = @@character_set_database;

SET CHARACTER SET は、character_set_connection をデータベースのデフォルトに設定する点が異なる。
通常は、SET NAMES を使用することが推奨される。

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

データベースの文字コード指定

データベース作成時に、文字コードと照合順序を指定できる。

 CREATE DATABASE database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

照合順序を省略した場合は、文字コードのデフォルト照合順序が使用される。

 CREATE DATABASE database_name
    CHARACTER SET utf8mb4;
 -- 照合順序は utf8mb4_0900_ai_ci (MySQL 8.0のデフォルト)

文字コードも照合順序も省略した場合は、サーバのデフォルトが使用される。

 CREATE DATABASE database_name;
 -- character_set_server と collation_server が使用される

既存データベースの文字コードと照合順序を変更する。

 ALTER DATABASE database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

※注意
ALTER DATABASE は、データベースのデフォルト設定のみを変更し、既存テーブルの文字コードは変更しない。
既存テーブルの文字コードを変更するには、個別に ALTER TABLE を実行する必要がある。

データベースの文字コード確認

データベースの文字コードと照合順序を確認する方法を以下に示す。

 -- 方法1: SHOW CREATE DATABASE
 
 SHOW CREATE DATABASE database_name;

出力例を以下に示す。

+---------------+--------------------------------------------------------------------------+
| Database      | Create Database                                                          |
+---------------+--------------------------------------------------------------------------+
| database_name | CREATE DATABASE `database_name`                                          |
|               | /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */    |
+---------------+--------------------------------------------------------------------------+

 -- 方法2: INFORMATION_SCHEMA.SCHEMATA
 
 SELECT SCHEMA_NAME,
        DEFAULT_CHARACTER_SET_NAME,
        DEFAULT_COLLATION_NAME
 FROM INFORMATION_SCHEMA.SCHEMATA
 WHERE SCHEMA_NAME = 'database_name';

 -- 方法3: システム変数 (現在のデータベース)
 
 USE database_name;
 SHOW VARIABLES LIKE 'character_set_database';
 SHOW VARIABLES LIKE 'collation_database';

全データベースの文字コード設定を一覧表示する。

 SELECT SCHEMA_NAME,
        DEFAULT_CHARACTER_SET_NAME,
        DEFAULT_COLLATION_NAME
 FROM INFORMATION_SCHEMA.SCHEMATA
 ORDER BY SCHEMA_NAME;

テーブル・カラムレベルの設定

テーブルの文字コード指定

テーブル作成時に、文字コードと照合順序を指定できる。

 CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
 ) CHARACTER SET utf8mb4
   COLLATE utf8mb4_unicode_ci;

テーブルの文字コードを省略した場合は、データベースのデフォルトが使用される。

 CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(100)
 );
 -- データベースの文字コードと照合順序が継承される

既存テーブルのデフォルト文字コードを変更する (既存カラムは変更されない)。

 ALTER TABLE table_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

※注意
この構文は、テーブルのデフォルト文字コードのみを変更し、既存カラムの文字コードは変更しない。
新しく追加されるカラムには、変更後の文字コードが適用される。

カラムの文字コード指定

カラム単位で文字コードと照合順序を指定できる。

 CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    email VARCHAR(255) CHARACTER SET ascii COLLATE ascii_general_ci,
    comment TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs
 );

カラムの文字コードを省略した場合は、テーブルのデフォルトが使用される。

 CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
 ) CHARACTER SET utf8mb4;
 -- 各カラムは utf8mb4 を継承

既存カラムの文字コードを変更する。

 ALTER TABLE table_name
    MODIFY name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

※注意
MODIFY 句を使用する場合は、カラムの型 (VARCHAR、TEXT等) も再指定する必要がある。

カラムの文字コードを確認する。

 SELECT COLUMN_NAME,
        CHARACTER_SET_NAME,
        COLLATION_NAME,
        COLUMN_TYPE
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'database_name'
   AND TABLE_NAME = 'table_name';

文字コードの継承ルール

文字コードと照合順序は、階層的に継承される。
下表に、継承ルールを示す。

文字コードの継承ルール

レベル	設定項目	継承元	優先度
サーバ	character_set_server collation_server	my.cnfまたはコマンドライン引数	最低 (最も広範囲)
データベース	DEFAULT CHARACTER SET DEFAULT COLLATE	サーバのデフォルト (未指定時)	低
テーブル	CHARACTER SET COLLATE	データベースのデフォルト (未指定時)	中
カラム	CHARACTER SET COLLATE	テーブルのデフォルト (未指定時)	最高 (最も優先)

継承の具体例を以下に示す。

 -- サーバのデフォルト: utf8mb4 / utf8mb4_0900_ai_ci
 
 -- データベース作成 (サーバのデフォルトを継承)
 
 CREATE DATABASE db1;
 -- db1: utf8mb4 / utf8mb4_0900_ai_ci
 
 -- データベース作成 (明示的に指定)
 
 CREATE DATABASE db2
    CHARACTER SET latin1
    COLLATE latin1_swedish_ci;
 -- db2: latin1 / latin1_swedish_ci
 
 -- テーブル作成 (データベースのデフォルトを継承)
 
 USE db1;
 CREATE TABLE table1 (
    name VARCHAR(100)
 );
 -- table1: utf8mb4 / utf8mb4_0900_ai_ci
 -- name: utf8mb4 / utf8mb4_0900_ai_ci
 
 -- テーブル作成 (明示的に指定)
 
 CREATE TABLE table2 (
    name VARCHAR(100)
 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 -- table2: utf8mb4 / utf8mb4_unicode_ci
 -- name: utf8mb4 / utf8mb4_unicode_ci (テーブルから継承)
 
 -- カラムレベルで指定
 
 CREATE TABLE table3 (
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs,
    email VARCHAR(255)
 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 -- table3: utf8mb4 / utf8mb4_unicode_ci
 -- name: utf8mb4 / utf8mb4_ja_0900_as_cs (カラムレベルで上書き)
 -- email: utf8mb4 / utf8mb4_unicode_ci (テーブルから継承)

文字コードの確認方法

システム変数の確認

MySQLの文字コード関連のシステム変数を確認する。

 SHOW VARIABLES LIKE 'character%';
 SHOW VARIABLES LIKE 'collation%';

出力例を以下に示す。

文字コード関連のシステム変数

変数名	値 (例)	説明
character_set_client	utf8mb4	クライアントが送信するSQLの文字コード
character_set_connection	utf8mb4	サーバがSQLを解釈する際の文字コード
character_set_database	utf8mb4	現在のデータベースの文字コード
character_set_filesystem	binary	ファイルシステム上のファイル名の文字コード
character_set_results	utf8mb4	サーバが返す結果セットの文字コード
character_set_server	utf8mb4	サーバのデフォルト文字コード
character_set_system	utf8mb3	システムメタデータの文字コード (固定)
collation_connection	utf8mb4_0900_ai_ci	接続の照合順序
collation_database	utf8mb4_0900_ai_ci	現在のデータベースの照合順序
collation_server	utf8mb4_0900_ai_ci	サーバのデフォルト照合順序

※注意
character_set_system は、システムメタデータ (テーブル名、カラム名等) の文字コードであり、utf8mb3 固定である。
この値は変更できない。

データベース・テーブル・カラムの確認

データベース、テーブル、カラムの文字コード設定を確認する方法を以下に示す。

データベースの文字コードを確認する。

 SELECT SCHEMA_NAME,
        DEFAULT_CHARACTER_SET_NAME,
        DEFAULT_COLLATION_NAME
 FROM INFORMATION_SCHEMA.SCHEMATA
 WHERE SCHEMA_NAME = 'database_name';

テーブルの文字コードを確認する。

 SELECT TABLE_NAME,
        TABLE_COLLATION
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'database_name';

※注意
TABLE_COLLATION カラムは照合順序を示し、文字コードは照合順序の接頭辞から判別できる。
例えば、utf8mb4_unicode_ciの文字コードは、utf8mb4である。

• カラムの文字コードを確認する。

   SELECT COLUMN_NAME,
          CHARACTER_SET_NAME,
          COLLATION_NAME,
          COLUMN_TYPE
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = 'database_name'
     AND TABLE_NAME = 'table_name'
   ORDER BY ORDINAL_POSITION;
  

  
  

• 特定のテーブルの詳細を確認する。

   SHOW CREATE TABLE <テーブル名>;
  

  
  

  出力例を以下に示す。

   CREATE TABLE `users` (
     `id` int NOT NULL AUTO_INCREMENT,
     `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
     `email` varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  

  
  

• 全データベースの文字コード設定を一覧表示する。

   SELECT TABLE_SCHEMA,
          TABLE_NAME,
          TABLE_COLLATION,
          COLUMN_NAME,
          CHARACTER_SET_NAME,
          COLLATION_NAME
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
     AND CHARACTER_SET_NAME IS NOT NULL
   ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;
  

文字コードの変更・変換

サーバレベルの変更

サーバレベルの文字コードを変更する場合は、my.cnf ファイルを編集する。

 # my.cnf
 
 [mysqld]
 character-set-server=utf8mb4
 collation-server=utf8mb4_0900_ai_ci
 
 [client]
 default-character-set=utf8mb4

設定ファイルを変更した後は、MySQLサーバを再起動する。

 sudo systemctl restart mysqld

再起動後、設定が反映されているか確認する。

 SHOW VARIABLES LIKE 'character_set_server';
 SHOW VARIABLES LIKE 'collation_server';

※注意
サーバレベルの設定変更は、新規作成されるデータベースにのみ影響する。
既存のデータベース、テーブル、カラムの文字コードは変更されない。

データベースレベルの変更

既存データベースのデフォルト文字コードを変更する。

 ALTER DATABASE database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

※注意
この操作は、データベースのデフォルト設定のみを変更する。
既存テーブルの文字コードは変更されない。
新しく作成されるテーブルには、変更後の文字コードが適用される。

既存テーブルの文字コードを変更するには、個別に ALTER TABLE を実行する必要がある。

テーブル・カラムの変換

既存テーブルとカラムの文字コードを変換する方法を以下に示す。

• テーブルのデフォルト文字コードのみを変更する。(既存カラムは変更されない)

   ALTER TABLE table_name
      CHARACTER SET utf8mb4
      COLLATE utf8mb4_unicode_ci;
  

  
  

• 全カラムの文字コードを一括変換する。(データも変換される)

   ALTER TABLE table_name
      CONVERT TO CHARACTER SET utf8mb4
      COLLATE utf8mb4_unicode_ci;
  

  
  

  ※注意

  CONVERT TO CHARACTER SET は、全ての文字カラムのデータを実際に変換する。

  大量のデータがある場合、この操作には時間が掛かる。

  また、テーブルロックが発生するため、本番環境では注意が必要である。

  
  

• 特定のカラムのみを変換する。

   ALTER TABLE table_name
      MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  

  
  

  ※注意

  MODIFY 句を使用する場合は、カラムの型も再指定する必要がある。

  
  

• バイナリカラムを経由して安全に変換する方法

  この方法は、既存データが既にutf8mb4として保存されているが、カラム定義がlatin1等の異なる文字コードになっている場合に有効である。

  バイナリ型を経由することで、文字コード変換を回避し、バイト列をそのまま保持することができる。

   -- ステップ1: バイナリ型に変換 (文字コード変換を回避)
   
   ALTER TABLE table_name
      MODIFY column_name VARBINARY(300);
   
   -- ステップ2: 目的の文字コードに変換
   
   ALTER TABLE table_name
      MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  

utf8mb3からutf8mb4への移行手順

utf8mb3 (utf8) からutf8mb4への移行は、以下の手順で実施する。

ステップ1 : バックアップ

移行前に、必ずデータベースの完全バックアップを取得する。

 mysqldump -u root -p --databases database_name > backup.sql

ステップ2 : インデックス長の確認

utf8mb4は1文字あたり最大4バイトを使用するため、インデックス長に制限がある。
InnoDBのインデックス最大長は767バイト (MySQL 5.7以前) または 3072バイト (MySQL 8.0以降) である。

問題のあるインデックスを確認する。

 SELECT TABLE_NAME,
        COLUMN_NAME,
        CHARACTER_MAXIMUM_LENGTH,
        CHARACTER_MAXIMUM_LENGTH * 4 AS max_bytes
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'database_name'
   AND DATA_TYPE IN ('varchar', 'char', 'text')
   AND CHARACTER_MAXIMUM_LENGTH * 4 > 767
 ORDER BY max_bytes DESC;

VARCHAR(255) を超えるカラムにインデックスがある場合は、カラム長を短縮するか、インデックスのプレフィックス長を指定する必要がある。

ステップ3 : サーバ設定の変更

my.cnf ファイルを編集する。

 # my.cnf
 
 [mysqld]
 character-set-server=utf8mb4
 collation-server=utf8mb4_0900_ai_ci
 
 # 大きなインデックスプレフィックスを許可 (MySQL 5.7以降)
 innodb_large_prefix=ON
 innodb_file_format=Barracuda
 innodb_file_per_table=ON
 
 [client]
 default-character-set=utf8mb4

※注意
MySQL 8.0以降では、innodb_large_prefix と innodb_file_format は非推奨となり、デフォルトで有効である。

MySQLサーバを再起動する。

sudo systemctl restart mysqld

ステップ4 : データベースの変換

データベースのデフォルト文字コードを変更する。

 ALTER DATABASE database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

ステップ5 : テーブルの変換

各テーブルを変換する。

 ALTER TABLE table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

全テーブルを一括変換するスクリプト例を以下に示す。

 #!/usr/bin/env sh
 
 DB_NAME="database_name"
 MYSQL_USER="root"
 MYSQL_PASS="password"
 
 # 全テーブル名を取得
 TABLES=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -e "USE $DB_NAME; SHOW TABLES;" | tail -n +2)
 
 # 各テーブルを変換
 for TABLE in $TABLES; do
    echo "Converting $TABLE..."
    mysql -u $MYSQL_USER -p$MYSQL_PASS -e "ALTER TABLE $DB_NAME.$TABLE CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;"
 done
 
 echo "Conversion completed."

※注意
大量のデータがある場合、テーブル変換には時間が掛かる。
本番環境では、メンテナンスウィンドウ中に実施するか、オンラインスキーマ変更ツール (pt-online-schema-change等) の使用を検討する。

ステップ6 : アプリケーションの接続設定

アプリケーションの接続設定で、SET NAMES 'utf8mb4' を実行するように変更する。

• PHPの例

   <?php
      $mysqli = new mysqli('localhost', 'user', 'password', 'database');
      $mysqli->set_charset('utf8mb4');
   ?>
  

  
  

• Pythonの例

   import mysql.connector
   
   conn = mysql.connector.connect(
      host='localhost',
      user='user',
      password='password',
      database='database',
      charset='utf8mb4',
      collation='utf8mb4_0900_ai_ci'
   )
  

ステップ7 : 動作確認

絵文字が正しく保存・取得できるか確認する。

 INSERT INTO test_table (text_column) VALUES ('🍣🍺😀');
 SELECT text_column FROM test_table;

文字化けが発生せず、絵文字が正しく表示されれば移行成功である。

トラブルシューティング

文字化けの原因と対処

文字化けは、文字コードの不一致によって発生する。
主な原因と対処方法を以下に示す。

原因1 : クライアント接続時の文字コード不一致

クライアントがサーバに送信するSQLの文字コードとサーバが期待する文字コードが異なる場合に発生する。

 -- 接続直後に実行
 
 SET NAMES 'utf8mb4';

または、アプリケーションの接続設定で文字コードを指定する。

 <?php
    $mysqli = new mysqli('localhost', 'user', 'password', 'database');
    $mysqli->set_charset('utf8mb4');
 ?>

原因2 : カラムの文字コードとデータの文字コードの不一致

カラムの定義がlatin1だが、実際のデータはutf8mb4として保存されている場合等に発生する。

 -- バイナリカラムを経由して変換
 
 ALTER TABLE table_name
    MODIFY column_name VARBINARY(1000);
 
 ALTER TABLE table_name
    MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

原因3 : ダンプファイルの文字コード不一致

mysqldumpで取得したダンプファイルの文字コードと、リストア先の文字コードが異なる場合に発生する。

 # ダンプ取得時に文字コードを指定
 mysqldump -u root -p --default-character-set=utf8mb4 database_name > dump.sql
 
 # リストア時に文字コードを指定
 mysql -u root -p --default-character-set=utf8mb4 database_name < dump.sql

原因4 : ターミナルの文字コード設定

mysqlクライアントを使用する際、ターミナルの文字コード設定が異なる場合に発生する。

• Linuxの場合

  環境変数 LANG を確認して、ja_JP.UTF-8 等に設定する。

• Windowsの場合

  コマンドプロンプトのプロパティでフォントを変更して、chcp 65001 でUTF-8に設定する。

診断クエリ

文字化けの原因を診断するクエリを以下に示す。

 -- 文字コード関連の変数を確認
 SHOW VARIABLES LIKE 'character%';
 SHOW VARIABLES LIKE 'collation%';
 
 -- テーブルとカラムの文字コードを確認
 SELECT TABLE_NAME,
        COLUMN_NAME,
        CHARACTER_SET_NAME,
        COLLATION_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'database_name'
   AND TABLE_NAME = 'table_name';
 
 -- データの実際のバイト列を確認
 SELECT column_name,
        HEX(column_name) AS hex_value,
        LENGTH(column_name) AS byte_length,
        CHAR_LENGTH(column_name) AS char_length
 FROM table_name
 WHERE id = 1;

LENGTH() 関数はバイト数、CHAR_LENGTH() 関数は文字数を返す。
utf8mb4の場合、日本語1文字は3バイトまたは4バイトである。

Illegal mix of collationsエラー

異なる照合順序を持つカラム同士を比較・結合する際に発生するエラーである。

# エラーメッセージ例

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT)
and (utf8mb4_general_ci,IMPLICIT) for operation '='

原因

異なる照合順序を持つカラムを比較している。

 -- table1.name: utf8mb4_unicode_ci
 -- table2.name: utf8mb4_general_ci
 
 SELECT *
 FROM table1
 JOIN table2 ON table1.name = table2.name;
 -- エラー発生

対処方法1 : COLLATE句で明示的に照合順序を指定

 SELECT *
 FROM table1
 JOIN table2 ON table1.name = table2.name COLLATE utf8mb4_unicode_ci;

対処方法2 : カラムの照合順序を統一

 ALTER TABLE table2
    MODIFY name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

対処方法3 : データベース全体の照合順序を統一

 -- 全テーブルを同じ照合順序に変換
 
 ALTER DATABASE database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
 
 ALTER TABLE table1
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
 
 ALTER TABLE table2
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

診断クエリ

照合順序の不一致を確認するクエリを以下に示す。

 -- 全カラムの照合順序を確認
 SELECT TABLE_NAME,
        COLUMN_NAME,
        COLLATION_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'database_name'
   AND COLLATION_NAME IS NOT NULL
 ORDER BY TABLE_NAME, ORDINAL_POSITION;
 
 -- 照合順序が混在しているカラムを検出
 SELECT TABLE_NAME,
        COLUMN_NAME,
        COLLATION_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'database_name'
   AND COLLATION_NAME NOT IN ('utf8mb4_unicode_ci')
   AND COLLATION_NAME IS NOT NULL;

文字コードの変換エラー

文字コード変換時に、変換できない文字が含まれている場合にエラーが発生する。

# エラーメッセージ例

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8D\xA3' for column 'name' at row 1

原因

utf8mb3カラムに絵文字 (4バイト文字) を挿入しようとした場合等に発生する。

対処方法1 : カラムをutf8mb4に変換

 ALTER TABLE table_name
    MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

対処方法2: 変換できない文字を削除・置換

 -- 絵文字を削除
 
 UPDATE table_name
 SET column_name = REGEXP_REPLACE(column_name, '[^\u0000-\uFFFF]', '');

または、アプリケーション側で絵文字を事前に削除・置換する。

対処方法3: sql_modeを一時的に緩和

 -- エラーを無視して変換
 
 SET sql_mode = '';
 ALTER TABLE table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
 SET sql_mode = DEFAULT;

※注意
sql_mode を緩和すると、変換できない文字が切り捨てられる可能性がある。
本番環境では推奨されない。

診断クエリ

変換できない文字を含む行を検出するクエリを以下に示す。

 -- 4バイト文字 (絵文字等) を含む行を検出
 
 SELECT id,
        column_name,
        HEX(column_name) AS hex_value
 FROM table_name
 WHERE LENGTH(column_name) != CHAR_LENGTH(column_name) * 3;
 
 -- または、正規表現で検出 (MySQL 8.0以降)
 
 SELECT id,
        column_name
 FROM table_name
 WHERE column_name REGEXP '[^\u0000-\uFFFF]';

絵文字が保存できない場合

絵文字が保存できない、または、文字化けする場合の対処方法を以下に示す。

原因1 : カラムの文字コードがutf8mb3

対処方法を以下に示す。

 ALTER TABLE table_name
    MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

原因2 : 接続の文字コードがutf8mb3

対処方法を以下に示す。

 SET NAMES 'utf8mb4';

または、アプリケーションの接続設定で utf8mb4 を指定する。

原因3 : データベースの文字コードがutf8mb3

対処方法を以下に示す。

 ALTER DATABASE database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
 
 ALTER TABLE table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

原因4 : サーバのデフォルトがutf8mb3

対処方法を以下に示す。

 # my.cnf
 
 [mysqld]
 character-set-server=utf8mb4
 collation-server=utf8mb4_0900_ai_ci
 
 [client]
 default-character-set=utf8mb4

MySQLサーバを再起動する。

動作確認

絵文字が正しく保存・取得できるか確認する。

 -- テストテーブル作成
 CREATE TABLE emoji_test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    text VARCHAR(255)
 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
 -- 接続の文字コードを設定
 SET NAMES 'utf8mb4';
 
 -- 絵文字を挿入
 INSERT INTO emoji_test (text) VALUES ('🍣🍺😀🎉👍');
 
 -- 確認
 SELECT id, text, HEX(text) AS hex_value
 FROM emoji_test;

絵文字が正しく表示され、HEX(text) に4バイトの絵文字が含まれていれば成功である。