MySQLの文字コード事情

2017春版

NSEG #87 MySQL

2017/05/13

とみたまさひろ

自己紹介

• とみた まさひろ
  • http://tmtms.hatenablog.com
  • http://twitter.com/tmtms
• 長野県北部在住プログラマー
• 好きなプログラミング言語 Ruby
• Ruby/MySQLライブラリ
• MyNA代表

最近の活動

• Software Design 2016年6月号
  • 「MySQLでデータベースを作ってみよう！」
• Software Design 2016年9月号
  • 「MySQL 4つのログの使いどころ」
• Software Design 2016年12月号
  • 「Rubyと文字コード」
  • 「MySQLと文字コード」

• Software Design 2017年7月号(予定)

• mysql.gr.jp ドメイン料支払い

MySQLの文字コード事情

MySQLで文字コードというと Character set(Charset)

Character set ?

語源的には文字集合

文字集合

US-ASCII

数字、英字、32個の記号

JIS X 0201

US-ASCII(「\」→「¥」/「~」→「‾」)+カタカナ

JIS X 0208

数字、ひらがな、カタカナ、漢字、ラテン文字、ギリシャ文字、記号等々

JIS X 0213

JIS X 0208 + 第三水準/第四水準、ローマ数字、鼻濁音文字等々

文字集合

Windows-31J

JIS X 0201 + JIS X 0208 + NEC特殊文字 + IBM拡張文字(「⑧」「Ⅷ」「㈱」「髙」「﨑」「彅」等)

Unicode

世界中の文字。絵文字(「🍺」「🍣」等)も含む。

あれ？

「CP932」とか「UTF-8」は？

エンコーディング

文字符号化方式

文字集合の文字をコンピュータで利用できるバイト列に変換する方式

エンコーディング

エンコーディング

Charset ≒ エンコーディング

(MySQLに限らない)

MySQLのCharset

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

日本語が使えてASCII互換のは ujis, sjis, eucjpms, cp932, utf8, utf8mb4

MySQL Charset

今なら普通はUnicode

• 世界中のほぼすべての文字を扱える
• 絵文字も使える
• 世の中もう普通にユニコード
• 文字コード変換とか考えなくていい
• 文字化けしない

utf8 ? utf8mb4 ?

utf8 と utf8mb4

• utf8: 1文字 1〜3バイト (U+0000〜U+FFFF)
  utf8mb3 という別名あり
• utf8mb4: 1文字 1〜4バイト(U+0000〜U+1FFFFF)
• UTF-8 は普通1〜4バイト
  utf8 / utf8mb4 の違いは MySQL のみ
• ほとんどの日本語文字は3バイト
• 一部の漢字と絵文字等が4バイト

4バイトになる JIS X 0213 の文字

4バイトになる絵文字(一部)

MySQLのCharset

• サーバー
• クライアント
• サーバー/クライアント間の接続
• データベース毎
• テーブル毎
• カラム毎

…を全部別々に設定できる（嫌な予感しかしない）

ハマりたくなければ全部統一

サーバー Charset

• 新規に作成するデータベースのCharsetのデフォルト
• これさえ指定しておけばだいたいOK
• mysqld の起動オプション
  --character-set-server=utf8mb4
• my.cnf の [mysqld] セクション
  character-set-server = utf8mb4
• サーバー変数 character_set_server

データベース Charset

• 配下に作成するテーブルのCharsetのデフォルト値

• サーバーCharsetと同じであれば指定しなくてもいい

• 作成: CREATE DATABASE db CHARSET utf8mb4;

• 確認: SHOW CREATE DATABASE db;

• 変更: ALTER DATABASE db CHARSET utf8mb4;

• 変更しても既存のテーブルCharsetは変更されない

テーブル Charset

• テーブル内カラムのCharsetのデフォルト値

• データベースCharsetと同じであれば指定する必要はない

• 作成: CREATE TABLE tbl (...) CHARSET utf8mb4;

• 確認: SHOW CREATE TABLE tbl;

テーブル Charset

• テーブル属性だけ変更:
  ALTER TABLE tbl CHARSET utf8mb4;

  • 既存のカラムのcharsetは変更されない

• 全カラムとデータの変換:
  ALTER TABLE tbl CONVERT TO CHARSET utf8mb4;

  • utf8 で作ってしまったテーブルを utf8mb4 に変換とか

カラム Charset

• テーブルCharsetと同じであれば指定する必要はない

• 作成: CREATE TABLE tbl (col VARCHAR(10) CHARSET utf8mb4, ...);

• 確認: SHOW CREATE TABLE tbl;

• 変更: ALTER TABLE tbl MODIFY col VARCHAR(10) CHARSET utf8mb4;

• 変更するとカラム内のデータも変換される

クライアント Charset

• クライアント内での文字列処理と
  サーバーとの接続Charsetに使用される

• 指定方法はプログラム/言語に依存

  mysql --default-character-set=utf8mb4

• プログラムによっては my.cnf の [client] セクションが有効

  [client]
  loose-default-character-set = utf8mb4
  

  「loose-」をつけておくとそのパラメータを知らないプログラムは無視してくれる

クライアント Charset

• mysqld との接続毎に異なる

• mysql コマンドでOKでも他のアプリからはNGかもしれない

• 何も指定しなければ latin1

Charset の確認

mysql> SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| 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     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql コマンド

• mysql コマンドのデフォルトCharsetは auto

• システムロケール(LC_ALL, LC_CTYPE, LANG 環境変数等)により値が決定

• LANG=ja_JP.UTF-8 の場合は utf8 になる
  utf8mb4 ではない

• LANG=C の場合は latin1 になる

utf8 と utf8mb4 の混在で起きること

utf8接続から4バイト文字を参照

クライアントが扱えない文字は「?」になる

mysql> SELECT str FROM tbl;
+-------+
| str   |
+-------+
| ?と?  |     ← '🍣と🍺'
| ?と?  |     ← '?と?'
+-------+

バイト列を見れば違いがわかる

mysql> SELECT HEX(str) FROM tbl;
+------------------------+
| HEX(str)               |
+------------------------+
| F09F8DA3E381A8F09F8DBA | ← '🍣と🍺'
| 3FE381A83F             | ← '?と?'
+------------------------+

utf8接続から4バイト文字を登録

文字化けして登録されちゃう

mysql> INSERT INTO tbl (str) VALUES ('🍣と🍺');
Query OK, 1 row affected, 2 warnings (0.05 sec)

mysql> SELECT str,HEX(str) FROM tbl;
+-------------+------------------------+
| str         | HEX(str)               |
+-------------+------------------------+
| ????と????  | 3F3F3F3FE381A83F3F3F3F |
+-------------+------------------------+

sql_mode

• MySQLはおかしなことしてもあまりエラーにならない(余計なお世話)

• sql_mode でちゃんとエラーにしてくれる

mysql> SET sql_mode='STRICT_ALL_TABLES';

mysql> INSERT INTO tbl (str) VALUES ('🍣と🍺');
ERROR 1366 (HY000): Incorrect string value:
 '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'str' at row 1

sql_mode

MySQL 5.7 からはデフォルト

mysql> SELECT @@sql_mode
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION

🍣=🍺 問題

🍣と🍺以外でも

mysql> SELECT '🐰'='🐢';
+---------+
| '?'='?' |
+---------+
|       1 |
+---------+
mysql> SELECT '🍛'='💩';
+---------+
| '?'='?' |
+---------+
|       1 |
+---------+

実は未知の文字�扱い

‘🍣’=’🍺’=’�’

Collation

Collation

• 文字の照合規則・照合順序
• Charset 毎に Collation がある
• Charset の指定は Charset のデフォルトの Collation を指定するのと同じ

Collation 一覧

mysql> SHOW COLLATION LIKE 'utf8mb4%';
+------------------------+---------+-----+---------+----------+---------+
| Collation              | Charset | Id  | Default | Compiled | Sortlen |
+------------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci     | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_bin            | utf8mb4 |  46 |         | Yes      |       1 |
| utf8mb4_unicode_ci     | utf8mb4 | 224 |         | Yes      |       8 |
| utf8mb4_icelandic_ci   | utf8mb4 | 225 |         | Yes      |       8 |
| utf8mb4_latvian_ci     | utf8mb4 | 226 |         | Yes      |       8 |
| utf8mb4_romanian_ci    | utf8mb4 | 227 |         | Yes      |       8 |
| utf8mb4_slovenian_ci   | utf8mb4 | 228 |         | Yes      |       8 |
| utf8mb4_polish_ci      | utf8mb4 | 229 |         | Yes      |       8 |
| utf8mb4_estonian_ci    | utf8mb4 | 230 |         | Yes      |       8 |
| utf8mb4_spanish_ci     | utf8mb4 | 231 |         | Yes      |       8 |
| utf8mb4_swedish_ci     | utf8mb4 | 232 |         | Yes      |       8 |
| utf8mb4_turkish_ci     | utf8mb4 | 233 |         | Yes      |       8 |
| utf8mb4_czech_ci       | utf8mb4 | 234 |         | Yes      |       8 |
| utf8mb4_danish_ci      | utf8mb4 | 235 |         | Yes      |       8 |
| utf8mb4_lithuanian_ci  | utf8mb4 | 236 |         | Yes      |       8 |
| utf8mb4_slovak_ci      | utf8mb4 | 237 |         | Yes      |       8 |
| utf8mb4_spanish2_ci    | utf8mb4 | 238 |         | Yes      |       8 |
| utf8mb4_roman_ci       | utf8mb4 | 239 |         | Yes      |       8 |
| utf8mb4_persian_ci     | utf8mb4 | 240 |         | Yes      |       8 |
| utf8mb4_esperanto_ci   | utf8mb4 | 241 |         | Yes      |       8 |
| utf8mb4_hungarian_ci   | utf8mb4 | 242 |         | Yes      |       8 |
| utf8mb4_sinhala_ci     | utf8mb4 | 243 |         | Yes      |       8 |
| utf8mb4_german2_ci     | utf8mb4 | 244 |         | Yes      |       8 |
| utf8mb4_croatian_ci    | utf8mb4 | 245 |         | Yes      |       8 |
| utf8mb4_unicode_520_ci | utf8mb4 | 246 |         | Yes      |       8 |
| utf8mb4_vietnamese_ci  | utf8mb4 | 247 |         | Yes      |       8 |
+------------------------+---------+-----+---------+----------+---------+
26 rows in set (0.00 sec)

utf8mb4 の Collation

• utf8mb4_bin
• utf8mb4_general_ci
• utf8mb4_unicode_ci
• utf8mb4_unicode_520_ci
• utf8mb4_言語_ci
• utf8mb4_japanese_ci は無い

「ci」は Case Insensitive(大文字小文字を区別しない)の意味らしい

utf8mb4_bin

• varchar(n) binary
• 全文字を区別する(A≠a, 🍣≠🍺)

utf8mb4_general_ci

• utf8mb4 Charset のデフォルト Collation
• 大文字小文字を区別しない(A=a, Ａ=ａ)
• 全角半角は区別する(A≠Ａ)
• 絵文字を区別しない(🍣=🍺)

utf8mb4_unicode_ci

• Unicode Collation Algorithm 4.0.0
  • http://www.unicode.org/reports/tr10/tr10-11.html
  • http://dev.mysql.com/doc/refman/5.6/ja/charset-unicode-sets.html
• 大文字小文字を区別しない(A=a, Ａ=ａ, や=ゃ)
  • 「びょういん=びよういん」問題
• 全角半角も区別しない(A=Ａ)
• 絵文字を区別しない(🍣=🍺)
• ひらがな、カタカナ、濁点有無、全角、半角を区別しない(は=ば=ぱ=ハ=バ=パ=ﾊ)
  • 「ハハ=パパ=ババ問題」

utf8mb4_unicode_520_ci

• Unicode Collation Algorithm 5.2.0
  • http://www.unicode.org/reports/tr10/tr10-20.html
• 大文字小文字を区別しない(A=a, Ａ=ａ, や=ゃ)
• 全角半角も区別しない(A=Ａ)
• 絵文字を区別する(🍣≠🍺)
• ひらがな、カタカナ、濁点有無、全角、半角を区別しない(は=ば=ぱ=ハ=バ=パ=ﾊ)

utf8mb4_*

utf8mb4_*

寿司=ビール問題 : MySQL 8.0でのUTF8サポート入門(MySQL Server Blogより)

https://yakst.com/ja/posts/4405

私たちは日本語の照合順序の追加も計画中です。日本語は興味深い言語であり、私たちの照合順序のエキスパートであるXing ZhangとBernt Marius Johnsenが、今後のブログ記事でもっと詳しく説明するはずです。

2017/1/13の記事(の翻訳)

MySQL 8 (開発中)

mysql> SELECT collation_name FROM collations
WHERE character_set_name='utf8mb4' AND
collation_name LIKE '%ja%';
+-----------------------+
| collation_name        |
+-----------------------+
| utf8mb4_ja_0900_as_cs |
+-----------------------+

utf8mb4_*

ちょっと期待したのとは違う

正式リリースまでに変わるかも

デフォルトのCollation

MySQL 5.7

mysql> SELECT collation_name, is_default FROM collations
WHERE character_set_name='utf8mb4' and is_default='Yes';
+--------------------+------------+
| collation_name     | is_default |
+--------------------+------------+
| utf8mb4_general_ci | Yes        |
+--------------------+------------+

MySQL 8

+--------------------+------------+
| collation_name     | is_default |
+--------------------+------------+
| utf8mb4_0900_ai_ci | Yes        |
+--------------------+------------+

MySQL 8 の utf8mb4 はデフォルトで

「ハハ=パパ=ババ」

「びょういん=びよういん」

まとめ

• ふつうは utf8mb4
• サーバーとクライアントの両方で Charset を指定
• sql_mode をちゃんと設定
• Collation は適切に
• MySQL 8 でデフォルトが変わる