Zenn
🐬

collation_connection のはたらきを確認(Collation Coercibility)

2023/02/02に公開
MySQL
collation
tech

tl;dr

  • collation_connection を指定しても実用上あまり意味がないことについて MySQL のドキュメントの ↓ を動作確認した

https://dev.mysql.com/doc/refman/8.0/ja/server-system-variables.html#sysvar_collation_database

接続文字セットの照合順序。collation_connection は、リテラル文字列の比較に重要です。 カラム値と文字列を比較する場合、collation_connection は関係ありません。

  • リテラルとリテラルを比較するときに、このパラメータが作用するが、リテラルとカラム値を比較するときは影響しない

仕様と想定される動作

  • collation_connection はリテラルの照合順序を決める
  • 複数のオペランドが異なる照合順序を持つとき Collation Coercibility によって、照合順序が決まる
  • Coercibility が小さいほうの照合順序が優先され、カラムの Coercibility は 2 で、リテラルは 4 なので、WHERE 句でカラムに保持されているデータと検索したいリテラルを比較すると、カラムの照合順序(カラム、テーブル、データベースのどこで定義されたかによらず)によって決まる。
  • ので collation_connection で、結果が変わることは実用上はない

というのを知識として知っていたが、実際に動きをみたことがなかったので、試してみた。

動作としては、以下が期待される

  • リテラルどうしの比較をしたとき collation_connecion によって結果が異なる
  • リテラルとカラム、カラム相互の比較をしたとき collation_connecion によって結果は変わらない。

比較に使う照合順序

  • MySQL 8.0.32 で utf8mb4_0900_as_ci と utf8mb4_general_ci を比較
    • SET NAMES や SET PERSIST による照合順序の指定はない環境
  • utf8mb4_0900_as_ci では次のように判定される
    • 'は' と 'ぱ' が同じ
    • '🍣' と '🍺' は違う
    • 拗音を識別しない
  • これに対し utf8mb4_general_ci では
    • 'は' と 'ぱ' が違う
    • '🍣' と '🍺' は同じ
    • 拗音を識別する

確認したこと

  • 2 つのデータベース(dgen, d900)を作成し、それぞれ照合順序が異なるようにすること
    • 両方のデータベースに同じかたちのテーブルを作成し、カラムに明示的に照合順序を指定した場合はその照合順序が、カラムおよびテーブルに照合順序を指定しない場合データベースの照合順序がそれぞれ指定される
    • カラム間の比較がカラムの照合順序によって決まること
    • カラムとリテラルの比較がカラムの照合順序によって決まり collation_connection の変更によって異ならないこと
  • リテラルの比較をしたとき collation_connection を変更することで、比較結果が異なること

動作の記録

データベースとテーブルの作成、カラムの照合順序の確認

mysql> create database dgen character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.01 sec)

mysql> create table dgen.t (
    ->     c00 int not null auto_increment primary key,
    ->     c10 varchar(32) not null default '',
    ->     c11 varchar(32) not null default '',
    ->     c20 varchar(32) character set utf8mb4 not null default '' ,
    ->     c21 varchar(32) character set utf8mb4 not null default '' ,
    ->     c30 varchar(32) character set utf8mb4 collate utf8mb4_general_ci not null default '',
    ->     c31 varchar(32) character set utf8mb4 collate utf8mb4_general_ci not null default '');
Query OK, 0 rows affected (0.02 sec)

mysql> create database d900 character set utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> create table d900.t (
    ->     c00 int not null auto_increment primary key,
    ->     c10 varchar(32) not null default '',
    ->     c11 varchar(32) not null default '',
    ->     c20 varchar(32) character set utf8mb4 not null default '' ,
    ->     c21 varchar(32) character set utf8mb4 not null default '' ,
    ->     c30 varchar(32) character set utf8mb4 collate utf8mb4_general_ci not null default '',
    ->     c31 varchar(32) character set utf8mb4 collate utf8mb4_general_ci not null default '');
Query OK, 0 rows affected (0.02 sec)

mysql> select schema_name as d, default_character_set_name as `char`, default_collation_name as `collate` from information_schema.schemata where schema_name like 'd___';
+------+---------+--------------------+
| d    | char    | collate            |
+------+---------+--------------------+
| dgen | utf8mb4 | utf8mb4_general_ci |
| d900 | utf8mb4 | utf8mb4_0900_ai_ci |
+------+---------+--------------------+
2 rows in set (0.00 sec)

mysql> select table_schema as d, table_name as `table`, table_collation as `collate` from information_schema.tables where table_schema like 'd___';
+------+-------+--------------------+
| d    | table | collate            |
+------+-------+--------------------+
| d900 | t     | utf8mb4_0900_ai_ci |
| dgen | t     | utf8mb4_general_ci |
+------+-------+--------------------+
2 rows in set (0.01 sec)

mysql> select table_schema as d, table_name as `table`, column_name as `column`, character_set_name as `char`, collation_name as `collate` from information_schema.columns where table_schema like 'd___' and data_type like '%char%';
+------+-------+--------+---------+--------------------+
| d    | table | column | char    | collate            |
+------+-------+--------+---------+--------------------+
| dgen | t     | c10    | utf8mb4 | utf8mb4_general_ci |
| dgen | t     | c11    | utf8mb4 | utf8mb4_general_ci |
| dgen | t     | c20    | utf8mb4 | utf8mb4_0900_ai_ci |
| dgen | t     | c21    | utf8mb4 | utf8mb4_0900_ai_ci |
| dgen | t     | c30    | utf8mb4 | utf8mb4_general_ci |
| dgen | t     | c31    | utf8mb4 | utf8mb4_general_ci |
| d900 | t     | c10    | utf8mb4 | utf8mb4_0900_ai_ci |
| d900 | t     | c11    | utf8mb4 | utf8mb4_0900_ai_ci |
| d900 | t     | c20    | utf8mb4 | utf8mb4_0900_ai_ci |
| d900 | t     | c21    | utf8mb4 | utf8mb4_0900_ai_ci |
| d900 | t     | c30    | utf8mb4 | utf8mb4_general_ci |
| d900 | t     | c31    | utf8mb4 | utf8mb4_general_ci |
+------+-------+--------+---------+--------------------+
12 rows in set (0.01 sec)
  • サンプルデータ
mysql> insert into dgen.t (c10, c11, c20, c21, c30, c31) values ('はは','ぱぱ','はは','ぱぱ','はは','ぱぱ'), ('🍣','🍺','🍣','🍺','🍣','🍺'), ('びょういん','び よういん','びょういん','びよういん','びょういん','びよういん');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into d900.t (c10, c11, c20, c21, c30, c31) values ('はは','ぱぱ','
はは','ぱぱ','はは','ぱぱ'), ('🍣','🍺','🍣','🍺','🍣','🍺'), ('びょういん','
よういん','びょういん','びよういん','びょういん','びよういん');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

カラムデータの比較

  • カラム同士の比較もカラムとリテラルの比較も collation_connection によって変化しない
    • カラムの照合順序が utf8mb4_0900_ai_ci だと'は'と'ぱ'は同じで'🍣'と'🍺'は違う
mysql> set collation_connection=utf8mb4_0900_ai_ci;select @@collation_connection;
Query OK, 0 rows affected (0.00 sec)

+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci     |
+------------------------+
1 row in set (0.00 sec)

mysql> select c10 as `は/ぱ`, c20 = c21 as `utf8mb4_0900_ai_ci columns`, 'はは' = c21 as `utf8mb4_0900_ai_ci column/literal`from dgen.t where c10 = 'はは' AND (c20 = c21 or c21 = 'はは');
+---------+----------------------------+-----------------------------------+
| は/ぱ   | utf8mb4_0900_ai_ci columns | utf8mb4_0900_ai_ci column/literal |
+---------+----------------------------+-----------------------------------+
| はは    |                          1 |                                 1 |
+---------+----------------------------+-----------------------------------+
1 row in set (0.00 sec)

mysql> select c10 as `は/ぱ`, c30 = c31 as `utf8mb4_general_ci columns`, 'はは' = c31 as `utf8mb4_general_ci column/literal`from dgen.t where c10 = 'はは' AND (c30 = c31 or c31 = 'はは');
Empty set (0.00 sec)

mysql> select c10 as `🍣/🍺`, c20 = c21 as `utf8mb4_0900_ai_ci columns`, '🍣' = c21 as `utf8mb4_0900_ai_ci column/literal` from dgen.t where c10 = '🍣' AND (c20 = c21 or c21 = '🍣');
Empty set, 1 warning (0.00 sec)

mysql> select c10 as `🍣/🍺`, c30 = c31 as `utf8mb4_general_ci columns`, '🍣' = c31 as `utf8mb4_general_ci column/literal` from dgen.t where c10 = '🍣' AND (c30 = c31 or c31 = '🍣');
+------+----------------------------+-----------------------------------+
| ?/?  | utf8mb4_general_ci columns | utf8mb4_general_ci column/literal |
+------+----------------------------+-----------------------------------+
| 🍣     |                          1 |                                 1 |
+------+----------------------------+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

リテラルの比較

  • リテラルどうしの比較では collation_connection によって結果がかわる
    • utf8mb4_0900_ai_ci だと'は'と'ぱ'は同じで'🍣'と'🍺'は違う
mysql> set collation_connection=utf8mb4_0900_ai_ci;select @@collation_connection;
Query OK, 0 rows affected (0.01 sec)

+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci     |
+------------------------+
1 row in set (0.00 sec)

mysql> select 'はは' = 'ぱぱ' as papa, '🍣' = '🍺' as beer, 'びょういん' = 'びよういん' as hospital;
+------+------+----------+
| papa | beer | hospital |
+------+------+----------+
|    1 |    0 |        1 |
+------+------+----------+
1 row in set (0.00 sec)

mysql> set collation_connection=utf8mb4_general_ci;select @@collation_connection;
Query OK, 0 rows affected (0.00 sec)

+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_general_ci     |
+------------------------+
1 row in set (0.00 sec)

mysql> select 'はは' = 'ぱぱ' as papa, '🍣' = '🍺' as beer, 'びょういん' = 'びよういん' as hospital;
+------+------+----------+
| papa | beer | hospital |
+------+------+----------+
|    0 |    1 |        0 |
+------+------+----------+
1 row in set (0.00 sec)

補足

mysql> select coercibility(concat(c20 collate utf8mb4_general_ci,c21)), coercibility(concat(c10,c21)), coercibility(concat(c10, c11)), coercibility(user()), coercibility(concat('🍣','🍺')), coercibility(now()),  coercibility(NULL) from dgen.t limit 1\G
*************************** 1. row ***************************
coercibility(concat(c20 collate utf8mb4_general_ci,c21)): 0
                           coercibility(concat(c10,c21)): 1
                          coercibility(concat(c10, c11)): 2
                                    coercibility(user()): 3
                           coercibility(concat('?','?')): 4
                                     coercibility(now()): 5
                                      coercibility(NULL): 6
1 row in set (0.01 sec)

おわりに

  • collation_connection の設定を確認できた
    • カラム値を concat で結合したとしても coercibility は 2 のままなので、このパラメータが実用上便利になる局面はなさそう
  • カラム定義で character set を指定すると、Database の collation ではなく、カラムに指定した character set の default に定義されている照合順序がカラムの collate になることに気付いた

参考

COLLATE によってどのような文字が識別されるかについて下記を参考にしました。

Discussion