MySQLのEnumに関する調査:

目的:

業務でEnumを使ったWHERE句で想定していない状態が発生して、大いにハマってしまったのでこれを機にきちんと調べてみた。

問題:

example_tableのようなENUM型のカラムがある、この中に'10'という数値のように見えるが文字列というデータが存在すると仮定する。

CREATE TABLE example_table (
   number ENUM('one', 'two', 'three', 'four', '10')
);
INSERT INTO example_table (number) VALUES ('one'), ('two'), ('three'), ('four'), ('10')

このテーブルに対して以下のようなSQLを実行。

# データは正しく登録されている
mysql> SELECT * FROM example_table;
+--------+
| number |
+--------+
| one    |
| two    |
| three  |
| four   |
| 10     |
+--------+

# 当たり前だが取得できる
mysql> SELECT * FROM example_table WHERE number='one';
+--------+
| number |
+--------+
| one    |
+--------+

# アイエエエ？！！ナンデ！ナンデEmpty？？！
mysql> SELECT * FROM example_table WHERE number=10;
Empty set (0.00 sec)

原因:

この現象には恐らくMySQLのStruct Modeも関係していると思われる、厳密に比較する設定になっていない場合はnumber=10でもデータが取得できたことを確認している。

今回問題になったのは手元の環境(Mac)では別の問題から厳密なチェックを行わないように変更を行っていて手元で問題なかったのでサーバにあげてテストしたところ特定のパラメータでのみ問題が発生したというもの。
プログラムそのものやSQL、データには一見問題がないように見えていたので調査に非常に時間がかかってしまった。

↑は嘘。試したデータが5だったのでインデックス番号と同じだったので成功してしまっていただけ。
10に変更したら駄目だった。

厳密な SQL モードが有効な場合は、無効な ENUM 値を挿入しようとするとエラーが発生します。

とあるので厳密なSQLモードの場合はエラーになるが正しい…と思われる。

閑話休題

数値が引用符で囲まれている場合、列挙値のリストに一致する文字列がなければ、そのままインデックスとして解釈されます。これらの理由により、ENUM カラムを数字のように見える列挙値で定義することは、混乱を招きやすくなるのでお勧めできません。
(中略)
引用符を省略した場合は、その数字はインデックスと見なされます。

……ときちんと公式ドキュメントに記載されていた。
つまりEnumに数値と認識できてしまうような値を入れてしまう事自体がアンチパターンだということですね。
静的型付け言語なら問題になりにくいかもしれないですが動的型付け言語だとコードは正しいが動作が意図しない状態になるというわかりにくいバグを埋め込んでしまうことになるのでやめましょう。

ちなみに↓を実行すると検索結果が返ってくるのでやはり数値をEnumに突っ込むのはEvil。

# ふぇぇ……
mysql> SELECT * FROM example_table WHERE number=5;
+--------+
| number |
+--------+
| 10     |
+--------+

ENUM カラムのすべての指定可能な値を特定するには、SHOW COLUMNS FROM tbl_name LIKE 'enum_col' を使用して、出力の Type カラム内の ENUM 定義を構文解析します。

SHOW COLUMNS FROM example_table LIKE 'number';
+--------+--------------------------------------+------+-----+---------+-------+
| Field  | Type                                 | Null | Key | Default | Extra |
+--------+--------------------------------------+------+-----+---------+-------+
| number | enum('one','two','three','four','10') | YES  |     | NULL    |       |
+--------+--------------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

登録されているのが数値なのか文字列なのか判別したいときは↑を実行すれば確認可能。

知見

今回のことで生兵法は怪我の元になってしまったので、いい機会なのできちんと仕様を調べようと思い、公式サイトであるMySQL 5.6 リファレンスマニュアル / ... / 11.4.4 ENUM 型のEnum項目を読んでいて得た知見をまとめておく。

Enum型(列挙型)には自動でインデックスが設定される

インデックス番号が自動的に割り振られる。
今回の例で言うならば↓のような状態と等価ということ。

let number = [];
number[1] = 'one';
number[2] = 'two';
number[3] = 'three';
number[4] = 'four';
number[5] = '10';

注意すべき点としてインデックス番号は1始まりである点。
0は空の文字列エラーの予約値のような扱いになる、戻り値は''が返ると公式ドキュメントには書かれている、PHPなどから呼び出した場合どうなのか？は未検証だが恐らく空文字が返る。
NULLのインデックスはNULL、NULLになるのは当たり前だけどエラーにはならない？(未確認)

Enum型の最大要素数:

ENUM カラムには、最大 65,535 個の個別の要素を含めることができます。(実用的な限度は 3000 個までです。)

理論上と実用は違うが実用上の上限に関してコメントされているのは珍しいので恐らくこれ以上になるととたんにパフォーマンスが劣化するのだと思われる。

まとめ:

なんとなく途中でジョインしたプロジェクトでENUM型を使っていたので雰囲気で使っていたが使う前に軽くドキュメントに目を通すくらいのことはしておくべきだった。
まあ今回の問題となったデータを登録したのはぼくではないし、どういうデータが来るか事前に想定できない状態というあまりいい状態で開発着手できなかったので次回に活かしていきたい。

インデックス番号の開始位置とか最大要素数とか頭の片隅においておくだけでも価値のある情報なのでよい機会だった。
痛い目にはあったけど。
Postgresとかだとこの辺の扱いが変わったりするのかがちょっと気になる。