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を合わせておくことが望ましいでしょう。