MySQL 8.0がutf8mb4のデフォルトcollationを utf8mb4_0900_ai_ci に変更したことは有名です。
MariaDBも11.5からデフォルトcollationを変更したのですが、 utf8mb4_0900_ai_ci ではなく utf8mb4_uca1400_ai_ci になりました。
この2つのcollationについてはMySQLやMariaDBの識者に任せて、私はMySQLとMariaDBで接続のcollation (collation_connection) がどう設定されるかを調べてみました。
MySQL 8.4のクライアントからサーバーに --default-character-set=utf8mb4 を指定して接続すると、 collation_connection は utf8mb4_0900_ai_ci になります。
utf8mb4のデフォルトcollationが utf8mb4_0900_ai_ci になっていて、クライアントは utf8mb4 のデフォルトcollationのcollation idをハンドシェイクパケットで送信しています。
$ docker run --network host -it mysql:8.4 mysql -h 127.0.0.1 --default-character-set=utf8mb4
...
Server version: 8.4.5 MySQL Community Server - GPL
...
mysql> select @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME='utf8mb4' AND ID<256;
+------------------------+--------------------+-----+------------+-------------+---------+---------------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
+------------------------+--------------------+-----+------------+-------------+---------+---------------+
| utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 | PAD SPACE |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE |
...
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD |
+------------------------+--------------------+-----+------------+-------------+---------+---------------+
27 rows in set (0.01 sec)
ただし、MySQL 8.4同士でも注意が必要なケースがあります。
デフォルトのcharsetがutf8mb4になっていても、 --default-charcter-set=utf8mb4 を指定しないと utf8mb4 にならないことがあります。たとえばmysql:8.4のコンテナのmysqlを使うとこうなります。
$ docker run --rm --network host -it mysql:8.4 mysql -h 127.0.0.1
mysql> show variables like '%_connection';
+--------------------------+-------------------+
| Variable_name | Value |
+--------------------------+-------------------+
| character_set_connection | latin1 |
| collation_connection | latin1_swedish_ci |
+--------------------------+-------------------+
2 rows in set (0.00 sec)
これはmysqlクライアントが、 --default-character-set が指定されていないときにlocaleからcharsetを選択するためです。例えば環境変数 LANG=C.UTF-8 を設定すると utf8mb4 が使われます。
$ docker run --rm --network host -it --env LANG=C.UTF-8 mysql:8.4 mysql -h 127.0.0.1
...
mysql> show variables like '%_connection';
+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_connection | utf8mb4 |
| collation_connection | utf8mb4_0900_ai_ci |
+--------------------------+--------------------+
2 rows in set (0.01 sec)
もう一つ注意する必要があるのがビルド時のオプションです。Homebrewのmysql-clientは -DDEFAULT_COLLATION=utf8mb4_general_ci をつけてビルドされています。(mysqlではこのオプションは消されているのでmysql-clientでも消すPRを作っています。)
mysqlが選択したcharsetが、DEFAULT_COLLATIONのcharsetと一致している場合、charsetのデフォルトのcollationではなく DEFAULT_COLLATIONが使われます。
https://github.com/mysql/mysql-server/blob/ff05628a530696bc6851ba6540ac250c7a059aa7/sql-common/client.cc#L3954-L3958
そのためHomebrewのmysql-clientのmysqlコマンドを使うと utf8mb4_general_ci が使われます。utf8mb4のデフォルトcollationが変わっている訳では無いので、 SET NAMES utf8mb4 を実行すると utf8mb4_0900_ai_ci になります。
$ /opt/homebrew/opt/mysql-client@8.4/bin/mysql -h 127.0.0.1 -uroot --default-character-set=utf8mb4
...
mysql> select @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_general_ci |
+------------------------+
1 row in set (0.00 sec)
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci |
+------------------------+
1 row in set (0.00 sec)
MariaDBは組み込みのutf8mb4のデフォルトcollationをMySQL 5.7時代の utf8mb4_general_ci にしたまま、追加の character_set_collations というシステム変数でutf8mb4のデフォルトcollationを変更しています。試しにこの変数を空にして information_schema.COLLATIONS を見てみると utf8mb4_general_ci がデフォルトであることが分かります。
# $ docker run --rm --name mariadb --network host --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 mariadb:latest --character_set_collations=''
mysql> SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME='utf8mb4' AND ID<256;
+------------------------------+--------------------+------+------------+-------------+---------+---------------------------------------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | COMMENT |
+------------------------------+--------------------+------+------------+-------------+---------+---------------------------------------+
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 | UTF-8 Unicode |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | UTF-8 Unicode |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | |
...
+------------------------------+--------------------+------+------------+-------------+---------+---------------------------------------+
27 rows in set (0.00 sec)
今度はデフォルトの設定でMariaDBを起動してみると、 information_schema.COLLATIONS からデフォルトのcollationが消えました。 character_set_collations 変数を確認するとUnicode系のcharsetに対して *_uca1400_ai_ci をデフォルトのcollationとして設定していることが分かります。
# $ docker run --rm --name mariadb --network host --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 mariadb:latest
mysql> SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME='utf8mb4' AND IS_DEFAULT='Yes';
Empty set (0.00 sec)
mysql> SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME='utf8mb3' AND IS_DEFAULT='Yes';
Empty set (0.00 sec)
mysql> select @@character_set_collations;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| @@character_set_collations |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| utf8mb3=utf8mb3_uca1400_ai_ci,ucs2=ucs2_uca1400_ai_ci,utf8mb4=utf8mb4_uca1400_ai_ci,utf16=utf16_uca1400_ai_ci,utf32=utf32_uca1400_ai_ci |
+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
character_set_collations システム変数は MariaDB 11.2.1 で追加されましたが、 uca1400_ai_ci をデフォルトにしたのは MariaDB 11.5 からです。
MariaDBで collation_connection が設定されるまでの流れは次のようになります。
- mariadbクライアントはmysqlと違い
DEFAULT_COLLATION によるcollationの上書きをしないので、 --default-character-set=utf8mb4 を指定すると utf8mb4_general_ci のID(45)をハンドシェイクパケットで送信します。
- mariadbサーバーは、
utf8mb4_general_ci のIDを受け取ると、これが utf8mb4 のデフォルトのcollationであり、なおかつ character_set_collations に utf8mb4=utf8mb4_uca1400_ai_ci が設定されているので、 utf8mb4_uca1400_ai_ci を使います。
相互接続
--default-character-set=utf8mb4 を指定して接続するときの、MySQL/MariaDB間やバージョン間の挙動をまとめます。
MySQLサーバーは(オプションで無効化されていなければ)ハンドシェイクパケットで指定されたcollationを優先します。
mariadbクライアントからMySQLに接続すると、ハンドシェイクパケットで指定された utf8mb4_general_ci が利用されます。
これは MySQL 5.7 のクライアントから接続された場合も同じです。またHomebrew の mysql-client, mysql-client@8.4, mysql-client@8.0 なども、 -DDEFAULT_COLLATION=utf8mb4_general_ci をつけてビルドされているので utf8mb4_general_ci が利用されます。(近い将来に修正されるかもしれません。)
# $ docker run --rm --name mysql --network host -e MYSQL_ALLOW_EMPTY_PASSWORD=1 mysql:8.4
# mysql -> mysqld では utf8mb4_0900_ai_ci
$ /opt/homebrew/Cellar/mysql@8.4/8.4.5/bin/mysql -uroot -h127.0.0.1 --default-character-set=utf8mb4 -e 'SELECT @@collation_connection'
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci |
+------------------------+
# mariadb -> mysqld では utf8mb4_general_ci.
# SET NAMES utf8mb4 で utf8mb4_0900_ai_ci になる
$ /opt/homebrew/Cellar/mariadb/11.7.2/bin/mariadb -uroot -h127.0.0.1 --default-character-set=utf8mb4
Server version: 8.4.5 MySQL Community Server - GPL
MySQL [(none)]> SELECT @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_general_ci |
+------------------------+
1 row in set (0.008 sec)
MySQL [(none)]> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.005 sec)
MySQL [(none)]> SELECT @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci |
+------------------------+
1 row in set (0.005 sec)
# Homebrewのmysql-client -> mysqld でも utf8mb4_general_ci
$ /opt/homebrew/opt/mysql-client@8.4/bin/mysql -uroot -h127.0.0.1 --default-character-set=utf8mb4 -e 'SELECT @@collation_connection'
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_general_ci |
+------------------------+
MariaDBサーバーはハンドシェイクパケットで utf8mb4_general_ci が指定されていると、 character_set_collations で指定されたcollation(デフォルトでは utf8mb4_uca1400_ai_ci)を利用します。 ただし、ハンドシェイクパケットで指定されたcollationがそのcharsetのデフォルトcollationでなければ、 character_set_collations は無視されます。
MySQL 8.xのクライアントは、(ビルド時にDEFAULT_COLLATIONが指定されていなければ) utf8mb4のデフォルトcollationである utf8mb4_0900_ai_ci をハンドシェイクパケットで指定しますが、これはMariaDBでは utf8mb4 のデフォルトcollationではないので character_set_collations は無視され、そのまま collation_connection に設定されます。
# $ docker run --rm --name mariadb --network host --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 mariadb:latest
# mariadb -> mariadb では utf8mb4_uca1400_ai_ci
$ /opt/homebrew/Cellar/mariadb/11.7.2/bin/mariadb -uroot -h127.0.0.1 --default-character-set=utf8mb4 -e 'SELECT @@collation_connection'
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_uca1400_ai_ci |
+------------------------+
# mysql -> mariadb では utf8mb4_0900_ai_ci
$ /opt/homebrew/Cellar/mysql@8.4/8.4.5/bin/mysql -uroot -h127.0.0.1 --default-character-set=utf8mb4 -e 'SELECT @@collation_connection'
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci |
+------------------------+
# SET NAMES utf8mb4 すると utf8mb4_uca1400_ai_ci になる。
$ /opt/homebrew/opt/mysql-client@8.4/bin/mysql -uroot -h127.0.0.1 --default-character-set=utf8mb4
mysql> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_uca1400_ai_ci |
+------------------------+
1 row in set (0.01 sec)
# Homebrewのmysql-client -> mariadb では utf8mb4_uca1400_ai_ci (近い将来修正される可能性あり)
$ /opt/homebrew/opt/mysql-client@8.4/bin/mysql -uroot -h127.0.0.1 --default-character-set=utf8mb4 -e 'SELECT @@collation_connection'
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_uca1400_ai_ci |
+------------------------+
まとめ
collation_connection は実際に影響するケースは少ない変数ですが、だからこそ稀にハマった場合に原因がなかなかわからない事があります。
クライアント側では charset はエスケープ処理に関係するものの、collationは全く使いません。
そのため、私がメンテナンスしている go-sql-drivers/mysql, mysqlclient, PyMySQL では、サーバーのデフォルトのcollationを優先するように、接続後に SET NAMES utf8mb4 を実行するようになっています。
ほかの接続ライブラリでも、例えばPHPのmysqliにある MYSQLI_INIT_COMMAND のように新規接続時に実行するコマンドを指定できる場合は、 SET NAMES utf8mb4 を実行することをお勧めします。
