文字列のルックアップに mysql の ENUM 型を使う
用語
「文字列のルックアップ」とはリレーションのない 数値 => 文字列
のマッピングを指します。例えば
$sex_codes = array(
1 => '男性',
2 => '女性',
);
などです。
DB のカラム値としては 1 とか 2 の値が入り、画面に表示するときに 「男性」とか「女性」と表示するイメージです。
input[type=radio]
や select
で選べる選択肢としても頻出します。
「mysql の ENUM 型」は公式マニュアルを参照するといいでしょう。
現状
DB では単に整数値として定義し、アプリ側(ここでは php。配列とか定数とか)で上記のようなマスタ配列を保持して担保しています。
しかし、それだと DB 側の制約がないため、 sex_code
のようなカラムに 1,2 以外の 4 などを入れても誰も何も注意してくれません。
また、DB を覗いてもそこには 1 とか 2 の値が入っているだけであり、「その値が何を意味するのか」はアプリのソースコードを確認しないと分かりません。
ルックアップ用の [id, name] だけを持ったテーブルを定義するのが正攻法(外部キーで制約できるため)だと思いますが、名前引きのためにそこまでするのもなんだかなぁ、と言った感じです。
理想
下記のようなテーブルを想定します。
CREATE TABLE person (
id SERIAL,
name VARCHAR(16) NOT NULL,
sex_code ENUM('男性', '女性') NOT NULL COLLATE 'utf8_bin',
PRIMARY KEY (id)
)
person.sex_code
は「男性」「女性」の2値のみを取ります。それ以外の値は受け付けません(後述)。
ただし、 ENUM 型のインデックスは1ベースで振られるので、数値として 1
を入れれば「男性」として入りますし、文字列として「男性」を入れてもそのまま「男性」として扱われます(これも後述)。
input[type=radio]
や select
で表示する選択肢は テーブル定義をパースすることで得られます。
function getEnumList(\PDO $pdo)
{
$describe = $pdo->query('DESCRIBE person')->fetchAll(\Pdo::FETCH_ASSOC);
if (!preg_match('#^enum\((.+)\)$#ui', $describe[2]['Type'], $matches)) {
throw new \Exception();
}
$vals = str_getcsv($matches[1], ',', "'", "\\");
$keys = range(1, count($vals));
return array_combine($keys, $vals);
}
※ マジックナンバーが多いですが、サンプルなので勘弁(というか適当です)。やりようはいくらでもあると思います
キーを数値として取得していますが、 array_combine($vals, $vals)
として、 文字列 => 文字列
の配列でもいいと思います。
というか、その方が ENUM 値を得るとき一手間減る1のでむしろその方が良いかもしれません。
注意点
マニュアルによると、ENUM 型は 照合順序の影響を受けるようです。
utf8_bin のようなバイナリ型でないと、大文字小文字を区別した列挙値を定義できないかもしれません。
また、 sql mode が strict でないと、定義された列挙値以外を INSERT しても怒られず、''(空文字)が挿入されるようです。
したがって strict でないとメリットがほとんど享受できません。
さらに INSERT 時に「数値っぽい文字列」を与えてもよしなに扱ってくれるようですが、列挙値に「数値っぽい文字列」が含まれていると多大な混乱を招きます。
http 経由で得られるパラメータはまず文字列であるため、int キャストがほぼ必須になると思います。
さらにさらに、並び順の制御ができません。
「選択肢を『女性』、『男性』の順で表示して欲しい」といった要望が来た場合、値の変更が必要になります。
…と、思ったんですが、カラム定義を変更するとよしなに変換が行われるようです。
例えば上記の例の場合、 person.sex_code
を ENUM('女性', '男性')
と変えたところでレコードの値としては何一つ変わりません。
ただし、これが規定された動作なのか不明のため、一応注意点として記しておきます。
※ mysql 内部では整数値で保持しているという認識なんだけど違うのかな?
こんなに注意点があるなら使えない気がする…。
-
ENUM 型はそのまま取得すると列挙値の文字列で返すので、数値化(
SELECT person.sex_code+0
)してやる必要がある。 ↩