こんにちは、hachi8833です。MySQLで気をつけたい文字コード関連のトラブルシューティングを解説した記事の翻訳をお送りいたします。
文字コードのトラブルとしてよく知られている寿司ビール問題は、昨年のTechRacho記事「MySQLのencodingをutf8からutfmb4に変更して寿司ビール問題に対応する」でも扱ったことがありました。🍣 🍺
ちょうど、つい最近のkamipoさんの記事「MySQL 8.0ではデフォルトで濁点半濁点を区別しなくなる」などで8.0系のデフォルトのcollationが残念なことになっているのを知ったところです。kamipoさんはこうした問題をかなり前から指摘していますね。
概要
原著者より許諾を得て翻訳・公開いたします。
- 英語記事: Troubleshooting Issues with MySQL Character Sets Q & A（2017/03/28）
- 著者: Sveta Smirnova
- 運営: Percona: MySQLを改良したPercona Serverをはじめとするデータベースソリューションで知られています。
なおcharacter setsは本記事では原文に即して「文字セット」と訳しています。
MySQLの文字コード関連のQ&A集（翻訳）
元記事より
このブログでは、「MySQLの文字セットのトラブルシューティング」セミナーで出された質問への回答を掲載いたします。
まず、3月9日に行われた同セミナーに参加いただいた皆さまにお礼を申し上げたいと思います。セミナーのスライドと動画はこちらからご覧いただけます。以下は、セミナー中に回答できなかった質問と回答のリストです。
Q1: utf8からutf8mb4への変換
私たちのところでは、テーブルをutf8からutf8mb4に変換するときに問題がいくつか発生しました。このときはutf8mb4_unicode_520_ciというcollationを使いたかったのですが、このcollationでは普通のスペースと日本語の漢字のスペース（訳注: いわゆる全角スペース）が区別されませんでした。そのため、varchar型のフィールド間で、2つのエントリにスペースの種類以外は同じテキストがある場合、unique制約違反が発生しました。こういう問題を見かけたことはありますか？また、どのように回避すればよいでしょうか？私たちの方では、この問題はcollationの親文字と子文字のバグに関連しているのではないかと想像しています。
A1: 残念ながら、この問題はさまざまな言語で発生します。たとえばutf8やutf8mb4を使うと、ロシア語でеとёが区別されなくなってしまいます。しかし日本語については希望が持てます。OracleがMySQL 8.0で各言語固有のutf8mb4 collationを新しく実装すると発表したのです。私は、8.0.0で既に21の新しいcollationが実装されていることを確認しています。
mysql> show collation like '%0900%';
+----------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+----------------------------+---------+-----+---------+----------+---------+
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | | Yes | 8 |
| utf8mb4_cs_0900_ai_ci | utf8mb4 | 266 | | Yes | 8 |
| utf8mb4_da_0900_ai_ci | utf8mb4 | 267 | | Yes | 8 |
| utf8mb4_de_pb_0900_ai_ci | utf8mb4 | 256 | | Yes | 8 |
| utf8mb4_eo_0900_ai_ci | utf8mb4 | 273 | | Yes | 8 |
| utf8mb4_es_0900_ai_ci | utf8mb4 | 263 | | Yes | 8 |
| utf8mb4_es_trad_0900_ai_ci | utf8mb4 | 270 | | Yes | 8 |
| utf8mb4_et_0900_ai_ci | utf8mb4 | 262 | | Yes | 8 |
| utf8mb4_hr_0900_ai_ci | utf8mb4 | 275 | | Yes | 8 |
| utf8mb4_hu_0900_ai_ci | utf8mb4 | 274 | | Yes | 8 |
| utf8mb4_is_0900_ai_ci | utf8mb4 | 257 | | Yes | 8 |
| utf8mb4_la_0900_ai_ci | utf8mb4 | 271 | | Yes | 8 |
| utf8mb4_lt_0900_ai_ci | utf8mb4 | 268 | | Yes | 8 |
| utf8mb4_lv_0900_ai_ci | utf8mb4 | 258 | | Yes | 8 |
| utf8mb4_pl_0900_ai_ci | utf8mb4 | 261 | | Yes | 8 |
| utf8mb4_ro_0900_ai_ci | utf8mb4 | 259 | | Yes | 8 |
| utf8mb4_sk_0900_ai_ci | utf8mb4 | 269 | | Yes | 8 |
| utf8mb4_sl_0900_ai_ci | utf8mb4 | 260 | | Yes | 8 |
| utf8mb4_sv_0900_ai_ci | utf8mb4 | 264 | | Yes | 8 |
| utf8mb4_tr_0900_ai_ci | utf8mb4 | 265 | | Yes | 8 |
| utf8mb4_vi_0900_ai_ci | utf8mb4 | 277 | | Yes | 8 |
+----------------------------+---------+-----+---------+----------+---------+
21 rows in set (0,03 sec)
8.0.1ではさらに、大文字小文字を区別するcollationと日本語のcollationを追加すると約束しています。詳しくはMySQLのブログをご覧ください。日本語に関するサポート計画についてはブログの末尾にあります。
それまでの間おすすめできるのは、MySQLのマニュアル「10.4.4 Adding a UCA Collation to a Unicode Character Set」の方法に沿って独自のcollationを実装することぐらいです。#51976のバグに載っているutf8_russian_ci collationを例にしてみましょう。
マニュアルでは、新しいcollationを作成可能な文字セットリストの中にutf8mb4は載っていませんが、実際は作成できます。utf8mb4文字セットのレコードと新しいcollationをIndex.xmlに1つ追加し、サーバーを再起動するだけで完了します。
<charset name="utf8mb4">
<collation name="utf8mb4_russian_ci" id="1033">
<rules>
<reset>u0415</reset><p>u0451</p><t>u0401</t>
</rules>
</collaiton>
</charset>
mysql> show collation like 'utf8mb4_russian_ci';
+--------------------+---------+------+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------+---------+------+---------+----------+---------+
| utf8mb4_russian_ci | utf8mb4 | 1033 | | | 8 |
+--------------------+---------+------+---------+----------+---------+
1 row in set (0,03 sec)
mysql> create table test_yo(gen varchar(100) CHARACTER SET utf8mb4, yo varchar(100) CHARACTER SET utf8mb4 collate utf8mb4_russian_ci) engine=innodb default character set=utf8mb4;
Query OK, 0 rows affected (0,25 sec)
mysql> set names utf8mb4;
Query OK, 0 rows affected (0,02 sec)
mysql> insert into test_yo values('ел', 'ел'), ('ель', 'ель'), ('ёлка', 'ёлка');
Query OK, 3 rows affected (0,05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into test_yo values('Ел', 'Ел'), ('Ель', 'Ель'), ('Ёлка', 'Ёлка');
Query OK, 3 rows affected (0,06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test_yo order by gen;
+----------+----------+
| gen | yo |
+----------+----------+
| ел | ел |
| Ел | Ел |
| ёлка | ёлка |
| Ёлка | Ёлка |
| ель | ель |
| Ель | Ель |
+----------+----------+
6 rows in set (0,00 sec)
mysql> select * from test_yo order by yo;
+----------+----------+
| gen | yo |
+----------+----------+
| ел | ел |
| Ел | Ел |
| ель | ель |
| Ель | Ель |
| ёлка | ёлка |
| Ёлка | Ёлка |
+----------+----------+
6 rows in set (0,00 sec)
Q2: latin1でutf8を受けると文字化ける
latin1文字セットでutf8を受けると文字化けするんですが、utf8に再フォーマットしてデータの文字化けを解消できますか？また、後から作業しても手遅れになるようなタイムリミットはありますか？
A2: utf8データをlatin1のカラムに保存した場合にのみ文字化けします。たとえば以下のように定義されたテーブルがあるとしましょう。
create table latin1(
f1 varchar(100)
) engine=innodb default charset=latin1;
次に、latin1文字セットに該当しない文字を含むutf8形式の単語をそこに挿入します。
mysql> set names utf8;
Query OK, 0 rows affected (0,00 sec)
mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0,00 sec)
mysql> insert into latin1 values('Sveta'), ('Света');
Query OK, 2 rows affected, 1 warning (0,04 sec)
Records: 2 Duplicates: 0 Warnings: 1
このUTF8形式データは文字化けしてしまい、回復不能になります。
mysql> select * from latin1;
+-------+
| f1 |
+-------+
| Sveta |
| ????? |
+-------+
2 rows in set (0,00 sec)
mysql> select f1, hex(f1) from latin1;
+-------+------------+
| f1 | hex(f1) |
+-------+------------+
| Sveta | 5376657461 |
| ????? | 3F3F3F3F3F |
+-------+------------+
2 rows in set (0,01 sec)
データをUTF8カラムに保存していても、コネクションをlatin1にしていると、結果セットは文字化けします。この場合、データそのものは無事です。
mysql> create table utf8(f1 varchar(100)) engine=innodb character set utf8;
Query OK, 0 rows affected (0,18 sec)
mysql> insert into utf8 values('Sveta'), ('Света');
Query OK, 2 rows affected (0,15 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> set names latin1;
Query OK, 0 rows affected (0,00 sec)
mysql> select f1, hex(f1) from utf8;
+-------+----------------------+
| f1 | hex(f1) |
+-------+----------------------+
| Sveta | 5376657461 |
| ????? | D0A1D0B2D0B5D182D0B0 |
+-------+----------------------+
2 rows in set (0,00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0,00 sec)
mysql> select f1, hex(f1) from utf8;
+------------+----------------------+
| f1 | hex(f1) |
+------------+----------------------+
| Sveta | 5376657461 |
| Света | D0A1D0B2D0B5D182D0B0 |
+------------+----------------------+
2 rows in set (0,00 sec)
Q3: 文字セットはmysqldumpにどう影響するか
文字セットはmysqldumpでどのように影響しますか？特に、デフォルトの文字セットが異なるテーブルが複数あるデータをダンプした場合にはどうなりますか？
A3: ダンプは正常に行われます。MySQLは異なる文字セットを使っているテーブルを正しく変換できるので、mysqldumpで--default-character-setオプションを指定するだけでよいです。この場合、文字セットの種類にかかわらず、指定した文字セットに文字列を変換します。
たとえばcp1251とlatin1を使っている場合、--default-character-setでcp1251、utf8、utf8mb4を指定できます。ただしこの場合latin1は指定できません。cp1251に含まれるキリル文字はlatin1には含まれていないからです。
mysqldumpのデフォルト値はutf8です。utf8のサポート範囲外の値（utf8mb4の顔文字など）を使う場合を除き、デフォルト値以外に設定する必要はありません。
Q4: --single-transactionオプション
mysqldumpの--single-transactionオプションだと、一度に1つの文字セットしかデフォルトに指定できないのではないでしょうか？
A4: そのとおりです。そしてそれで問題ありません。すべてのデータはこの文字セットに変換され、ダンプ結果をリストアするときにはカラム定義で指定された文字セットに逆変換されるからです。
Q5: UTF-8での大文字小文字の区別
MySQLでは大文字小文字を区別するUTF-8文字セットがサポートされていないことに気が付きました。大文字小文字を区別するUTF-8の実装についておすすめはありますか？また完全に実現できるものでしょうか？
A5: 上にも書いたリンクによると、OracleはMySQL 8.0.1でutf8mb4での大文字小文字を区別するcollationの実装を約束しています。それまでの間は、大文字小文字を区別するcollationを独自に実装することをおすすめします。
Q6: ツールが文字セットに影響される可能性
pt-table-checksumのようなツールは文字セットからどのような影響を受けますか？utf8mb4のような4バイト文字セットは、どんな比較でもデフォルトの文字セットとして安全に使えますか？今使っているテーブルにはcollationがlatin1、utf8、utf8mb4のものがあります。
A6: その組み合わせでしたら何の問題もありません。pt-table-checksumには、カラムを結合してCRC32チェックサムを算出する複雑な関数セットが使われています。今の場合、どのデータもutf8mb4に変換されるので衝突は生じません。
ただし、互換性のない複数の文字セットが1つのテーブル内に保存されていると、"Illegal mix of collations for operation 'concat_ws' "エラーが生じるかもしれません。
mysql> create table cp1251(f1 varchar(100) character set latin1, f2 varchar(100) character set cp1251) engine=innodb;
Query OK, 0 rows affected (0,32 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0,00 sec)
mysql> insert into cp1251 values('Sveta', 'Света');
Query OK, 1 row affected (0,07 sec)
sveta@Thinkie:~/build/mysql-8.0/mysql-test$ ~/build/percona-toolkit/bin/pt-table-checksum h=127.0.0.1,P=13000,u=root,D=test
Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.
03-18T03:51:58 Error executing EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `f1`, `f2`, CONCAT(ISNULL(`f1`), ISNULL(`f2`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `db1`.`cp1251` /*explain checksum table*/: DBD::mysql::st execute failed: Illegal mix of collations for operation 'concat_ws' [for Statement "EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `f1`, `f2`, CONCAT(ISNULL(`f1`), ISNULL(`f2`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `db1`.`cp1251` /*explain checksum table*/"] at /home/sveta/build/percona-toolkit/bin/pt-table-checksum line 11351.
03-18T03:51:58 Error checksumming table db1.cp1251: Error executing checksum query: DBD::mysql::st execute failed: Illegal mix of collations for operation 'concat_ws' [for Statement "REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `f1`, `f2`, CONCAT(ISNULL(`f1`), ISNULL(`f2`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `db1`.`cp1251` /*checksum table*/" with ParamValues: 0='db1', 1='cp1251', 2=1, 3=undef, 4=undef, 5=undef] at /home/sveta/build/percona-toolkit/bin/pt-table-checksum line 10741.
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
03-18T03:51:58 2 0 0 1 0 0.003 db1.cp1251
03-18T03:51:58 0 0 2 1 0 0.167 db1.latin1
03-18T03:51:58 0 0 6 1 0 0.198 db1.test_yo
...
このツールはそのまま動き続け、テーブルの残りの部分も処理します。この動作についてはバグ#1674266に報告しました。
「MySQLの文字セットのトラブルシューティング」セミナーにご出席いただいた皆さまに、改めてお礼を申し上げます。
関連記事
- PostgreSQLの機能と便利技トップ10（2016年版）（翻訳）
- Rails開発者のためのPostgreSQLの便利技（翻訳）
- [Rails] RubyistのためのPostgreSQL EXPLAINガイド（翻訳）
- 【ゆるふわDocker部】任意バージョンのPostgreSQLコマンドを実行して外部DBに接続する
- RailsでPostgresを使おうとしてはまった
- Railsで発行されたSQLを監視する
- [Rails 3] 失敗しないmigrationを書こう
- EUC-JPのDBをmysqldumpしたあとインポートしたら文字化け