MySQLのCharacter Sets, Collations, Unicodeのページを読んでみる その1
suzuki-naviこのページを読んでみる。
suzuki-navi
このページは、そもそもcharacter setとcollationとはなにかという一般的な話。
suzuki-navi
サポートされているcharacter setの一覧を見るには
SHOW CHARACTER SET;
または
SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;
以下のようにすると名前が utf で始まるものだけをリストできる。
SHOW CHARACTER SET LIKE 'utf%';
SHOW COLLATION;
こんな感じでcollationがいっぱいリストされる。
+-----------------------------+----------+-----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+-----------------------------+----------+-----+---------+----------+---------+---------------+
| armscii8_bin | armscii8 | 64 | | Yes | 1 | PAD SPACE |
| armscii8_general_ci | armscii8 | 32 | Yes | Yes | 1 | PAD SPACE |
| ascii_bin | ascii | 65 | | Yes | 1 | PAD SPACE |
| ascii_general_ci | ascii | 11 | Yes | Yes | 1 | PAD SPACE |
| big5_bin | big5 | 84 | | Yes | 1 | PAD SPACE |
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 | PAD SPACE |
| binary | binary | 63 | Yes | Yes | 1 | NO PAD |
| cp1250_bin | cp1250 | 66 | | Yes | 1 | PAD SPACE |
| cp1250_croatian_ci | cp1250 | 44 | | Yes | 1 | PAD SPACE |
| cp1250_czech_cs | cp1250 | 34 | | Yes | 2 | PAD SPACE |
| cp1250_general_ci | cp1250 | 26 | Yes | Yes | 1 | PAD SPACE |
| cp1250_polish_ci | cp1250 | 99 | | Yes | 1 | PAD SPACE |
| cp1251_bin | cp1251 | 50 | | Yes | 1 | PAD SPACE |
| cp1251_bulgarian_ci | cp1251 | 14 | | Yes | 1 | PAD SPACE |
| cp1251_general_ci | cp1251 | 51 | Yes | Yes | 1 | PAD SPACE |
| cp1251_general_cs | cp1251 | 52 | | Yes | 1 | PAD SPACE |
以下のSQLでも同様。
SELECT * FROM INFORMATION_SCHEMA.COLLATIONS;
character setごとに1つまたは複数のcollationが存在する。character setごとに1つのデフォルトのcollationが指定されている。collationはいずれか1つのみのcharacter setに紐づく。
utf8mb4に対応したcollationを見るには
SHOW COLLATION WHERE Charset = 'utf8mb4';
CREATE TABLE t1 (
c1 CHAR(1) CHARACTER SET latin1,
c2 CHAR(1) CHARACTER SET ascii
);
INSERT INTO t1 VALUES ('a','b');
SELECT CONCAT(c1,c2) FROM t1;
+---------------+
| CONCAT(c1,c2) |
+---------------+
| ab |
+---------------+
MySQL [test]> show variables like 'char%';
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8mb3 |
| character_set_connection | utf8mb3 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb3 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
mysqlコマンドのコマンドラインパラメータに--default-character-set utf8mb4を追加したら以下のようになった。
MySQL [test]> 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 | utf8mb3 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
接続後に変えることもできる。接続後に以下の状態だったとしても
MySQL [test]> show variables like 'char%';
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8mb3 |
| character_set_connection | utf8mb3 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb3 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.004 sec)
SET NAMES ... を実行すれば変わる。
MySQL [test]> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.001 sec)
MySQL [test]> 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 | utf8mb3 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.004 sec)
テーブル名などのメタデータはUTF-8で保存されている。具体的なcharacter setはcharacter_set_systemで確認できる。
MySQL [test]> SHOW VARIABLES LIKE 'character_set_system';
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| character_set_system | utf8mb3 |
+----------------------+---------+
この例ではメタデータはutf8mb3ということになる。
suzuki-naviちょっと実験。テーブル名にutf8mb3で表現できない文字(𠮟)を使ってみるとどうなるか
MySQL [test]> 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 | utf8mb3 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
CREATE TABLE t_叱る ( t VARCHAR(10) );
CREATE TABLE t_𠮟る ( t VARCHAR(10) );
MySQL [test]> select table_name from information_schema.tables where table_schema = 'test';
+------------+
| TABLE_NAME |
+------------+
| t1 |
| t_?る |
| t_叱る |
+------------+
叱の漢字はutf8mb3で表現できないので ? になった。
suzuki-navi
character setとcollationはサーバ、データベース、テーブル、カラムそれぞれに設定できる。
suzuki-navi
collationの名前は紐づくcharacter setの名前から始まる。
言語に依存するcollationは言語名または言語コードが後ろに付く。例) utf8mb4_tr_0900_ai_ci はトルコ語のcollation
utf8mb4_0900 というのはUnicode Collation Algorithm 9.0.0に従うcollation。
collationの接尾辞の命名規則
| suffix | meaning |
|---|---|
| _ai | Accent-insensitive |
| _as | Accent-sensitive |
| _ci | Case-insensitive |
| _cs | Case-sensitive |
| _ks | Kana-sensitive |
| _bin | Binary |
suzuki-navi
サーバのデフォルトのcharacter setはutf8mb4、collationはutf8mb4_0900_ai_ci。
サーバ起動時にコマンドラインオプションで指定することもできる。
サーバのビルド時に指定することもできる。
suzuki-navi
データベースのcharacter setとcollationはCREATE DATABASEまたはALTER DATABASEで指定できる。
CHARACTER SETだけを指定したらcollationはそのcharacter setのデフォルトcollationが選択される。
COLLATEだけを指定したらcharacter setはそのcollationの紐づくcharacter setが選択される。
いずれも指定されていなかったらデータベース作成時のサーバのcharacter setとcollationがデータベースに設定される。
character_set_databaseとcollation_databaseシステム変数は、接続しているデータベースのcharacter setとcollationを表し、接続先データベースが変わったらシステム変数の値も変わる。
suzuki-navi
テーブルのcharacter setとcollationもCREATE TABLEまたはALTER TABLEで指定できる。
値の選択方法はデータベースの作成時と同様だが、指定されていない場合はデータベースのcharacter setとcollationの値が引き継がれる。
suzuki-navi
CHAR, VARCHAR, TEXT型のカラムもcharacter setとcollationの値を持つ。CREATE TABLEまたはALTER TABLEで指定できる。
値の選択方法はテーブルのcharacter setとcollation選択と同様だが、指定されていない場合はテーブルのcharacter setとcollationの値が引き継がれる。
suzuki-navi
SQLに記述する文字列リテラルにもcharacter setとcollationの値がある。
単純にシングルクオーテーションでくくった文字列はcharacter_set_connectionとcollation_connectionに従う。
いまの接続環境がutf8mb3だとして、
MySQL [test]> show variables like 'char%';
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8mb3 |
| character_set_connection | utf8mb3 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb3 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
MySQL [test]> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb3_general_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
以下のようなことはできる。
MySQL [test]> select 'abc';
+-----+
| abc |
+-----+
| abc |
+-----+
MySQL [test]> select _utf8mb4'abc';
+-----+
| abc |
+-----+
| abc |
+-----+
MySQL [test]> select _utf8mb4'abc' COLLATE utf8mb4_0900_ai_ci;
+------------------------------------------+
| _utf8mb4'abc' COLLATE utf8mb4_0900_ai_ci |
+------------------------------------------+
| abc |
+------------------------------------------+
_utf8mb4みたいな表記をintroducerという。
introducerを指定しないと私のいまの環境では utf8mb3 なので、それとは違うcollationを指定するとエラーになる。
MySQL [test]> select 'abc' COLLATE utf8mb4_0900_ai_ci;
ERROR 1253 (42000): COLLATION 'utf8mb4_0900_ai_ci' is not valid for CHARACTER SET 'utf8mb3'
実験
MySQL [test]> select 'あ';
+-----+
| あ |
+-----+
| あ |
+-----+
MySQL [test]> select _latin1'あ';
+---------+
| ã‚ |
+---------+
| ã‚ |
+---------+
MySQL [test]> select _ascii'あ';
+-----+
| |
+-----+
| ??? |
+-----+
MySQL [test]> select HEX('あ');
+------------+
| HEX('あ') |
+------------+
| E38182 |
+------------+
MySQL [test]> select HEX(_latin1'あ');
+-------------------+
| HEX(_latin1'あ') |
+-------------------+
| E38182 |
+-------------------+
MySQL [test]> select HEX(_ascii'あ');
+------------------+
| HEX(_ascii'あ') |
+------------------+
| E38182 |
+------------------+
なるほど、'あ' というリテラルをバイナリにしてから、_latin1みたいなintroducerを適用しているようだ。introducer自体はバイナリを変更しない。
suzuki-naviこれはなぜこうなる？
MySQL [test]> select _ascii'𠮟';
+------+
| |
+------+
| ???? |
+------+
MySQL [test]> select _utf8mb3'𠮟';
+------+
| 𠮟 |
+------+
| 𠮟 |
+------+
MySQL [test]> select _utf8mb4'𠮟';
+---+
| ? |
+---+
| ? |
+---+
_utf8mb3'𠮟' はMySQLは解釈できていないけど、4バイトのバイナリをそのまま素通りしていて、ターミナルが対応しているからそのまま表示できているのだと思う。_utf8mb4の挙動がわからない。
suzuki-naviset names utf8mb4 したら直った。なるほど。
MySQL [test]> set names utf8mb4;
MySQL [test]> select _utf8mb3'𠮟';
+------+
| ???? |
+------+
| ???? |
+------+
MySQL [test]> select _utf8mb4'𠮟';
+------+
| ? |
+------+
| 𠮟 |
+------+
ヘッダ行が ? になっているのは、character_set_systemがutf8mb3になっているため。
suzuki-navi
NCHARとNATIONAL CHARについて。
suzuki-navi
introducerについて。
introducerはCONVERT()と違って値を変更しない。
introducerが指定されていてCOLLATEがない場合はそのcharacter setのデフォルトのcollationが適用される。
suzuki-naviMySQL [test]> SELECT HEX(X'01' | X'05'), HEX(_binary X'01' | X'05');
+--------------------+----------------------------+
| HEX(X'01' | X'05') | HEX(_binary X'01' | X'05') |
+--------------------+----------------------------+
| 5 | 05 |
+--------------------+----------------------------+
_binaryがないほうは数値になる。_binaryが|演算子の片方にでもあれば結果は_binaryになる。ここでは_binaryがintroducerで、Xは16進リテラルを示す記号。
suzuki-navi
サーバやデータベースなどの設定値としてのcharacter setやcollationとは別に、DB接続のセッション内のみで有効な、サーバとクライアントとの間の通信で使われるcharacter setやcollationの設定値がある。セッションの途中でこの値を変更することもできる。
以下のシステム変数が関わってくる
character_set_clientcharacter_set_connectioncharacter_set_resultscollation_connection
これらを変更するには
-
mysqlコマンドのコマンドラインオプション--default-character-set=charset_name SET NAMES charset_nameSET CHARACTER SET charset_name
SET NAMESについては
SET CHARACTER SETについては