MySQL - 文字コードと照合順序のソースを表示

== 概要 ==
MySQLにおける文字コード (CHARACTER SET) と照合順序 (COLLATION) は、データベースにおけるテキストデータの保存方法、比較方法、ソート方法を制御する重要な設定である。<br>
<br>
文字コードは、文字をバイト列としてどのように表現するかを定義し、照合順序は文字列の比較・ソート規則を定義する。<br>
<br>
MySQL 8.0以降では、デフォルトの文字コードが <code>utf8mb4</code> に変更され、デフォルトの照合順序が <code>utf8mb4_0900_ai_ci</code> となった。<br>
これにより、絵文字を含む全Unicode文字の正しい保存・比較が可能となった。<br>
<br>
文字コードと照合順序は、サーバレベル、データベースレベル、テーブルレベル、カラムレベルでそれぞれ設定でき、下位レベルが設定されていない場合は上位レベルの設定が継承される。<br>
<br>
また、既存のテーブルやカラムの文字コード変換、utf8mb3から utf8mb4への移行手順、文字化けやコード変換エラーのトラブルシューティングについても理解する必要がある。<br>
<br><br>

== 文字コードの基本概念 ==
==== 文字コードとは ====
文字コード (CHARACTER SET または CHARSET) は、文字をコンピュータ内部でバイト列として表現するための規則である。<br>
MySQLでは、各カラム、テーブル、データベース、サーバに対して文字コードを設定できる。<br>
<br>
文字コードは、以下の要素を定義する。<br>
* 文字の表現方法
*: 文字をバイト列に変換する方法
* 最大バイト長
*: 1文字を表現するために必要な最大バイト数
* 文字のレパートリー
*: 表現可能な文字の集合
<br>
MySQLでは、各文字コードに対して1つ以上の照合順序が定義されており、文字コードを設定すると同時にデフォルトの照合順序も決定される。<br>
<br>
==== MySQLでサポートされる文字コード ====
MySQLは、多数の文字コードをサポートしている。<br>
下表に、主要な文字コードを示す。<br>
<br>
<center>
{| class="wikitable"
|+ MySQLでサポートされる主要な文字コード
! 文字コード !! 説明 !! 最大バイト長 !! 備考
|-
| <code>utf8mb4</code> || 4バイトUTF-8<br>全Unicode文字をサポート || 4 || MySQL 8.0以降のデフォルト<br>絵文字・拡張文字対応
|-
| <code>utf8mb3</code> (utf8) || 3バイトUTF-8<br>BMP (基本多言語面) のみ || 3 || MySQL 8.0.28以降は非推奨<br>絵文字は保存不可
|-
| <code>utf8</code> || utf8mb3のエイリアス || 3 || 将来的にutf8mb4に変更予定
|-
| <code>utf16</code> || UTF-16エンコーディング || 4 || BOM不要、ビッグエンディアン
|-
| <code>utf16le</code> || UTF-16LE (リトルエンディアン) || 4 || リトルエンディアン版
|-
| <code>utf32</code> || UTF-32エンコーディング || 4 || 全文字が4バイト固定
|-
| <code>latin1</code> || ISO-8859-1 (西欧言語) || 1 || MySQL 5.7以前のデフォルト
|-
| <code>latin2</code> || ISO-8859-2 (中欧言語) || 1 || チェコ語、ハンガリー語等
|-
| <code>ascii</code> || US-ASCII || 1 || 7ビット文字セット
|-
| <code>binary</code> || バイナリデータ || 1 || 文字コード変換なし
|-
| <code>cp932</code> || Windows-31J (Shift_JIS) || 2 || 日本語Windows環境
|-
| <code>eucjpms</code> || EUC-JP (拡張版) || 3 || MySQL拡張EUC-JP
|-
| <code>ujis</code> || EUC-JP || 3 || 標準EUC-JP
|-
| <code>sjis</code> || Shift_JIS || 2 || 日本語Shift_JIS
|-
| <code>gb18030</code> || GB18030 (中国語) || 4 || 中国国家標準
|-
| <code>gbk</code> || GBK (中国語) || 2 || GB2312の拡張
|-
| <code>gb2312</code> || GB2312 (簡体字中国語) || 2 || 簡体字中国語
|-
| <code>big5</code> || Big5 (繁体字中国語) || 2 || 台湾・香港で使用
|-
| <code>euckr</code> || EUC-KR (韓国語) || 2 || 韓国語KS X 1001
|-
| <code>tis620</code> || TIS-620 (タイ語) || 1 || タイ語
|-
| <code>ucs2</code> || UCS-2 (BMP範囲のみ) || 2 || BMP範囲のみの2バイト固定
|-
| <code>armscii8</code> || ARMSCII-8 (アルメニア語) || 1 || アルメニア語
|-
| <code>greek</code> || ISO-8859-7 (ギリシャ語) || 1 || ギリシャ語
|-
| <code>hebrew</code> || ISO-8859-8 (ヘブライ語) || 1 || ヘブライ語
|}
</center>
<br>
現在の推奨は <code>utf8mb4</code> である。<br>
<code>utf8mb4</code> は、全Unicode文字を正しく保存でき、絵文字や拡張漢字も扱える。<br>
<br>
==== utf8mb4とutf8 (utf8mb3) の違い ====
MySQLには、<code>utf8</code> (utf8mb3) と <code>utf8mb4</code> という2つのUTF-8文字コードが存在する。<br>
両者の違いを理解することは重要である。<br>
<br>
<center>
{| class="wikitable"
|+ 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バイト/文字 (最大)
|-
| インデックス長制限 || 少ない制限 || より大きい制限 (考慮が必要)
|-
| パフォーマンス || わずかに高速 || わずかに低速 (実用上は無視可能)
|-
| 推奨度 || 非推奨 (レガシー環境以外) || 推奨 (新規開発は必須)
|}
</center>
<br>
MySQL 8.0.28以降では、<code>utf8mb3</code> の使用時に非推奨警告が表示される。<br>
新規開発では、必ず <code>utf8mb4</code> を使用すべきである。<br>
<br>
<code>utf8</code> というエイリアスは、現在は <code>utf8mb3</code> を指すが、将来的には <code>utf8mb4</code> を指すように変更される予定である。<br>
そのため、明示的に <code>utf8mb4</code> または <code>utf8mb3</code> を指定することが推奨される。<br>
<br><br>

== 照合順序の基本概念 ==
==== 照合順序とは ====
照合順序 (COLLATION) は、文字列の比較・ソート規則を定義する。<br>
同じ文字コードでも、照合順序によって比較結果やソート順が異なる。<br>
<br>
照合順序は、以下の動作に影響する。<br>
* <code>WHERE</code> 句の文字列比較
* <code>ORDER BY</code> 句のソート順
* <code>GROUP BY</code> 句のグルーピング
* <code>DISTINCT</code> の重複判定
* インデックスの並び順
* 文字列関数の動作 (LIKE、UPPER、LOWER等)
<br>
照合順序の命名規則は、以下の形式である。<br>
* <code>charset_language_suffix</code>
<br>
サフィックスの意味を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ 照合順序のサフィックス
|-
! サフィックス !! 説明
|-
| <code>_ci</code> || Case Insensitive (大文字小文字を区別しない)
|-
| <code>_cs</code> || Case Sensitive (大文字小文字を区別する)
|-
| <code>_bin</code> || Binary (バイナリ比較、厳密な比較)
|-
| <code>_ai</code> || Accent Insensitive (アクセント記号を区別しない、MySQL 8.0以降)
|-
| <code>_as</code> || Accent Sensitive (アクセント記号を区別する、MySQL 8.0以降)
|-
| <code>_ks</code> || Kana Sensitive (ひらがな・カタカナを区別する、MySQL 8.0以降)
|}
</center>
<br>
例として、<u>utf8mb4_0900_ai_ci</u> は、以下を意味する。<br>
<br>
<center>
{| class="wikitable"
|+ utf8mb4_0900_ai_ciの命名規則
|-
! 要素 !! 説明
|-
| <u>utf8mb4</u> || 文字コード
|-
| <u>0900</u> || Unicode Collation Algorithm (UCA) 9.0.0準拠
|-
| <u>ai</u> || Accent Insensitive (アクセント記号を区別しない)
|-
| <u>ci</u> || Case Insensitive (大文字小文字を区別しない)
|}
</center>
<br>
==== 主要な照合順序の一覧 ====
MySQLでは、各文字コードに対して複数の照合順序が用意されている。<br>
下表に、<u>utf8mb4</u> の主要な照合順序を示す。<br>
<br>
<center>
{| class="wikitable"
|+ utf8mb4の主要な照合順序
! 照合順序 !! 説明 !! MySQL !! 備考
|-
| utf8mb4_general_ci || シンプルな大文字小文字非区別比較 || 5.5以降 || 高速だが、一部の言語で不正確<br>デフォルト (MySQL 5.7以前)
|-
| utf8mb4_unicode_ci || Unicode標準に基づく比較 || 5.5以降 || より正確だが、やや低速<br>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以降)<br>UCA 9.0.0準拠<br>高速かつ正確
|-
| 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準拠<br>日本語のソート順
|-
| 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以降 || ベトナム語特有のソート順
|}
</center>
<br>
<u>MySQL 8.0以降では、utf8mb4_0900_ai_ciがデフォルトの照合順序である。</u><br>
<u>この照合順序は、高速かつ正確で、多くの言語に対応している。</u><br>
<br>
<u>日本語データを扱う場合は、<u>utf8mb4_ja_0900_as_cs</u> または <u>utf8mb4_ja_0900_as_cs_ks</u> の使用を検討する。</u><br>
<br>
==== 照合順序の比較動作 ====
照合順序によって、文字列比較の結果が異なる。<br>
具体的な例を以下に示す。<br>
<br>
* 大文字小文字の区別
*: <syntaxhighlight lang="mysql">
 -- 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)
 </syntaxhighlight>
*: <br>
* アクセント記号の区別
*: <syntaxhighlight lang="mysql">
 -- 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)
 </syntaxhighlight>
*: <br>
* ひらがな・カタカナの区別
*: <syntaxhighlight lang="mysql">
 -- 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)
 </syntaxhighlight>
*: <br>
* ソート順の違い
*: <syntaxhighlight lang="mysql">
 -- 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 (バイト値順、大文字が先)
 </syntaxhighlight>
<br><br>

== サーバレベルの設定 ==
==== サーバの文字コードと照合順序 ====
MySQLサーバ全体のデフォルト文字コードと照合順序は、システム変数で設定される。<br>
<br>
主要なシステム変数を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ 主要なシステム変数
|-
! 変数名 !! 説明
|-
| <code>character_set_server</code> || サーバのデフォルト文字コード
|-
| <code>collation_server</code> || サーバのデフォルト照合順序
|-
| <code>character_set_database</code> || 現在のデータベースの文字コード
|-
| <code>collation_database</code> || 現在のデータベースの照合順序
|}
</center>
<br>
サーバの文字コード設定を確認する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW VARIABLES LIKE 'character_set_server';
 SHOW VARIABLES LIKE 'collation_server';
 </syntaxhighlight>
<br>
MySQL 8.0以降のデフォルト値を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ MySQL 8.0以降のデフォルト値
|-
! 変数名 !! デフォルト値
|-
| <code>character_set_server</code> || <code>utf8mb4</code>
|-
| <code>collation_server</code> || <code>utf8mb4_0900_ai_ci</code>
|}
</center>
<br>
MySQL 5.7以前のデフォルト値を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ MySQL 5.7以前のデフォルト値
|-
! 変数名 !! デフォルト値
|-
| <code>character_set_server</code> || <code>latin1</code>
|-
| <code>collation_server</code> || <code>latin1_swedish_ci</code>
|}
</center>
<br>
==== my.cnfでの設定 ====
サーバのデフォルト文字コードと照合順序は、設定ファイル <u>my.cnf</u> (または <u>my.ini</u>) で設定できる。<br>
<br>
 <syntaxhighlight lang="ini">
 # my.cnf

 [mysqld]
 character-set-server=utf8mb4
 collation-server=utf8mb4_0900_ai_ci
 
 [client]
 default-character-set=utf8mb4
 </syntaxhighlight>
<br>
<u>[mysqld]</u> セクションは、MySQLサーバの設定である。<br>
<u>[client]</u> セクションは、MySQLクライアント (mysql コマンド等) の設定である。<br>
<br>
設定ファイルを変更した後は、MySQLサーバを再起動する必要がある。<br>
<br>
 # systemd環境
 sudo systemctl restart mysqld
 ## または
 sudo systemctl restart mysql
<br>
レガシー環境からの移行時には、以下に示す設定も追加する。<br>
<br>
 <syntaxhighlight lang="ini">
 # my.cnf - レガシークライアント対策
 
 [mysqld]
 character-set-server=utf8mb4
 collation-server=utf8mb4_0900_ai_ci
 
 # クライアントハンドシェイクを無視 (全接続をutf8mb4で処理)
 skip-character-set-client-handshake
 </syntaxhighlight>
<br>
<code>skip-character-set-client-handshake</code> オプションは、クライアントの文字コード設定を無視し、サーバの文字コードを強制する。<br>
レガシーアプリケーションが誤った文字コードを要求する場合に有効である。<br>
<br>
==== 接続時の文字コード ====
クライアントがMySQLサーバに接続する際、文字コード関連の変数が設定される。<br>
<br>
下表に、接続時の文字コード関連変数を示す。<br>
<br>
<center>
{| class="wikitable"
|+ 接続時の文字コード関連変数
! 変数名 !! 説明 !! 影響範囲
|-
| <code>character_set_client</code> || クライアントが送信するSQLステートメントの文字コード || クライアント → サーバ
|-
| <code>character_set_connection</code> || サーバがSQLステートメントを解釈する際の文字コード || サーバ内部処理
|-
| <code>character_set_results</code> || サーバがクライアントに返す結果セットの文字コード || サーバ → クライアント
|-
| <code>character_set_database</code> || 現在のデータベースの文字コード || データベース単位
|-
| <code>character_set_server</code> || サーバのデフォルト文字コード || サーバ全体
|}
</center>
<br>
これらの変数を確認する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW VARIABLES LIKE 'character_set%';
 </syntaxhighlight>
<br>
出力例を以下に示す。<br>
<br>
 +--------------------------+--------------------------------+
 | 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                        |
 +--------------------------+--------------------------------+
<br>
<code>SET NAMES</code> ステートメントは、3つの変数を一括設定する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SET NAMES 'utf8mb4';
 
 -- 上記は、以下と同等
 
 SET character_set_client = 'utf8mb4';
 SET character_set_connection = 'utf8mb4';
 SET character_set_results = 'utf8mb4';
 </syntaxhighlight>
<br>
照合順序も同時に指定できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 SET NAMES 'utf8mb4' COLLATE 'utf8mb4_ja_0900_as_cs';
 </syntaxhighlight>
<br>
<code>SET CHARACTER SET</code> ステートメントとの違いを以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SET CHARACTER SET 'utf8mb4';
 
 -- 上記は、以下と同等
 
 SET character_set_client = 'utf8mb4';
 SET character_set_results = 'utf8mb4';
 SET character_set_connection = @@character_set_database;
 </syntaxhighlight>
<br>
<code>SET CHARACTER SET</code> は、<code>character_set_connection</code> をデータベースのデフォルトに設定する点が異なる。<br>
通常は、<code>SET NAMES</code> を使用することが推奨される。<br>
<br><br>

== データベースレベルの設定 ==
==== データベースの文字コード指定 ====
データベース作成時に、文字コードと照合順序を指定できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE DATABASE database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;
 </syntaxhighlight>
<br>
照合順序を省略した場合は、文字コードのデフォルト照合順序が使用される。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE DATABASE database_name
    CHARACTER SET utf8mb4;
 -- 照合順序は utf8mb4_0900_ai_ci (MySQL 8.0のデフォルト)
 </syntaxhighlight>
<br>
文字コードも照合順序も省略した場合は、サーバのデフォルトが使用される。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE DATABASE database_name;
 -- character_set_server と collation_server が使用される
 </syntaxhighlight>
<br>
既存データベースの文字コードと照合順序を変更する。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER DATABASE database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u><code>ALTER DATABASE</code> は、データベースのデフォルト設定のみを変更し、既存テーブルの文字コードは変更しない。</u><br>
<u>既存テーブルの文字コードを変更するには、個別に <code>ALTER TABLE</code> を実行する必要がある。</u><br>
<br>
==== データベースの文字コード確認 ====
データベースの文字コードと照合順序を確認する方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 方法1: SHOW CREATE DATABASE
 
 SHOW CREATE DATABASE database_name;
 </syntaxhighlight>
<br>
出力例を以下に示す。<br>
<br>
 +---------------+--------------------------------------------------------------------------+
 | Database      | Create Database                                                          |
 +---------------+--------------------------------------------------------------------------+
 | database_name | CREATE DATABASE `database_name`                                          |
 |               | /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */    |
 +---------------+--------------------------------------------------------------------------+
<br>
 <syntaxhighlight lang="mysql">
 -- 方法2: INFORMATION_SCHEMA.SCHEMATA
 
 SELECT SCHEMA_NAME,
        DEFAULT_CHARACTER_SET_NAME,
        DEFAULT_COLLATION_NAME
 FROM INFORMATION_SCHEMA.SCHEMATA
 WHERE SCHEMA_NAME = 'database_name';
 </syntaxhighlight>
<br>
 <syntaxhighlight lang="mysql">
 -- 方法3: システム変数 (現在のデータベース)
 
 USE database_name;
 SHOW VARIABLES LIKE 'character_set_database';
 SHOW VARIABLES LIKE 'collation_database';
 </syntaxhighlight>
<br>
全データベースの文字コード設定を一覧表示する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT SCHEMA_NAME,
        DEFAULT_CHARACTER_SET_NAME,
        DEFAULT_COLLATION_NAME
 FROM INFORMATION_SCHEMA.SCHEMATA
 ORDER BY SCHEMA_NAME;
 </syntaxhighlight>
<br><br>

== テーブル・カラムレベルの設定 ==
==== テーブルの文字コード指定 ====
テーブル作成時に、文字コードと照合順序を指定できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
 ) CHARACTER SET utf8mb4
   COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
<br>
テーブルの文字コードを省略した場合は、データベースのデフォルトが使用される。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(100)
 );
 -- データベースの文字コードと照合順序が継承される
 </syntaxhighlight>
<br>
既存テーブルのデフォルト文字コードを変更する (既存カラムは変更されない)。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>この構文は、テーブルのデフォルト文字コードのみを変更し、既存カラムの文字コードは変更しない。</u><br>
<u>新しく追加されるカラムには、変更後の文字コードが適用される。</u><br>
<br>
==== カラムの文字コード指定 ====
カラム単位で文字コードと照合順序を指定できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 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
 );
 </syntaxhighlight>
<br>
カラムの文字コードを省略した場合は、テーブルのデフォルトが使用される。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
 ) CHARACTER SET utf8mb4;
 -- 各カラムは utf8mb4 を継承
 </syntaxhighlight>
<br>
既存カラムの文字コードを変更する。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
    MODIFY name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u><code>MODIFY</code> 句を使用する場合は、カラムの型 (VARCHAR、TEXT等) も再指定する必要がある。</u><br>
<br>
カラムの文字コードを確認する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT COLUMN_NAME,
        CHARACTER_SET_NAME,
        COLLATION_NAME,
        COLUMN_TYPE
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'database_name'
   AND TABLE_NAME = 'table_name';
 </syntaxhighlight>
<br>
==== 文字コードの継承ルール ====
文字コードと照合順序は、階層的に継承される。<br>
下表に、継承ルールを示す。<br>
<br>
<center>
{| class="wikitable"
|+ 文字コードの継承ルール
! レベル !! 設定項目 !! 継承元 !! 優先度
|-
| サーバ || <code>character_set_server</code><br><code>collation_server</code> || my.cnfまたはコマンドライン引数 || 最低 (最も広範囲)
|-
| データベース || <code>DEFAULT CHARACTER SET</code><br><code>DEFAULT COLLATE</code> || サーバのデフォルト (未指定時) || 低
|-
| テーブル || <code>CHARACTER SET</code><br><code>COLLATE</code> || データベースのデフォルト (未指定時) || 中
|-
| カラム || <code>CHARACTER SET</code><br><code>COLLATE</code> || テーブルのデフォルト (未指定時) || 最高 (最も優先)
|}
</center>
<br>
継承の具体例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- サーバのデフォルト: 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 (テーブルから継承)
 </syntaxhighlight>
<br><br>

== 文字コードの確認方法 ==
==== システム変数の確認 ====
MySQLの文字コード関連のシステム変数を確認する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW VARIABLES LIKE 'character%';
 SHOW VARIABLES LIKE 'collation%';
 </syntaxhighlight>
<br>
出力例を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ 文字コード関連のシステム変数
! 変数名 !! 値 (例) !! 説明
|-
| <code>character_set_client</code> || utf8mb4 || クライアントが送信するSQLの文字コード 
|-
| <code>character_set_connection</code> || utf8mb4 || サーバがSQLを解釈する際の文字コード
|-
| <code>character_set_database</code> || utf8mb4 || 現在のデータベースの文字コード
|-
| <code>character_set_filesystem</code> || binary || ファイルシステム上のファイル名の文字コード
|-
| <code>character_set_results</code> || utf8mb4 || サーバが返す結果セットの文字コード
|-
| <code>character_set_server</code> || utf8mb4 || サーバのデフォルト文字コード
|-
| <code>character_set_system</code> || utf8mb3 || システムメタデータの文字コード (固定)
|-
| <code>collation_connection</code> || utf8mb4_0900_ai_ci || 接続の照合順序 
|-
| <code>collation_database</code> || utf8mb4_0900_ai_ci || 現在のデータベースの照合順序
|-
| <code>collation_server</code> || utf8mb4_0900_ai_ci || サーバのデフォルト照合順序
|}
</center>
<br>
<u>※注意</u><br>
<u><code>character_set_system</code> は、システムメタデータ (テーブル名、カラム名等) の文字コードであり、<u>utf8mb3</u> 固定である。</u><br>
<u>この値は変更できない。</u><br>
<br>
==== データベース・テーブル・カラムの確認 ====
データベース、テーブル、カラムの文字コード設定を確認する方法を以下に示す。<br>
<br>
データベースの文字コードを確認する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT SCHEMA_NAME,
        DEFAULT_CHARACTER_SET_NAME,
        DEFAULT_COLLATION_NAME
 FROM INFORMATION_SCHEMA.SCHEMATA
 WHERE SCHEMA_NAME = 'database_name';
 </syntaxhighlight>
<br>
テーブルの文字コードを確認する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT TABLE_NAME,
        TABLE_COLLATION
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'database_name';
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u><code>TABLE_COLLATION</code> カラムは照合順序を示し、文字コードは照合順序の接頭辞から判別できる。</u><br>
<u>例えば、utf8mb4_unicode_ciの文字コードは、utf8mb4である。</u><br>
<br>
* カラムの文字コードを確認する。
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
*: <br>
* 特定のテーブルの詳細を確認する。
*: <syntaxhighlight lang="mysql">
 SHOW CREATE TABLE <テーブル名>;
 </syntaxhighlight>
*: <br>
*: 出力例を以下に示す。
*: <syntaxhighlight lang="text">
 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
 </syntaxhighlight>
*: <br>
* 全データベースの文字コード設定を一覧表示する。
*: <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br><br>

== 文字コードの変更・変換 ==
==== サーバレベルの変更 ====
サーバレベルの文字コードを変更する場合は、<u>my.cnf</u> ファイルを編集する。<br>
<br>
 <syntaxhighlight lang="ini">
 # my.cnf
 
 [mysqld]
 character-set-server=utf8mb4
 collation-server=utf8mb4_0900_ai_ci
 
 [client]
 default-character-set=utf8mb4
 </syntaxhighlight>
<br>
設定ファイルを変更した後は、MySQLサーバを再起動する。<br>
<br>
 <syntaxhighlight lang="bash">
 sudo systemctl restart mysqld
 </syntaxhighlight>
<br>
再起動後、設定が反映されているか確認する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW VARIABLES LIKE 'character_set_server';
 SHOW VARIABLES LIKE 'collation_server';
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>サーバレベルの設定変更は、新規作成されるデータベースにのみ影響する。</u><br>
<u>既存のデータベース、テーブル、カラムの文字コードは変更されない。</u><br>
<br>
==== データベースレベルの変更 ====
既存データベースのデフォルト文字コードを変更する。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER DATABASE database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>この操作は、データベースのデフォルト設定のみを変更する。</u><br>
<u>既存テーブルの文字コードは変更されない。</u><br>
<u>新しく作成されるテーブルには、変更後の文字コードが適用される。</u><br>
<br>
既存テーブルの文字コードを変更するには、個別に <code>ALTER TABLE</code> を実行する必要がある。<br>
<br>
==== テーブル・カラムの変換 ====
既存テーブルとカラムの文字コードを変換する方法を以下に示す。<br>
<br>
* テーブルのデフォルト文字コードのみを変更する。(既存カラムは変更されない)
*: <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
*: <br>
* 全カラムの文字コードを一括変換する。(データも変換される)
*: <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
*: <br>
*: <u>※注意</u>
*: <u><code>CONVERT TO CHARACTER SET</code> は、全ての文字カラムのデータを実際に変換する。</u>
*: <u>大量のデータがある場合、この操作には時間が掛かる。</u>
*: <u>また、テーブルロックが発生するため、本番環境では注意が必要である。</u>
*: <br>
* 特定のカラムのみを変換する。
*: <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
    MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
*: <br>
*: <u>※注意</u>
*: <u><code>MODIFY</code> 句を使用する場合は、カラムの型も再指定する必要がある。</u>
*: <br>
* バイナリカラムを経由して安全に変換する方法
*: この方法は、既存データが既にutf8mb4として保存されているが、カラム定義がlatin1等の異なる文字コードになっている場合に有効である。
*: バイナリ型を経由することで、文字コード変換を回避し、バイト列をそのまま保持することができる。
*: <syntaxhighlight lang="mysql">
 -- ステップ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;
 </syntaxhighlight>
<br>
==== utf8mb3からutf8mb4への移行手順 ====
utf8mb3 (utf8) からutf8mb4への移行は、以下の手順で実施する。<br>
<br>
===== ステップ1 : バックアップ =====
移行前に、必ずデータベースの完全バックアップを取得する。<br>
<br>
 <syntaxhighlight lang="bash">
 mysqldump -u root -p --databases database_name > backup.sql
 </syntaxhighlight>
<br>
===== ステップ2 : インデックス長の確認 =====
utf8mb4は1文字あたり最大4バイトを使用するため、インデックス長に制限がある。<br>
InnoDBのインデックス最大長は767バイト (MySQL 5.7以前) または 3072バイト (MySQL 8.0以降) である。<br>
<br>
問題のあるインデックスを確認する。<br>
<br>
 <syntaxhighlight lang="mysql">
 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;
 </syntaxhighlight>
<br>
VARCHAR(255) を超えるカラムにインデックスがある場合は、カラム長を短縮するか、インデックスのプレフィックス長を指定する必要がある。<br>
<br>
===== ステップ3 : サーバ設定の変更 =====
<u>my.cnf</u> ファイルを編集する。<br>
<br>
 <syntaxhighlight lang="ini">
 # 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
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>MySQL 8.0以降では、<code>innodb_large_prefix</code> と <code>innodb_file_format</code> は非推奨となり、デフォルトで有効である。</u><br>
<br>
MySQLサーバを再起動する。<br>
<br>
 sudo systemctl restart mysqld
<br>
===== ステップ4 : データベースの変換 =====
データベースのデフォルト文字コードを変更する。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER DATABASE database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;
 </syntaxhighlight>
<br>
===== ステップ5 : テーブルの変換 =====
各テーブルを変換する。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;
 </syntaxhighlight>
<br>
全テーブルを一括変換するスクリプト例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sh">
 #!/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."
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>大量のデータがある場合、テーブル変換には時間が掛かる。</u><br>
<u>本番環境では、メンテナンスウィンドウ中に実施するか、オンラインスキーマ変更ツール (pt-online-schema-change等) の使用を検討する。</u><br>
<br>
===== ステップ6 : アプリケーションの接続設定 =====
アプリケーションの接続設定で、<code>SET NAMES 'utf8mb4'</code> を実行するように変更する。<br>
<br>
* PHPの例
*: <syntaxhighlight lang="php">
 <?php
    $mysqli = new mysqli('localhost', 'user', 'password', 'database');
    $mysqli->set_charset('utf8mb4');
 ?>
 </syntaxhighlight>
*: <br>
* Pythonの例
*: <syntaxhighlight lang="python">
 import mysql.connector
 
 conn = mysql.connector.connect(
    host='localhost',
    user='user',
    password='password',
    database='database',
    charset='utf8mb4',
    collation='utf8mb4_0900_ai_ci'
 )
 </syntaxhighlight>
<br>
===== ステップ7 : 動作確認 =====
絵文字が正しく保存・取得できるか確認する。<br>
<br>
 <syntaxhighlight lang="mysql">
 INSERT INTO test_table (text_column) VALUES ('🍣🍺😀');
 SELECT text_column FROM test_table;
 </syntaxhighlight>
<br>
文字化けが発生せず、絵文字が正しく表示されれば移行成功である。<br>
<br><br>

== トラブルシューティング ==
==== 文字化けの原因と対処 ====
<u>文字化けは、文字コードの不一致によって発生する。</u><br>
主な原因と対処方法を以下に示す。<br>
<br>
===== 原因1 : クライアント接続時の文字コード不一致 =====
クライアントがサーバに送信するSQLの文字コードとサーバが期待する文字コードが異なる場合に発生する。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 接続直後に実行
 
 SET NAMES 'utf8mb4';
 </syntaxhighlight>
<br>
または、アプリケーションの接続設定で文字コードを指定する。<br>
<br>
 <syntaxhighlight lang="php">
 <?php
    $mysqli = new mysqli('localhost', 'user', 'password', 'database');
    $mysqli->set_charset('utf8mb4');
 ?>
 </syntaxhighlight>
<br>
===== 原因2 : カラムの文字コードとデータの文字コードの不一致 =====
カラムの定義がlatin1だが、実際のデータはutf8mb4として保存されている場合等に発生する。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- バイナリカラムを経由して変換
 
 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;
 </syntaxhighlight>
<br>
===== 原因3 : ダンプファイルの文字コード不一致 =====
mysqldumpで取得したダンプファイルの文字コードと、リストア先の文字コードが異なる場合に発生する。<br>
<br>
 <syntaxhighlight lang="bash">
 # ダンプ取得時に文字コードを指定
 mysqldump -u root -p --default-character-set=utf8mb4 database_name > dump.sql
 
 # リストア時に文字コードを指定
 mysql -u root -p --default-character-set=utf8mb4 database_name < dump.sql
 </syntaxhighlight>
<br>
===== 原因4 : ターミナルの文字コード設定 =====
mysqlクライアントを使用する際、ターミナルの文字コード設定が異なる場合に発生する。<br>
<br>
* Linuxの場合
*: 環境変数 <code>LANG</code> を確認して、<u>ja_JP.UTF-8</u> 等に設定する。
* Windowsの場合
*: コマンドプロンプトのプロパティでフォントを変更して、<u>chcp 65001</u> でUTF-8に設定する。
<br>
===== 診断クエリ =====
文字化けの原因を診断するクエリを以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 文字コード関連の変数を確認
 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;
 </syntaxhighlight>
<br>
<code>LENGTH()</code> 関数はバイト数、<code>CHAR_LENGTH()</code> 関数は文字数を返す。<br>
utf8mb4の場合、日本語1文字は3バイトまたは4バイトである。<br>
<br>
==== Illegal mix of collationsエラー ====
異なる照合順序を持つカラム同士を比較・結合する際に発生するエラーである。<br>
<br>
 # エラーメッセージ例

 ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT)
 and (utf8mb4_general_ci,IMPLICIT) for operation '='
<br>
===== 原因 =====
異なる照合順序を持つカラムを比較している。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- table1.name: utf8mb4_unicode_ci
 -- table2.name: utf8mb4_general_ci
 
 SELECT *
 FROM table1
 JOIN table2 ON table1.name = table2.name;
 -- エラー発生
 </syntaxhighlight>
<br>
===== 対処方法1 : COLLATE句で明示的に照合順序を指定 =====
 <syntaxhighlight lang="mysql">
 SELECT *
 FROM table1
 JOIN table2 ON table1.name = table2.name COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
<br>
===== 対処方法2 : カラムの照合順序を統一 =====
 <syntaxhighlight lang="mysql">
 ALTER TABLE table2
    MODIFY name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
<br>
===== 対処方法3 : データベース全体の照合順序を統一 =====
 <syntaxhighlight lang="mysql">
 -- 全テーブルを同じ照合順序に変換
 
 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;
 </syntaxhighlight>
<br>
===== 診断クエリ =====
照合順序の不一致を確認するクエリを以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 全カラムの照合順序を確認
 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;
 </syntaxhighlight>
<br>
==== 文字コードの変換エラー ====
文字コード変換時に、変換できない文字が含まれている場合にエラーが発生する。<br>
<br>
 # エラーメッセージ例
 
 ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8D\xA3' for column 'name' at row 1
<br>
===== 原因 =====
utf8mb3カラムに絵文字 (4バイト文字) を挿入しようとした場合等に発生する。<br>
<br>
===== 対処方法1 : カラムをutf8mb4に変換 =====
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
    MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
<br>
===== 対処方法2: 変換できない文字を削除・置換 =====
 <syntaxhighlight lang="mysql">
 -- 絵文字を削除
 
 UPDATE table_name
 SET column_name = REGEXP_REPLACE(column_name, '[^\u0000-\uFFFF]', '');
 </syntaxhighlight>
<br>
または、アプリケーション側で絵文字を事前に削除・置換する。<br>
<br>
===== 対処方法3: sql_modeを一時的に緩和 =====
 <syntaxhighlight lang="mysql">
 -- エラーを無視して変換
 
 SET sql_mode = '';
 ALTER TABLE table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
 SET sql_mode = DEFAULT;
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u><code>sql_mode</code> を緩和すると、変換できない文字が切り捨てられる可能性がある。</u><br>
<u>本番環境では推奨されない。</u><br>
<br>
===== 診断クエリ =====
変換できない文字を含む行を検出するクエリを以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 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]';
 </syntaxhighlight>
<br>
==== 絵文字が保存できない場合 ====
絵文字が保存できない、または、文字化けする場合の対処方法を以下に示す。<br>
<br>
===== 原因1 : カラムの文字コードがutf8mb3 =====
対処方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER TABLE table_name
    MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
<br>
===== 原因2 : 接続の文字コードがutf8mb3 =====
対処方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SET NAMES 'utf8mb4';
 </syntaxhighlight>
<br>
または、アプリケーションの接続設定で <u>utf8mb4</u> を指定する。<br>
<br>
===== 原因3 : データベースの文字コードがutf8mb3 =====
対処方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER DATABASE database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
 
 ALTER TABLE table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
<br>
===== 原因4 : サーバのデフォルトがutf8mb3 =====
対処方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="ini">
 # my.cnf
 
 [mysqld]
 character-set-server=utf8mb4
 collation-server=utf8mb4_0900_ai_ci
 
 [client]
 default-character-set=utf8mb4
 </syntaxhighlight>
<br>
MySQLサーバを再起動する。<br>
<br>
===== 動作確認 =====
絵文字が正しく保存・取得できるか確認する。<br>
<br>
 <syntaxhighlight lang="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;
 </syntaxhighlight>
<br>
絵文字が正しく表示され、<u>HEX(text)</u> に4バイトの絵文字が含まれていれば成功である。<br>
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - 電子回路とSUSE Linuxに関する情報 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]