MySQLでテーブルを作るとき、こんな場面ないですか？
- カラムの値を制限したい
- 決まった値だけを入れたい
僕自身、今までこういうことってできないと思ってました。
でも実は、、、MySQLにも 列挙体（ENUM） があって、
それを使えばカラムに INSERT できる値を制限できるみたいです。
そこで記憶の整理も兼ね、
MySQLで列挙体（ENUM）を使ってカラム値を制限する方法を紹介
このページの目次
カラム値を制限するには ENUM を使えばＯＫ
MySQLには 列挙体（ ENUM ） が用意されてます。
これは何かというと、リファレンスによると次のようなデータ型
ENUM は、テーブル作成時にカラム仕様に明示的に列挙された、許可されている値のリストから選択された値を持つ文字列オブジェクトです。これには次の利点があります。
指定可能な値のセットがカラムで制限されている状況でのコンパクトなデータストレージ。入力値として指定した文字列は自動的に数値としてエンコードされます。ENUM 型のストレージ要件については、セクション11.7「データ型のストレージ要件」を参照してください。
読みやすいクエリーと出力。数値は、クエリー結果で対応する文字列に戻されます。
引用元 ： https://dev.mysql.com/doc/refman/5.6/ja/enum.html
許可された値だけを挿入できる、文字列オブジェクトという定義
例えば
ENUM の利用場面として考えられるのは、
次みたいに 性別 とか決まった値しか入らないレコードを作りたいときです。
|
1 2 3 4 5 |
CREATE TABLE users ( user_id INT KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, gender ENUM('male', 'female', 'other') ); |
上みたいに
ENUM を作成すると、
内部的には次みたいな構造のデータが作られる（あくまでイメージ）
|
1 2 3 4 5 |
$gender = [ 1 => 'male', 2 => 'female', 3 => 'other' ]; |
各 ENUM 内の値は、定義した順番にインデックス値（ 1 ～ ）が割り降られます。
この性質を除けば、普通のレコードとあんまり変わりません。
そして値を挿入するときも特別な操作とかは必要なし
次みたいに 'male' とか 'female' の値をそのまま入れるだけです。
|
1 2 3 4 5 6 |
INSERT INTO users (name, gender) VALUES( 'Tanaka Taro', 'male' ); INSERT INTO users (name, gender) VALUES( 'Yamada Hanako', 'female' ); |
ただし、ENUM が他と違うのは、
列挙体に含まれない値を挿入するとエラーが出ること
例えば次、わざと ENUM に含まれない値を挿入してるSQL例です。
|
1 2 3 |
INSERT INTO users (name, gender) VALUES( 'Hoge Hoge', 'xxx' ); |
こういう不正なSQLを書くと、次みたいにしっかり エラー を出してくれます。
|
1 |
ERROR 1265 (01000) at line 17: Data truncated for column 'gender' at row 1 |
これは便利ですね～
性別 とか 曜日 とか 服のサイズ とか・・・
そういう固定した値しか入らいないレコードを作りたいときに便利です。
MySQLで列挙体（ENUM）を使う時の注意点
こういう風に便利ですが、少し注意すべきこともあります。
その注意点とは次の２つ
１．数値を保存するのに ENUM を使わないこと
まず数値（特に整数）を保存するのに、ENUMは使わない方が安全
なぜなら数値に見える列挙値を作成した場合、
保存されてる値とインデックスが混同されやすくなるからです。
例えば次みたいなテーブルとデータを作成したとしましょう。
|
1 2 3 4 5 6 |
CREATE TABLE test ( numbers ENUM( '5', '4', '3', '2', '1' ) ); INSERT INTO test (numbers) VALUES ('2'), ('4'), ('1'); |
この時点でゴチャゴチャして分かりにくいけど、問題はそこじゃありません。
問題なのは WHERE 句とかで値でもインデックスでも指定ができてしまうことです。
例えば次の２つのクエリ、どういう結果になるか分かりますか？
|
1 2 3 4 5 |
SELECT numbers FROM test WHERE numbers = '2'; -- 2 が返される SELECT numbers FROM test WHERE numbers = 2; -- 4 が返される |
コメントにも書いたように、
初めのクエリを実行すると
2 が返され、次のクエリを実行すると
4 が返されます。
こういう風に ENUM に数値を使うと、超ややこしくなるので要注意
できるだけ数値と紛らわしい値は入れない方が無難です。
２．ソートには値ではなくインデックス値が使われること
そして ENUM で厄介のは並び替えしたいときです。
厄介なのはソートにインデックス値が使われてしまうこと
例えば次のテーブルを考えてみてください。
|
1 2 3 4 5 6 7 |
CREATE TABLE test ( alphabets ENUM( 'B', 'A', 'E', 'F', 'D', 'C' ) ); INSERT INTO test (alphabets) VALUES ('A'), ('B'), ('C'), ('D'), ('E'), ('F'); |
そして、このテーブルを alphabets の値で並び替えるのに、次のクエリを書いてみました。
|
1 2 |
SELECT * FROM test ORDER BY alphabets ASC; |
普通の値だったら昇順（ A => B => ... E => F ）で並び替えられる、、、と思っちゃうはず
でも ENUM はインデックス値で並び替えられるから、こうなるます。
|
1 |
B A E F D C |
値でソートができないのが ENUM で厄介な所です。
この問題の対処法として考えられるのは、次の２つ
- ソート不要なレコードのみ ENUM を使う
- 並び替え時に ORDER BY CONCAT を使う
最後の CONCAT というのは文字列を連結させるための関数
この関数を列挙値に使うと、インデックスではなく値を返してくれます。
なので、もしど～しても並び替えが必要なら、
次みたいに並び替える値を CONCAT で囲えばＯＫ
|
1 2 |
SELECT * FROM test ORDER BY CONCAT(numbers) ASC; |
▼こうすると期待通りに並んでくれる
|
1 |
A B C D E F |
あと CONCAT 同様、 CAST(col AS CHAR) を使ってもOKです。
ここまでのまとめ ー ENUMは便利だけど少し注意が必要
以上、MySQLでカラム値を制限するやり方についてでした。
少し扱いに注意が必要だけど、決まった値しか受け付けたくないときに使えそうです。
