MySQLでカラム値は制限できない?実はできた

MySQLでテーブルを作るとき、こんな場面ないですか?

  • カラムの値を制限したい
  • 決まった値だけを入れたい

僕自身、今までこういうことってできないと思ってました。

でも実は、、、MySQLにも 列挙体(ENUM) があって、
それを使えばカラムに INSERT できる値を制限できるみたいです。

そこで記憶の整理も兼ね、
MySQLで列挙体(ENUM)を使ってカラム値を制限する方法を紹介

カラム値を制限するには ENUM を使えばOK

MySQLには 列挙体( ENUM  ) が用意されてます。

これは何かというと、リファレンスによると次のようなデータ型

ENUM は、テーブル作成時にカラム仕様に明示的に列挙された、許可されている値のリストから選択された値を持つ文字列オブジェクトです。これには次の利点があります。

指定可能な値のセットがカラムで制限されている状況でのコンパクトなデータストレージ。入力値として指定した文字列は自動的に数値としてエンコードされます。ENUM 型のストレージ要件については、セクション11.7「データ型のストレージ要件」を参照してください。

読みやすいクエリーと出力。数値は、クエリー結果で対応する文字列に戻されます。

引用元 : https://dev.mysql.com/doc/refman/5.6/ja/enum.html

許可された値だけを挿入できる、文字列オブジェクトという定義

 

例えば ENUM  の利用場面として考えられるのは、
次みたいに 性別 とか決まった値しか入らないレコードを作りたいときです。

上みたいに ENUM  を作成すると、
内部的には次みたいな構造のデータが作られる(あくまでイメージ)

各 ENUM 内の値は、定義した順番にインデックス値( 1 ~ )が割り降られます

この性質を除けば、普通のレコードとあんまり変わりません。

 

そして値を挿入するときも特別な操作とかは必要なし

次みたいに 'male'  とか 'female'  の値をそのまま入れるだけです。

 

ただし、ENUM が他と違うのは、
列挙体に含まれない値を挿入するとエラーが出ること

例えば次、わざと ENUM  に含まれない値を挿入してるSQL例です。

こういう不正なSQLを書くと、次みたいにしっかり エラー を出してくれます。

これは便利ですね~

性別 とか 曜日 とか 服のサイズ とか・・・
そういう固定した値しか入らいないレコードを作りたいときに便利です。

MySQLで列挙体(ENUM)を使う時の注意点

こういう風に便利ですが、少し注意すべきこともあります。

その注意点とは次の2つ

1.数値を保存するのに ENUM を使わないこと

まず数値(特に整数)を保存するのに、ENUMは使わない方が安全

なぜなら数値に見える列挙値を作成した場合、
保存されてる値とインデックスが混同されやすくなるからです。

 

例えば次みたいなテーブルとデータを作成したとしましょう。

この時点でゴチャゴチャして分かりにくいけど、問題はそこじゃありません。

問題なのは WHERE  句とかで値でもインデックスでも指定ができてしまうことです。

 

例えば次の2つのクエリ、どういう結果になるか分かりますか?

コメントにも書いたように、
初めのクエリを実行すると 2  が返され、次のクエリを実行すると 4  が返されます。

こういう風に ENUM  に数値を使うと、超ややこしくなるので要注意

できるだけ数値と紛らわしい値は入れない方が無難です。

2.ソートには値ではなくインデックス値が使われること

そして ENUM で厄介のは並び替えしたいときです。

厄介なのはソートにインデックス値が使われてしまうこと

 

例えば次のテーブルを考えてみてください。

そして、このテーブルを alphabets  の値で並び替えるのに、次のクエリを書いてみました。

普通の値だったら昇順( A => B => ... E => F )で並び替えられる、、、と思っちゃうはず

でも ENUM  はインデックス値で並び替えられるから、こうなるます。

値でソートができないのが ENUM  で厄介な所です。

 

この問題の対処法として考えられるのは、次の2つ

  • ソート不要なレコードのみ ENUM を使う
  • 並び替え時に ORDER BY CONCAT を使う

最後の CONCAT  というのは文字列を連結させるための関数

この関数を列挙値に使うと、インデックスではなく値を返してくれます。

 

なので、もしど~しても並び替えが必要なら、
次みたいに並び替える値を CONCAT で囲えばOK

▼こうすると期待通りに並んでくれる

あと CONCAT  同様、 CAST(col AS CHAR)  を使ってもOKです。

ここまでのまとめ  ー  ENUMは便利だけど少し注意が必要

以上、MySQLでカラム値を制限するやり方についてでした。

少し扱いに注意が必要だけど、決まった値しか受け付けたくないときに使えそうです。

Commentsこの記事についたコメント

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA


このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください