MySQLのCollationはどのように決まるか。そして、３つの落とし穴。

今回は、設定値（パラメータ）の話

Oracle脳には馴染みの薄い、MySQLの「Collation」にまつわる挙動の話atsuizo.hatenadiary.jp
の続きです。

前回は、Collation設定についてMySQLのデフォルトで使用される「_general_ci」だと「大文字小文字を区別せず」となり、区別させるには「_bin」を使いましょう、って話をしました。
今回は、そもそもそのCollationを設定する場所はどこにあるか、どのレベルで設定が可能で、デフォルトはどうなっているか、という話です。

パラメータと階層

MySQLでCollationを設定可能なレイヤは4階層（厳密には5階層）になっています。

サーバ（インスタンス）レベル

通常「システム変数」と呼ばれます。

Collationに関連するパラメータは
・collation_connection
・collation_server
の2つです。特に後者「collation_server」が、サーバーのデフォルト値という位置づけになり、CREATE DATABASEをデフォルト値依存で実施する場合に、値の決定に影響します。

以前、以下のエントリでまとめたように、オンプレのMySQLでは何箇所か記述指定することができます。
atsuizo.hatenadiary.jp

起動時に、これらのパラメータがグローバル変数的にメモリに読み込まれます。
厳密には5階層、といったのは、このメモリ上の設定を

set [global] variable パラメータ名=設定値;

で変更することができるからです。

なお、Oracleのように、変更コマンドから静的ファイルに反映させることはできません。

データベース（スキーマ）レベル

データベース（スキーマ）に対し、DEFAULT COLLATEとして設定することができます。

MySQLのCREATE DATABASEの構文では、今回メインで取り扱うCollationと、保存時の文字コード（Character Set)しか、オプション指定ができません。

13.1.10 CREATE DATABASE 構文

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...

create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.10 CREATE DATABASE 構文

オプションなので、無指定でデフォルト任せにデータベースを作成することができます。

デフォルト依存でCREATE DATABASEした場合、上位にあるサーバ（インスタンス）レベルのパラメータ（collation_server）の値を使ってくれればいいのですが、必ずしもそうならない、という落とし穴があります。

これについては後述。

テーブルレベル

DEFAULT COLLATEとして設定することができます。
テーブル内の各列単位で指定できるCOLLATIONのデフォルト値として機能します。

13.1.17 CREATE TABLE 構文

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]

（中略）
table_option:
ENGINE [=] engine_name
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| CONNECTION [=] 'connect_string'
| DATA DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| INDEX DIRECTORY [=] 'absolute path to directory'
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| STATS_AUTO_RECALC [=] {DEFAULT|0|1}
| STATS_PERSISTENT [=] {DEFAULT|0|1}
| STATS_SAMPLE_PAGES [=] value
| TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
| UNION [=] (tbl_name[,tbl_name]...)

（後略）
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.17 CREATE TABLE 構文

オプションなので、無指定でデフォルト任せにテーブルを作成することができます。

その場合、上位であるデータベースに指定されているCOLLATEの値で作成されます。

カラムレベル

Collationとは照合順序ですので、実際に照合されるのは各値、そしてその器である列です。
上位にあるデータベースもテーブルも、それ自体を照合することはないので、Collationの設定値はあくまで「DEFAULT」という位置づけです。

というわけで、列毎にCollationを設定することができます。
サーバレベル、データベースレベル、テーブルレベルの設定を一切無視して、列レベルにさえ明示的に値を設定しておけば、それが正となります。

13.1.17 CREATE TABLE 構文

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
（中略）

create_definition:
col_name column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
[index_option] ...
| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
[index_name] [index_type] (index_col_name,...)
[index_option] ...
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) reference_definition
| CHECK (expr)

column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]

data_type:
BIT[(length)]
| TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| DATE
| TIME[(fsp)]
| TIMESTAMP[(fsp)]
| DATETIME[(fsp)]
| YEAR
| CHAR[(length)]
[CHARACTER SET charset_name] [COLLATE collation_name]
| VARCHAR(length)
[CHARACTER SET charset_name] [COLLATE collation_name]
| BINARY[(length)]
| VARBINARY(length)
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| TEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| MEDIUMTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| LONGTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| ENUM(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| SET(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| spatial_type

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.17 CREATE TABLE 構文

この通り、文字列系のデータ型を指定する際に、COLLATEオプションを指定することができます。

オプションなので、無指定でデフォルト任せにカラムを作成することができます。
その場合、上位であるテーブルに指定されているCOLLATEの値で作成されます。

3つの落とし穴

公式ドキュメントも交えてCollation設定に関係する箇所を説明してきましたが、前フリも入れたデータベースのCOLLATEの値決定も含め、3つの落とし穴があります。

１つずつ解説していきます。

1:後から上位レイヤのCOLLATEを変更しても、その変更は作成済の下位オブジェクトには波及しない。

当然と言われてしまえばそれまでですが、デフォルトとして上位レイヤの設定値を参照するのは、そのオブジェクトの作成時のみです。

MySQLのデフォルトである「collation_server=latin1」のままで、データベースやテーブルをDEFAULT COLLATE無指定でガンガン作った後に、やっぱり日本語だしsjisかutf8だよね、と思ってサーバーパラメータを変更しても、作られているオブジェクトは放置です。
作ったオブジェクトはそのままなので、DROP＆CREATEか、ALTERでの変更が必要になります。

ALTER TABLEやる場合は、文字列系のデータ型を使っている列を拾い出して、全部COLLATE変更のALTER文を作らなくてはいけません。
かなりの作業負荷になるかと思います。

2:CREATE DATABASEのCOLLATEを無指定（デフォルト依存）で実行しても、にcollation_serverの設定値が入らないケースがある。

ここ、公式ドキュメントに書いてあるのですが、ワリと動きが気持ち悪いです。

「COLLATEを指定しないけど、CHARACTER SETを指定していた」というケースでは、collation_serverの値を無視して、「CHARACTER SETに対応したデフォルトのCOLLATION」が選択されます。

10.1.3.2 データベース文字セットおよび照合順序

MySQL では、データベース文字セットとデータベース照合順序が次のように選択されます。
・CHARACTER SET X と COLLATE Y の両方が指定されている場合、文字セット X と照合順序 Y が使用されます。
・CHARACTER SET X は指定されているが COLLATE は指定されていない場合、文字セット X とそのデフォルト照合順序が使用されます。各文字セットのデフォルトの照合順序を確認するには、SHOW COLLATION ステートメントを使用します。
・COLLATE Y は指定されているが CHARACTER SET は指定されていない場合、Y に関連付けられた文字セットと照合順序 Y が使用されます。
・これ以外の場合は、サーバー文字セットとサーバー照合順序が使用されます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.3.2 データベース文字セットおよび照合順序

「CHARACTER SETに対応したデフォルトのCOLLATION」の対応関係の一覧は、公式ドキュメントのこちらを見てください。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.2 MySQL での文字セットと照合順序
MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.14 MySQL でサポートされる文字セットと照合順序

インスタンスレベルで「collation_server=utf8_bin」を指定しておいたから、CREATE DATABASEでイチイチ指定しなくてもいいだろー、と考えつつ、CHARACTERAWS SETだけは指定して発行すると、出来上がったデータベースのCOLLATIONはutf8_general_ciでした、っていうことが起こりますよ、というお話です。

CREATE DATABASEを一例にとってますが、テーブルもカラムも、CHARACTER SETオプションが指定可能ですので、どのレベルでもこの落とし穴に落ちる可能性があります。ガチです。

3:Amazon RDSのデフォルトデータベースは、さらにルールを破る

AmazonのRDSでインスタンス起動時に、デフォルトで１つデータベースを作成することができます。

Management Consoleから作成する場合、4ステップ目でDB名を入力するアレです。

atsuizo.hatenadiary.jp
の、「インストール直後のデータベース一覧」の節で触れたように、オンプレでは3つ、RDSでは4つ、システム制御用のデータベースが作成されますが、RDS起動の手順の中で、実際にアプリ等のデータを保存する用途で使用するデータベーススキーマを作成させることができます。

これに際して、Amazonの公式ドキュメントでは、丁寧に説明を入れています。

先にパラメータグループ（インスタンスレベルで指定可能なパラメータ。オンプレのmy.cnf相当）で設定しておいて、インスタンス起動の際にはそのパラメータグループを指定しなさいよ、と。
あとからパラメータ変更しても、作ってしまったものには変更が及ばないよ、と。

DB インスタンスの作成前、および DB インスタンスでデータベースを作成する前に、パラメータグループに含まれるデータベースの文字セットまたは照合に関連するパラメータをすべて設定します。これにより、DB インスタンスのデフォルトデータベースと新しいデータベースで、指定した文字セットと照合値が使用されるようになります。DB インスタンスの文字セットまたは照合パラメータを変更した場合、パラメータの変更は既存のデータベースに適用されません。
ALTER DATABASE コマンドを使用して、既存のデータベースの文字セットまたは照合値を変更できます。次に例を示します。

　ALTER DATABASE database_name CHARACTER SET character_set_name COLLATE collation;

DB パラメータグループを使用する - Amazon Relational Database Service

だがしかし！

collation_serverに使用したいCOLLATION名を設定したパラメータグループを指定してインスタンスを起動しても、デフォルトデータベースのCOLLATEは全く別のもので設定されて出来上がります。
マジです。

デフォルトとは別に、新たにCREATE DATABASE文を発行した場合、前述のルールに則ってデータベースが作成されますので、オプションを一切使用しなければ、素直にサーバーレベルのパラメータを参照して作成してくれます。

この話を更に面倒にしているのは

「SHOW CREATE DATABASE」では確認がとれない（COLLATEに何が設定されているか表示されない）ことがある、ということです。
こういうときは、information_schemaを参照するか、MySQL Wrokbenchを参照すると確認できます。

select * from information_schema.schemata where schema_name = 'データベース（スキーマ）名';

テーブルやカラムについてもinformation_schemaのtablesとcolumnsを参照すると、「show create table テーブル名」では確認できない設定状態が確認できます。

RDSでMySQLを立ち上げてから、データベースのCOLLATE設定をちゃんと確認したことがない人は、なる早で確認しましょうね。

実践ハイパフォーマンスMySQL 第3版