MySQLのcollationの動作を体系的に理解する
はじめに
collationとは二つの文字の間の順序を定義するものです。こう言われるととても単純に聞こえるのですが、MySQLのcollationの詳細な動作は実は結構複雑です。
この記事はcollationの挙動に関する体系的な解説と様々な具体例を元にcollationに対する理解を深め、collationの問題のトラブルシューティングの筋道を立てる事を目的としています。なお、この記事は大まかなcollationの動作の説明を目的としており、全てを網羅しているわけではありません。詳細な動作はMySQLの公式ドキュメントの方が丁寧ですので実際のトラブルシューティングではドキュメントもご活用ください。
なお、この記事での検証はMySQL8.0.31を利用しています。
collationの基礎
collationは冒頭で説明したように二つの文字の順序関係や同値関係を決めるものです。collationとセットで扱うものにcharacter setがありますが、character setが扱える文字を定義するとすれば、collationはcharacter setの文字をどの順序で並べるかを定義するものといえます。collationは一つもcharacter setに複数ある事があり、例えばアルファベットの大文字と小文字を区別するcollationと区別しないcollationや、ドイツ語などで用いられるウムラウト文字をアルファベット順でどこに置くかでいくつかのcollationがあったりします。
まずはイメージを掴むために実際に動作をさせてみます。
collationはクエリ中のあらゆる文字列データに対して設定されています。文字列のcollationはCOLLATION関数で確認できます。以下の一つ目の例では'test'という文字列のcollation、二つ目の例ではuserテーブルのレコードを取得してそのnameカラムの値のcollationを確認しています。
mysql> select collation('test'); +--------------------+ | collation('test') | +--------------------+ | utf8mb4_0900_ai_ci | +--------------------+ mysql> select collation(name) from user limit 1; +--------------------+ | collation(name) | +--------------------+ | utf8mb4_0900_ai_ci | +--------------------+
あまりやらないと思いますが文字列のcollationを明示的に指定する事もできます。以下の例では'test'という文字列にutf8mb4_general_ciを明示的に指定しています。
mysql> select collation('test' collate utf8mb4_general_ci); +-----------------------------------------------+ | collation('test' collate utf8mb4_general_ci) | +-----------------------------------------------+ | utf8mb4_general_ci | +-----------------------------------------------+
collationによって一致関係や順序関係は変化します。以下の一つ目はアルファベットの大文字と小文字を区別しないutf8mb4_0900_ai_ciの場合の'A'と'a'の等号関係が成り立ちますが、大文字と小文字を区別するutf8mb4_0900_as_csでは等号関係が成り立たない事がわかります。
mysql> select 'A' collate utf8mb4_0900_ai_ci = 'a' collate utf8mb4_0900_ai_ci; +-----------------------------------------------------------------+ | 'A' collate utf8mb4_0900_ai_ci = 'a' collate utf8mb4_0900_ai_ci | +-----------------------------------------------------------------+ | 1 | +-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select 'A' collate utf8mb4_0900_as_cs = 'a' collate utf8mb4_0900_as_cs; +-----------------------------------------------------------------+ | 'A' collate utf8mb4_0900_as_cs = 'a' collate utf8mb4_0900_as_cs | +-----------------------------------------------------------------+ | 0 | +-----------------------------------------------------------------+ 1 row in set (0.00 sec)
二つの文字列が異なるcollationの場合、それらを比較しようとするとどうなるでしょうか？一般的な感覚だと順序付けが異なる文字列の比較はできないのでエラーが発生すると思います。実際文字列に異なるcollationを設定して比較しようとした以下のケースではIllegal mix of collationsというエラーが発生します。
mysql> select 'A' collate utf8mb4_0900_as_cs = 'a' collate utf8mb4_0900_ai_ci; ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_as_cs,EXPLICIT) and (utf8mb4_0900_ai_ci,EXPLICIT) for operation '='
ですが、このあと見ていくように実際にはcollationが異なる文字列を比較してもエラーが発生せずに動作する事がほとんどで、中にはエラーは発生しないがインデックスはちゃんと使ってくれないといった厄介なケースもあります。この記事ではなぜこういった挙動が発生するのかを理解できるようになる事を目指します。
この記事で行うcollationの動作の解説の概要
以下ではcollationの詳細な動作の解説を行っていきますが、ここでは先に全体像の説明を行います。
MySQLのドキュメントや実際の挙動の理解を進める中で、collationの挙動は大まかには以下の二つのステップに分ける事で分かりやすくなると考えました。
- 各文字列のcollationを決定する
- collationが異なる文字列同士の比較が行われた場合の挙動の決定方法
collationのトラブルはだいたいcollationが異なる文字列を比較した時に発生するので、そもそも各文字列のcollationがどのように決まるかを理解し、その上で異なる場合にどのようなロジックで挙動が決まるかを理解すれば良いというのは自然な分け方だと思います。
その上で各ステップについて以下のトピックが複雑かつ重要であると感じました。
各文字列のcollationを決定する上では以下のトピック
- 文字列の定義のされ方によりcollationがどのように決まるかのロジック
- 特にカラム定義のcollationがどのように決まるかのロジック
collationが異なる文字列同士の比較が行われた場合の挙動を決定する上では以下のトピック
- 文字列の定義のされ方によるcollationの強制性
- collation間の包含関係
この記事ではこれらの各トピックに関する解説を実例を交えながら行っていきます。
各文字列のcollationを決定する
まずはクエリ中に発生する文字列のcollationの決定方法について説明します。先ほど説明したようにここでは特に複雑で重要な「文字列の定義のされ方でcollationがどのように決まるか」と「特にカラム定義のcollationがどのように決まるか」の二つを解説します。
文字列の定義のされ方によりcollationがどのように決まるかのロジック
文字列には様々な定義の仕方があります。ここではよく使われる文字列の定義の仕方でどのようにcollationが定まるかを見ていきます。
文字列リテラル
ユーザーがクエリを実行する時に渡すような文字列リテラルのcollationはcollation_connectionで決まります。以下では'test'が文字列リテラルで、collation_connectionのセッション変数を変更する事で'test'のcollationが変化する事が確認できます。
mysql> select collation('test'); +--------------------+ | collation('test') | +--------------------+ | utf8mb4_0900_ai_ci | +--------------------+ 1 row in set (0.00 sec) mysql> set local collation_connection=utf8mb4_general_ci; Query OK, 0 rows affected (0.00 sec) mysql> select collation('test'); +--------------------+ | collation('test') | +--------------------+ | utf8mb4_general_ci | +--------------------+ 1 row in set (0.01 sec)
例えばselect * from sample where name='hoge';の'hoge'も同じく文字列リテラルなのでcollation_connectionで決まります。
カラムの文字列
カラムの文字列のcollationの決まり方については後述しますが、確認方法としてはshow create tableを実行してカラムに対して明示的にcollationが指定されていればその値、そうでなければテーブルに紐づくcollationが使われます。
以下ではテーブルのcollationにutf8mb4_0900_ai_ciを設定している場合に、collationを指定していないvarcharカラムのnameではutf8mb4_0900_ai_ciが利用され、明示的にcollationにutf8mb4_general_ciを指定している場合にはutf8mb4_general_ciが利用されている事がわかります。
mysql> show create table sample; +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | sample | CREATE TABLE `sample` ( `id` int DEFAULT NULL, `name` varchar(10) DEFAULT NULL, `name2` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> select collation(name), collation(name2) from sample; +--------------------+--------------------+ | collation(name) | collation(name2) | +--------------------+--------------------+ | utf8mb4_0900_ai_ci | utf8mb4_general_ci | +--------------------+--------------------+
stored procedureの中の値
stored procedureやstored functionの引数や宣言した変数の文字列型のデータや文字列リテラルにもcollationがあります。これらについてはstored procedureに紐づくcollation_connection、引数はstored procedureに紐づくDATABASE_COLLATIONで決まります。
以下では引数(input)、文字列のローカル変数(a)、ユーザー定義変数@b、文字列データそのもののそれぞれのcollationを確認しています。
mysql> select routine_name, routine_definition, collation_connection, database_collation from information_schema.routines where routine_name='test'\G; *************************** 1. row *************************** ROUTINE_NAME: test ROUTINE_DEFINITION: begin declare a varchar(10); set a='a'; set @b='a'; select collation(input), collation(a), collation(@b), collation('a'); end COLLATION_CONNECTION: utf8mb4_general_ci DATABASE_COLLATION: utf8mb4_0900_ai_ci mysql> call test('a'); +--------------------+--------------------+--------------------+--------------------+ | collation(input) | collation(a) | collation(@b) | collation('a') | +--------------------+--------------------+--------------------+--------------------+ | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | utf8mb4_general_ci | utf8mb4_general_ci | +--------------------+--------------------+--------------------+--------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)
この結果から以下の挙動が分かります。
- 引数や内部で宣言された変数のcollationはstored procedureに紐づくDATABASE_COLLATIONで決まる
- stored procedure中の文字列リテラルはstored procedureに紐づくCOLLATION_CONNECTIONで決まる
- @で変数を宣言するユーザー定義変数では引数の文字列のcollationが引き継がれるため今回は文字列リテラルと同じCOLLATION_CONNECTIONの値が使われた。これはおそらくユーザー定義変数の動作によるものです。
なお、COLLATION_CONNECTIONやDATABASE_COLLATIONはstored procedureに紐づくものなので例えばset local collation_connection=utf8mb4_0900_ai_ciなどとした状態でこのstored procedureを実行しても実行結果は変化しません。
明示的なcollationの指定
冒頭に述べたようにCOLLATE句を用いて明示的にcollationを設定する事が可能で、この場合は明示的な設定が優先されます。 また、以下のようにソート順で明示的にcollationを設定することもできます。
select name from sample order by name collate utf8mb4_general_ci;
カラムのcollationがどのように決まるかのロジック
カラムのcollationはcreate tableやadd columnを実行する際にcollationを渡すというわかりやすいやり方もありますが、特に指定しなくても自動でcollationが設定されます。ここではその挙動の解説を行います。分量は多いですが大半は自然に連想できる動作だと思いますので流し読みで大丈夫だと思います。また、大体ドキュメントに書いてある内容です。
まず、MySQLはサーバー、データベース、テーブル、カラムの各レイヤーでcharacter setとcollationの設定を持っています。以下ではそれぞれの場合のcharacter setとcollationの決まり方を見ていきます。
サーバーレベルでのcharacter setとcollation
character_set_serverおよびcollation_serverというシステム変数で設定されています。
データベースレベルでのcharacter setとcollation
データベースレベルでのcharacter setとcollationは以下のように決まります。
- create database時にcharacter setとcollationを両方指定している場合は指定した値が使われます。
- collationのみ設定している場合はそのcollationと関連するcharacter setが使われます。
- character setのみが設定されている場合、そのcharacter setのdefault collationが使われます。
- どちらも設定していない場合サーバーレベルのcharacter setとcollationが使われます。
character setのみが設定されている場合以外は自然だと思うので、この場合について補足をします。
まず、default collationとはMySQLが事前に決めている各character setのデフォルトのcollationで、これは以下のようにshow character setで確認できます。
mysql> show character set like 'utf8mb4'; +---------+---------------+--------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+---------------+--------------------+--------+ | utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 | +---------+---------------+--------------------+--------+
もしcreate databaseを実行する際にcharacter setのみを指定する場合はこのdefault collationが利用されるという事に注意してください。特に、character_set_serverと同じ文字セットであっても明示的に文字セットを指定するとcollation_serverの値ではなくdefault collationが使われるという点に注意です。
以下の例ではcollation_serverにutf8mb4_general_ciを指定した時にcharacter setもcollationも設定しなかったutf8mb4_1ではcollationがcollation_serverと同じになり、character setをutf8mb4に設定しつつcollationは設定しなかったデータベース、utf8mb4_2ではdefault collationであるutf8mb4_0900_ai_ciが用いられている事がわかります。
mysql> show variables like 'collation_server'; +------------------+--------------------+ | Variable_name | Value | +------------------+--------------------+ | collation_server | utf8mb4_general_ci | +------------------+--------------------+ 1 row in set (0.01 sec) mysql> show create database utf8mb4_1; +-----------+-------------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +-----------+-------------------------------------------------------------------------------------------------------------------------------------+ | utf8mb4_1 | CREATE DATABASE `utf8mb4_1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +-----------+-------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> create database utf8mb4_2 character set utf8mb4; Query OK, 1 row affected (0.07 sec) mysql> show create database utf8mb4_2; +-----------+-------------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +-----------+-------------------------------------------------------------------------------------------------------------------------------------+ | utf8mb4_2 | CREATE DATABASE `utf8mb4_2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +-----------+-------------------------------------------------------------------------------------------------------------------------------------+
この挙動はテーブルやカラムでも同様の挙動なので気をつけてください。
なお、default collationの設定は基本的にユーザーの方で変更する事ができませんが、utf8mb4に限りdefault_collation_for_utf8mb4でutf8mb4_general_ciかutf8mb4_0900_ai_ciに限り選択を行う事ができます。ただし、これは後方互換性のため一時的な措置のようで、実際に以下のように設定してみるとわかるようにdeplication warningが発生します。
mysql> set global default_collation_for_utf8mb4 ='utf8mb4_general_ci'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+--------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------------------+ | Warning | 1681 | Updating 'default_collation_for_utf8mb4' is deprecated. It will be made read-only in a future release. | +---------+------+--------------------------------------------------------------------------------------------------------+
テーブルレベルのcharacter setとcollation
大体データベースと同様になります。
- character setとcollationを両方指定している場合は指定した値が使われます。
- collationのみ設定している場合はそのcollationと関連するcharacter setが使われます。
- character setのみが設定されている場合、そのcharacter setのdefault collationが使われます。
- どちらも設定していない場合データベースレベルのcharacter setとcollationが使われます。
カラムレベルのcharacter setとcollation
データベースレベルとテーブルレベルの挙動を理解していたら大体同じようにわかると思います。
- character setとcollationを両方指定している場合は指定した値が使われます。
- collationのみ設定している場合はそのcollationと関連するcharacter setが使われます。
- character setのみが設定されている場合、そのcharacter setのdefault collationが使われます。
- どちらも設定していない場合テーブルレベルのcharacter setとcollationが使われます。
collation_databaseの役割
本筋から少し離れますが、collationに関するvariablesを確認するとまだ出てきていない変数、collation_databaseというものがあります。
mysql> show variables like '%collation_%'; +-------------------------------+--------------------+ | Variable_name | Value | +-------------------------------+--------------------+ | collation_connection | utf8mb4_0900_ai_ci | | collation_database | utf8mb4_0900_ai_ci | | collation_server | utf8mb4_0900_ai_ci | | default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci | +-------------------------------+--------------------+
これは何をやっているかというと、現在のデータベース(use ...で指定したデータベース)のcollationを表示してくれるものになります。この変数はユーザーが設定するものではないのでご注意ください。
collationの定義のされ方まとめ
おおまかにはユーザーが渡した文字列はcollation_connectionで決まる事、カラムのデータはカラム定義でcollationが決まっているという事と、カラム定義のcollationはcharacter set含めて特に指定しなければサーバー、データベース、テーブル、カラムの各設定が引き継がれているという事を理解すれば大丈夫だと思います。
collationが異なる文字列同士の比較が行われた場合の挙動の決定方法
ここまでで各文字列のcollationがどのように決まるかが分かったと思うので、ここからでは実際のクエリに複数のcollationが存在する場合の動作を説明します。ここで重要な概念は「collationの強制性」と「collationの包含関係」の二つです。以下でそれぞれについて説明していきます。
collationの強制性
以下の例はcollation_connectionがutf8mb4_general_ciとなっている状況でcollationがutf8mb4_0900_ai_ciのnameカラムと文字列リテラルを比較して検索をする例です。異なるcollationの比較になりますがどういった動作になるでしょうか？
mysql> show variables like 'collation_connection'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_general_ci | +----------------------+--------------------+ 1 row in set (0.03 sec) mysql> show create table test; +-------+----------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> select * from test where name='a';
'a'はcollation_connectionで決まるのでutf8mb4_general_ci、対象のカラムはutf8mb4_0900_ai_ciと異なるのでエラーが発生すると思いたいところですが、実はクエリは実行可能です。
これは各文字列の定義のされ方によってcollationが決まるだけでなく強制性というものも設定され、異なるcollationの文字列の比較が行われる場合は強制性がより0に近い文字列のcollationを使う挙動になっているためです。
なお、各文字列のcollationの強制性はCOERCIBILITY関数で確認できます。
強制性はどのように定まるのか
強制性はその文字列の定義のされ方によって定まります。「文字列の定義のされ方によりcollationがどのように決まるかのロジック」で説明した各場合については以下のように強制性が設定されています。0の優先度が一番高く、1, 2, 3…と優先度が低くなります。
- 文字列リテラルは4
- カラムの文字列は2
- stored procedureの引数や変数は2
- 明示的にcollationの定義をした場合は0
先程の例では文字列リテラルの強制性が4でカラムの文字列の強制性が2なのでカラムの文字列のcollation、つまりutf8mb4_0900_ai_ciに合わせてクエリが実行されたことになります。では次のように検索に用いる文字列リテラルのcollationを明示的に指定するとどうなるでしょうか。
select * from test where name='a' collate utf8mb4_general_ci;
この場合'a'の強制性は0になるのでutf8mb4_general_ciに合わせて実行されます。 以下のように大文字小文字を区別するcollationとそうでないcollationで結果の変化を見るとわかりやすいと思います。
mysql> select * from test where name='a'; +------+ | name | +------+ | a | | A | +------+ 2 rows in set (0.00 sec) mysql> select * from test where name='a' collate utf8mb4_0900_as_cs; +------+ | name | +------+ | a | +------+ 1 row in set (0.00 sec)
異なるcollationを用いられた時にインデックスが使えない事がある
先ほどの最後の例のように、MySQLはcollationが異なる場合も強制性が異なる場合は強制性が0に近い方に合わせる動作をする事でエラーを返さずに実行が可能になります。しかし、文字列データをキーとするインデックスはその文字列のcollation順になっているので、異なるcollationで検索をされるとうまくインデックスが使えない場合があります。
以下の例ではutf8mb4_0900_ai_ciをnameカラムに用いており、かつnameカラムのインデックスを持つテーブルについて、検索に用いる文字列リテラルのcollationを指定しない場合と明示的にnameカラムと異なるcollationを指定した場合のexplainの結果を確認しています。検索時に明示的に文字列リテラルのcollationにカラムと異なるcollationを設定する後者ではインデックスが効かなくなりフルインデックススキャンしています。
mysql> explain select * from test where name='a'; +--+-----------+-----+----------+----+-------------+----+-------+-----+----+--------+-----------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +--+-----------+-----+----------+----+-------------+----+-------+-----+----+--------+-----------+ | 1 | SIMPLE | test | NULL | ref | name | name | 43 | const | 1 | 100.00 | Using index | +--+-----------+-----+----------+----+-------------+----+-------+-----+----+--------+-----------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from test where name='a' collate utf8mb4_general_ci; +--+-----------+-----+----------+-----+-------------+----+-------+----+----+--------+------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +--+-----------+-----+----------+-----+-------------+----+-------+----+----+--------+------------------------+ | 1 | SIMPLE | test | NULL | index | name | name | 43 | NULL | 4 | 25.00 | Using where; Using index | +--+-----------+-----+----------+-----+-------------+----+-------+----+----+--------+------------------------+ 1 row in set, 3 warnings (0.00 sec)
強制性が同じ場合の動作
ここまでで文字列のcollationの強制性が異なる場合はエラーを発生せずに強制性が0に近い方に合わせる事がわかりました。ですが、しばしば強制性が同じケースがあります。一番分かりやすいのはcollationの異なるカラムのjoinで、どちらもカラムに格納されたデータなのでcollationの強制性は2です。以下ではtestテーブルのnameカラムはutf8mb4_0900_ai_ci、test2テーブルのnameカラムはutf8mb4_general_ciとなっており、これらのカラムでjoinします。
mysql> show create table test; +-------+----------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> show create table test2; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ | test2 | CREATE TABLE `test2` ( `name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> select test.name from test join test2 on test.name=test2.name; ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
Illegal mix of collationsのエラーが発生しました。このように基本的には強制性が同一かつcollationが異なる場合、エラーを発生します。以下の例も同様にエラーを発生します。
mysql> select 'A' collate utf8mb4_0900_as_cs = 'a' collate utf8mb4_0900_ai_ci; ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_as_cs,EXPLICIT) and (utf8mb4_0900_ai_ci,EXPLICIT) for operation '='
この場合は両方とも強制性が0ですね。このように、強制性が同じ場合は通常エラーを発生します。ですが、この動作には実は例外があります。次に最後のトピック、collation間の包含関係の解説をします。
collation間の包含関係
以下はcollationの異なるカラムをjoinする例です。ここではtestテーブルのnameカラムはutf8mb4_0900_ai_ci、test3テーブルのnameカラムはutf8mb4_binです。このクエリは少し上の例ではエラーが発生しましたが、今回はなぜかエラーが発生しません。
mysql> show create table test; +-------+----------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> show create table test3; +-------+------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------+ | test3 | CREATE TABLE `test3` ( `name` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-------+------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select test.name from test join test3 on test.name=test3.name; Empty set (0.00 sec)
なぜこれはエラーが起きないのでしょうか？
実はutf8mb4_binはutf8mb4_0900_ai_ciのスーパーセットになっています。このような場合、utf8mb4_binを用いて比較が可能になります。一方でutf8mb4_general_ciとuf8_mb4_0900_ai_ciの例では二つの間に包含関係が無いためにエラーになりました。
この動作の正当性の説明をするとutf8mb4_binはバイナリ順序での比較なのでcollationがutf8mb4_ai_ciの文字列もバイナリ順序で比較ができるのでutf8mb4_binに寄せて実行できるから実行したという事なのですが、大事なのはこのようにcollation間に包含関係がある場合、スーパーセットに寄せて比較が行われるという事です。また、この場合はutf8mb4_binでない方のテーブルは文字列リテラルにカラムと異なるcollationを指定した場合の例と同様に実際のインデックスと異なる照合順序で比較する事になるのでインデックスが使えない可能性が高い点には気をつけてください。
最後に、これは補足ですがcollationを明示的に指定している場合は包含関係があってもエラーになるようです。以下では両方とも強制性が0で包含関係がありますが、明示的にcollationを指定しているのでエラーになります。エラーメッセージにEXPLICITの文字があります。
mysql> select 'A' collate utf8mb4_bin = 'a' collate utf8mb4_0900_ai_ci; ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_bin,EXPLICIT) and (utf8mb4_0900_ai_ci,EXPLICIT) for operation '='
collationが異なる文字列同士の比較が行われた場合の挙動の決定方法のまとめ
collationが異なる文字列同士の比較時の挙動をまとめると基本的には以下のフロー図になります。
最後に
この記事では複雑でとっつきにくいMySQLのcollationの動作の体系的な解説を目指しました。
要点としては各文字列データのcollationが何かを確認し、異なるcollationの比較が発生した時はここで紹介したフローチャートに従ってどのような動作をするか考えるという感じです。複雑で大変だと思いますが何かcollation関連のトラブルにあったら参考にしてもらえたら幸いです。
