第157回　MySQLのデフォルトcollationの注意点

MySQLではcharacter set（以後、charset）やcollationをグローバル、データベース、テーブルやカラムレベルで設定することができます。今回はMySQLのデフォルトcollationの注意点を紹介したいと思います。使用するMySQLのバージョンは8.0.26です。

charsetやcollationとはなにかについては説明はしません。よって、charsetやcollationについてご存知ない方は、先にマニュアル「第10章文字セット、照合順序、Unicode」をご確認ください。

charsetやcollationの各レベルの設定方法

グローバル

以下のシステム変数を設定します。

character_set_server… サーバーのデフォルトのcharset
collation_server… サーバーのデフォルトのcollation

データベース

CREATE DATABASE文に指定することができます。

CREATE DATABASE `test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

上記例は、testデータベースのcharsetにutf8mb4、collationにutf8mb4_binを設定しています。

テーブルとカラム

CREATE TABLE文に指定します。

CREATE TABLE tbl1 (
    id bigint NOT NULL,
    col1 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

上記例は、tbl1テーブルのcharsetにutf8、collationにutf8_general_ciを設定しています。またtbl1.col1カラムのcharsetにutf8mb4、collationにutf8mb4_0900_as_ciを設定しています。

MySQLのデフォルトcollationの注意点

では、ここから本題のMySQLのデフォルトcollationの注意点を紹介します。

CREATE DATABASEやCREATE TABLE文ではDEFAULT CHARSET=xx COLLATE=xxの部分を省略することもできます。CREATE TABLE文を例に、どのcollationが設定されるのか確認してみましょう。

前提として、設定は以下の通りです。

グローバルレベルのcharset… utf8mb4
グローバルレベルのcollation… utf8mb4_bin

mysql> show global variables like 'collation_server';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| collation_server | utf8mb4_bin |
+------------------+-------------+

データベースレベルのcharset… utf8mb4
データベースレベルのcollation… utf8mb4_unicode_ci

mysql> CREATE DATABASE `db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
1 row in set (0.00 sec)

dbデータベース内に以下の3つタイプのCREATE TABLEを実行すると、それぞれcollationには何が設定されるでしょうか。

CREATE TABLE t1 (col1 varchar(10));
CREATE TABLE t2 (col1 varchar(10)) DEFAULT CHARSET=utf8mb4;
CREATE TABLE t3 (col1 varchar(10)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

結果は以下の通りです。

Table	collation
t1	utf8mb4_unicode_ci
t2	utf8mb4_0900_ai_ci
t3	utf8mb4_general_ci

t1については、データベースレベルのcolationが引き継がれています。t3については、テーブルレベルで指定しているため希望通りの結果です。ところが、t2はutf8mb4_0900_ai_ciという、どこにも設定していないcollationが登録されました。これはMySQL 8.0のutf8mb4のデフォルトcollationになります。

つまり、CREATE TABLE文にDEFAULT CHARSET=utf8mb4のみ記述すると、collation_serverシステム変数に設定されているサーバーのデフォルトcollationでもデータベースレベルのcollationでもない、MySQLのデフォルトcollationが設定されてしまうのです。CREATE TABLE文を実行するときは予期せぬトラブルを避けるため、DEFAULT CHARSET=xx COLLATE=xxを省略せずに記述するのが良いでしょう。

また、MySQLのデフォルトcollationを確認するにはSHOW COLLATION文を実行します。DefaultカラムがYesになっているものが、MySQLのデフォルトcollationになります。

ちなみに、CREATE DATABASE文についても同様の動きをします。DEFAULT CHARACTER SET xx COLLATE xxを省略すると、グローバルレベルの設定が引き継がれます。

CREATE DATABASE db1 ;
CREATE DATABASE db2 DEFAULT CHARACTER SET utf8mb4;
CREATE DATABASE db3 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

結果は以下の通りです。

Database	collation
db1	utf8mb4_bin
db2	utf8mb4_0900_ai_ci
db3	utf8mb4_general_ci

MySQL 8.0アップグレード時の注意点

前述のデフォルトcollationはMySQL 8.0へのアップグレード時に注意する必要があります。MySQLのutf8mb4のデフォルトcollationは MySQL 5.7とそれ以前はutf8mb4_general_ciであり、MySQL 8.0からutf8mb4_0900_ai_ciに変更されました。

MySQL 5.7からMySQL 8.0へmysqldumpを使用して移行するとします。その際にutf8mb4_general_ciで運用されているMySQL 5.7からは以下のような出力をします。

# mysqldump --no-data test57db test_account -p
-- MySQL dump 10.13  Distrib 5.7.22, for linux-glibc2.12 (x86_64)
<snip>
CREATE TABLE `test_account` (
  `user_id` bigint(20) NOT NULL,
  `created_at` int(10) unsigned NOT NULL,
  `updated_at` int(10) unsigned NOT NULL,
  PRIMARY KEY (`user_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
<snip>
-- Dump completed on 2021-10-07 22:18:18

DEFAULT CHARSET=utf8mb4のみの記述で出力されてしまいます。このdumpファイルをそのままMySQL8.0にimportすると、utf8mb4_general_ciではなくutf8mb4_0900_ai_ciが設定されてしまいます。

この問題を解決するためには、default_collation_for_utf8mb4システム変数を使用します。このシステム変数はutf8mb4におけるMySQLのデフォルトcollationを指定します。デフォルトはutf8mb4_0900_ai_ciです。これをutf8mb4_general_ciに変更します。

mysql> SET GLOBAL default_collation_for_utf8mb4=utf8mb4_general_ci;

この変更をすると、DEFAULT CHARSET=utf8mb4のみの記述のCREATE TABLE文であっても、collationがutf8mb4_general_ciでテーブルが作成されます。

default_collation_for_utf8mb4システム変数の注意点として、my.cnfに記述する永続化設定はできません。永続化設定するにはSET PERSISTを使って設定します。詳しくは第94回　SET PERSISTを使ってシステム変数を永続化させるをご参照ください。また、下位互換のためのシステム変数なので、取りうる値はutf8mb4_0900_ai_ci、またはutf8mb4_general_ciのいずれかです。

テーブル間でcollationが異なるときの問題

テーブル間でcollationが異なるときに起こる問題について紹介したいと思います。その場合、JOINのときに結合キーでインデックスが効かないためクエリが遅くなる可能性があります。

たとえば、以下のようなテーブルとデータを用意します。

CREATE TABLE `t1` (
  `id` int NOT NULL,
  `col1` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


CREATE TABLE `t2` (
  `id` int NOT NULL,
  `col1` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY(col1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'b');
INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'b');

t1.col1がutf8mb4_binでt2.col1がutf8mb4_general_ciとします。このときに駆動表にt1をt2のcol1と結合するクエリを実行してみます。

mysql> explain SELECT * FROM t1 JOIN t2 ON t1.col1=t2.col1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                                           |
|  1 | SIMPLE      | t2    | NULL       | ALL  | col1          | NULL | NULL    | NULL |    3 |    33.33 | Range checked for each record (index map: 0x2) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+

本来であれば、t2.col1のインデックスを効かせて解決できるはずですが、type:ALLとなりインデックスが効かず、クエリが遅くなる問題があります。

Tipsとして、強制的にcollationを合わせることでインデックスを効かせて解決することもできます。また、JOIN_ORDERヒント句も使用して、確実にt1 → t2の順で結合するように指定しています。

mysql> explain SELECT /*+ JOIN_ORDER(t1,t2) */ * FROM t1 
> JOIN t2 ON t1.col1 COLLATE utf8mb4_general_ci=t2.col1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                     |
|  1 | SIMPLE      | t2    | NULL       | ref  | col1          | col1 | 43      | func |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+

しかし、このようなことが起こらないように、特別な要件がない場合は、すべてのテーブルでcollationを合わせておくことが望ましいでしょう。