日本語を適切に扱うための MySQL 5.7, 8.0 の character set と collation の設定
- TL;DR
- 真面目な説明
本稿の内容は、以下の環境で意図通りに動作することを確認している。
TL;DR
MySQL の文字列エンコーディングを UTF-8 にしたい場合、/etc/mysql/conf.d/charset.cnf を作成し、内容を次のようにする。charset.cnf の名前は何でも良いが、拡張子は .cnf である必要がある。
character set はほぼ間違いなく utf8mb4 で良いが、collation はアプリケーションの要求によって選択の余地がある。代表的な例として、次の設定を挙げる。
MySQL 5.7
[mysql] default_character_set = utf8mb4 [mysqld] character_set_server = utf8mb4 collation_server = utf8mb4_bin
MySQL 8.0
[mysql] default_character_set = utf8mb4 [mysqld] character_set_server = utf8mb4 collation_server = utf8mb4_ja_0900_as_cs_ks
真面目な説明
Unicode と UTF-16, UTF-8
MySQL における character set や collation の扱いを見る前に、まず Unicode や UTF そのものについて概観する。
Unicode は、人類が歴史上使ってきた全ての文字を収録することを目的とする符号化文字集合である。UTF (UCS/Unicode Transformation Format) は、Unicode の符号位置をどのようなビット組み合わせに対応付けるか、という取り決め（文字符号化方式）である。
Unicode には、
- 0 群
- 0, 1, ..., 16 面
- 0, 1, ..., 255 区
- 0, 1, ..., 255 点
が定義されており、全体で 1 * 17 * 256 * 256 = 1,114,112 の符号位置がある。
現代の日本語利用者が日常的に使うラテン文字、アラビア数字、ひらがな、カタカナ、そして多くの漢字は、そのほとんどが第0群第0面の基本多言語面 (Basic Multilingual Plane, BMP) に収録されている。1つの面の符号位置の数は 256 * 256 = 216 = 65,536 であるから、基本多言語面の文字だけを利用できれば良いのであれば、ナイーブに2オクテットを1文字に対応付けるような文字符号化方式を用いれば良い。この方法が UTF-16 である。*1
UTF-16 は、ラテン文字についても1文字あたり2オクテットを要するから、英文を表現する場合には、従来の Latin-1 の2倍の記憶容量を消費する。これは非効率であり、さらに従来の Latin-1 との互換性も失われる。そこで、現代のウェブアプリケーションやその周辺技術においては、別の文字符号化方式である UTF-8 が利用されることが多い。UTF-8 は、下表に示すように、1つの文字に対して、符号位置ごとに異なる長さのビット組み合わせを対応付ける。
| オクテット数 | 下限 | 上限 |
|---|---|---|
| 1 | U+0000 | U+007F |
| 2 | U+0080 | U+07FF |
| 3 | U+0800 | U+FFFF |
| 4 | U+10000 | U+10FFFF |
Unicode の U+0000 から U+007F の範囲の128文字は ASCII と同一であるから、ASCII に収録されているラテン文字やアラビア数字は1文字＝1オクテットで表現される。これにより、UTF-8 は UTF-16 よりも少ないデータ容量で、ラテン文字からなる文字列をエンコードできる。*2
下の例では、UTF-8 を用いる Linux 環境で、ラテン文字の C, J, K がそれぞれ1オクテット、漢字の 統, 合, 漢, 字 がそれぞれ3オクテット、絵文字の💩が4オクテットでエンコードされていることを確認している。
MySQL への応用
MySQL では、データベースサーバーの character set と collation、データベースクライアントの character set をそれぞれ設定することができる。ここで、character set とは、データベースで扱う文字の種類、並びに文字と一対一で対応するビット組み合わせの取り決めのことであり、collation とは、文字列を並び替えるときに、どのような基準に基づいて文字同士を比較するか、という取り決めのことである。
データベースサーバーの character set
MySQL 5.7, 8.0 のデフォルトの character set と collation を下表に示す。
| Default character set | Default collation | |
|---|---|---|
| MySQL 5.7 | latin1 | latin1_swedish_ci |
| MySQL 8.0 | utf8mb4 | utf8mb4_0900_ai_ci |
Latin-1 (ISO/IEC 8859-1) は、英語及び西ヨーロッパの諸言語を表現できる符号化文字集合である。Latin-1 ではひらがな、カタカナ、そして漢字を表現することはできないので、MySQL 5.7 を日本語文化圏で使う場合は、ほぼ間違いなくこれらの設定を見直す必要がある。
一方、MySQL 8.0 のデフォルト character set は utf8mb4 であり、これは、ひらがな、カタカナ、漢字、そして絵文字を全て適切に表現できる。collation の utf8mb4_0900_ai_ci については、見直す余地がある。
ところで、utf8mb4 とは大変珍妙な名前であるが、MySQL の世界には、utf8mb3 という名前の character set も存在する。先に述べたように、UTF-8 は Unicode の1つの符号位置を1オクテットから4オクテットの可変長ビット組み合わせで符号化する。基本多言語面内の符号位置は1から3オクテットで表現され、それ以外の面の符号位置は4オクテットで表現される。MySQL において、utf8mb3 は1から3オクテットの可変長ビット組み合わせに対応するエンコーディング方式である。つまり、utf8mb3 は、基本多言語面の文字しか表現できない。多くの絵文字や、アイヌ語を表現するためのカタカナ、そして一部の漢字は、第0群第1面の追加多言語面に収録されており、これらの文字を十分適切に表現するためには、1符号位置に1から4オクテットのビット組み合わせを対応付ける「フルスペックの」UTF-8 を利用する必要がある。この「フルスペックの」UTF-8 こそが utf8mb4 であり、現在は専ら utf8mb4 を利用することが推奨されている。
これまでの議論により、現代的な MySQL データベースサーバーの character set としては、utf8mb4 を設定するのが最適である。
データベースサーバーの collation
1つの character set には1つ以上の collation が紐づけられる。各 character set にはデフォルトの collation が割り当てられている。MySQL 5.7 の場合、utf8mb4 のデフォルト collation は utf8mb4_general_ci であり、MySQL 8.0 の場合、utf8mb4 のデフォルト collation は utf8mb4_0900_ai_ci である。
utf8mb4 に紐づく collation は複数あり、SHOW COLLATION WHERE Charset = 'utf8mb4'; により確認できる。下表では、日本語環境で有用であろう、代表的なものに限定して表示している。
ここで、各 collation の挙動を確かめるために、テスト用の MySQL 5.7, 8.0 データベースに次のような SQL 文を発行する。ここで、<collation> の部分は、上表内の collation の値で置き換え、行の挿入時に duplicate error が発生しても、無視して次の行に進む。
DROP DATABASE IF EXISTS `collation_test`; CREATE DATABASE `collation_test` CHARACTER SET utf8mb4 COLLATE <collation>; USE `collation_test`; CREATE TABLE `cities` (`name` VARCHAR(10) PRIMARY KEY); -- ラテン文字の大文字・小文字は区別されるか -- ダイアクリティカルマークの有無は区別されるか INSERT INTO `cities` (`name`) VALUES ('Tokyo'); INSERT INTO `cities` (`name`) VALUES ('Tôkyô'); INSERT INTO `cities` (`name`) VALUES ('Tōkyō'); INSERT INTO `cities` (`name`) VALUES ('TOKYO'); INSERT INTO `cities` (`name`) VALUES ('TÔKYÔ'); INSERT INTO `cities` (`name`) VALUES ('TŌKYŌ'); -- ひらがな・カタカナは区別されるか -- 拗音は区別されるか INSERT INTO `cities` (`name`) VALUES ('とうきょう'); INSERT INTO `cities` (`name`) VALUES ('とうきよう'); INSERT INTO `cities` (`name`) VALUES ('トウキョウ'); INSERT INTO `cities` (`name`) VALUES ('トウキヨウ'); -- 濁音は区別されるか INSERT INTO `cities` (`name`) VALUES ('なごや'); INSERT INTO `cities` (`name`) VALUES ('なこや'); -- 半濁音・促音は区別されるか INSERT INTO `cities` (`name`) VALUES ('さっぽろ'); INSERT INTO `cities` (`name`) VALUES ('さつぽろ'); INSERT INTO `cities` (`name`) VALUES ('さっほろ'); INSERT INTO `cities` (`name`) VALUES ('さつほろ'); -- 漢字の整列順はどうか INSERT INTO `cities` (`name`) VALUES ('東京'); INSERT INTO `cities` (`name`) VALUES ('名古屋'); INSERT INTO `cities` (`name`) VALUES ('札幌'); SELECT `name` FROM `cities` ORDER BY `name`;
各 collation に対する上 SQL 文の結果は、次のとおりとなった。
MySQL 5.7
collation: utf8mb4_general_ci
ラテン文字の大文字・小文字や、ダイアクリティカルマークの有無が区別されていない。ひらがな・カタカナ・漢字は全て区別されている。
collation: utf8mb4_bin
単純なバイナリ比較であるから、例に挙げた全ての文字列が区別されている。
collation: utf8mb4_unicode_ci
ラテン文字の大文字・小文字や、ダイアクリティカルマークの有無が区別されていない。また、ひらがな・カタカナも区別されず、濁音・半濁音・拗音・促音も全て区別されない。
MySQL 8.0
collation: utf8mb4_0900_ai_ci
ラテン文字の大文字・小文字や、マクロン (¯) やサーカムフレックス (^) のようなダイアクリティカルマークの有無は区別されず、Tokyo のみが挿入されている。また、ひらがな・カタカナも区別されず、濁音・半濁音・拗音・促音も全て区別されない。
collation: utf8mb4_0900_bin
単純なバイナリ比較であるから、例に挙げた全ての文字列が区別されている。
collation: utf8mb4_general_ci
ラテン文字の大文字・小文字や、ダイアクリティカルマークの有無が区別されていない。ひらがな・カタカナ・漢字は全て区別されている。
collation: utf8mb4_ja_0900_as_cs
日本語用の collation のひとつである。MySQL :: MySQL 8.0 Reference Manual :: 10.10.1 Unicode Character Sets の説明は、utf8mb4_ja_0900_as_cs はひらがな・カタカナを区別せず、次に挙げる utf8mb4_ja_0900_as_cs_ks はそれらを区別するとしている。ダイアクリティカルマークは区別されている。
For Japanese, the utf8mb4 character set includes utf8mb4_ja_0900_as_cs and utf8mb4_ja_0900_as_cs_ks collations. Both collations are accent-sensitive and case-sensitive. utf8mb4_ja_0900_as_cs_ks is also kana-sensitive and distinguishes Katakana characters from Hiragana characters, whereas utf8mb4_ja_0900_as_cs treats Katakana and Hiragana characters as equal for sorting. Applications that require a Japanese collation but not kana sensitivity may use utf8mb4_ja_0900_as_cs for better sort performance. utf8mb4_ja_0900_as_cs uses three weight levels for sorting; utf8mb4_ja_0900_as_cs_ks uses four.
collation: utf8mb4_ja_0900_as_cs_ks
utf8mb4_0900_bin と同様に、例に挙げた全ての文字列が区別されている。ただし、並び順に差異がある。
collation: utf8mb4_unicode_ci
ラテン文字の大文字・小文字や、ダイアクリティカルマークの有無が区別されていない。また、ひらがな・カタカナも区別されず、濁音・半濁音・拗音・促音も全て区別されない。
データベースクライアントの character set
MySQL に限らず、クライアント・サーバーモデルの DBMS では、多種多様なクライアントソフトウェアの選択肢がある。最もプリミティブなクライアントの一つとして、mysql がある。これは、例えば Debian 系のシステムで apt-get install mysql-server をすると、mysqld とともにインストールされる。
ほとんどの場合、mysql が利用する character set も、データベースサーバーと同様に、utf8mb4 で問題ないと思われる。
参考：
