アナログCPU：5108843109

ゲームと音楽とプログラミング（酒と女とロックンロールのノリで）

('ω') ＜ ｲｻﾞﾕｹｴﾝｼﾞﾆｬｰ

MySQLで4バイトutf-8文字を扱う＆ファイルフォーマットを変更する

MySQL

エントリをアップするの忘れてて今更感。

そもそも4バイトutf-8って何？

  • 絵文字
  • なんか特殊な漢字

が該当します。

4バイトutf-8文字の例

𣖔：𣖔木作（ほうのきざく、福島県の地名）
𣗄：𣗄代（たらのきだい、山形県の地名）
𩸽：ホッケ（おいしいさかな）
🍣：寿司（おいしい）
🍺：ビール（おいしい）

参考文献
JIS第3水準漢字一覧表【全1259字】(JIS X 0213:2004) - fragment.database.
JIS第4水準漢字一覧表【全2436字】(JIS X 0213:2004) - fragment.database.
「文字コード技術入門」こぼれ話: 「ほうの木作」を訪ねる - yanok.net

MySQLで扱えないの？

結論から言うと、文字セットを「utf8mb4_*」にすればOKです。
「utf8_*」では使えません。（INSERTしてみると、4バイト文字以降の部分が消えます）
接続方法、データベース、テーブル、カラム、すべてこの設定に統一しましょう。

何らかの理由で一部のみを変えたい場合、その動作はよく検証してください。
例えば、接続設定のみを変更した場合は4バイト文字が「?」に化けますが、それ以降の文字列は消えずに済みます。
接続設定とカラムのみの変更でも4バイト文字を保存することは可能ではあるようですが、
一時テーブル作成時、わざわざ文字セットを指定しない限りはデータベースの設定値が使用されるなど、
トラブルの原因になりがちです。

utf8mb4のデメリットはないの？

utf8_*系であれば1文字に割り当てられるサイズが3バイトですが、
utf8mb4_*系にすることで4バイトになります。
つまり文字列（特にCHAR型）のカラムやインデックスのサイズが大きくなったりします。

それは困る

困ると思う人はもうご存知な気もしますが、
ファイルフォーマットをAntelopeからBarracudaに変更することをお勧めします。

ちょっと何言ってるかわかんない

参考文献を貼っておきます。
InnoDBの制限とファイルフォーマットAntelopeとBarracudaの違い - かみぽわーる
MySQL InnoDBファイルフォーマットBarracudaへの設定方法 | Go-Nextブログ

Barracudaにすると何が良いのかというと、

  • インデックスサイズ制限が拡張できる（innodb_large_prefix）
  • 可変長カラムが外部ページに保存され、ローカルページにはそこへのポインタだけ保存される
    • Antelopeでは先頭768バイトがローカルページに保存されていた。つまりBarracudaにすると軽くなる

まあ後者は4バイト文字とは関係ないんですが、前者が良いポイントですね。
元々767バイトという制限だったのが3072バイトまで拡張可能になるんです。
（そんな長い文字列にインデックス張るのがよくない、という話は置いといて）
utf8_*系では255文字のカラムに対してインデックスを張ろうとすると
「255文字×3バイト＝765バイト」で767バイト制限に引っかからなかったんですが、
これをutf8mb4_*系に変更するだけで1020バイトになり、エラーとなってしまうのです。

変更方法

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

こちらは一発。

ALTER DATABASE 【データベース名】 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
テーブル・カラムの文字コードを変更

こちらはちょっと面倒。
（新規で作るときは、もちろんデータベースの設定さえしておけば無指定でOKなんですが）
後述のクエリやExcelを駆使して自動生成するのがたぶん一番楽。

ALTER TABLE 【テーブル名】
  -- テーブルの文字コード
  DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
  -- 以降はカラムの文字コード
  -- （型やコメントなども含める必要があるため注意）
 ,CHANGE 【カラム名1】 【カラム名1】 VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'カラム1'
 ,CHANGE 【カラム名2】 【カラム名2】 VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT 'カラム2'
 , ...

参考：作業時の確認用クエリ

データベースの文字コードを確認する
SELECT  `SCHEMA_NAME`
       ,`DEFAULT_CHARACTER_SET_NAME`
       ,`DEFAULT_COLLATION_NAME`
FROM    `information_schema`.`SCHEMATA`
WHERE   `SCHEMA_NAME` = '【データベース名】';
文字コードが「utf8」系になっているテーブルを抽出する
SELECT  `TABLE_NAME`
       ,`TABLE_COLLATION`
       ,`TABLE_COMMENT`
FROM    `information_schema`.`TABLES`
WHERE   `TABLE_SCHEMA` = '【データベース名】'
  AND   `TABLE_COLLATION` LIKE 'utf8\_%';
文字コードが「utf8」系になっているカラムを抽出する
SELECT  `TABLE_NAME`
       ,`COLUMN_NAME`
       ,`COLUMN_TYPE`
       ,`CHARACTER_SET_NAME`
       ,`COLLATION_NAME`
       ,`COLUMN_COMMENT`
FROM    `information_schema`.`COLUMNS`
WHERE   `TABLE_SCHEMA` = '【データベース名】'
  AND   `CHARACTER_SET_NAME` = 'utf8';