どうも、シローです。
今回は、特定の文字列に限定した列を定義する方法について、アンチパターン「サーティワンフレーバー」も一緒に紹介します。
特定の値を持ちうる列とは
例えばユーザ(Users)の権限(管理者かメンバーか)を表したい場合、Usersテーブルにメンバーの権限を表すuser_roleという列を定義するとします。
このuser_roleには管理者の場合はadmin
、メンバーの場合はmember
という値を持ちそれ以外の値は格納しない(できない)ようにするにはどうすれば良いのか
アンチパターン「サーティワンフレーバー」について
先にアンチパターンについて紹介します。
サーティワンフレーバーと呼ばれる設計方法では特定の値を入れるために
- 列に
CHECK
制約を入れる(MySQL8.0以降) - 列を
ENUM
型にする
という手法を取りうることです。
僕の環境はMySQL5.7なのでENUM
型のカラムuser_role
を作成してみます。
1 2 3 4 5 |
CREATE TABLE Users ( user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_role ENUM ('admin', 'member'), PRIMARY KEY (user_id) ); |
admin
とmember
は追加できるがbanana
は定義されていないので追加できません。
1 2 3 4 5 6 7 8 |
mysql> INSERT INTO Users(user_role) VALUES ('admin'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Users(user_role) VALUES ('member'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Users(user_role) VALUES ('banana'); ERROR 1265 (01000): Data truncated for column 'user_role' at row 1 |
僕もよくENUM
型のカラムを作成することはありましたが、SQLアンチパターンで書かれた通りの副作用に直面した記憶があります。
ENUMで持ちうる値がDB定義を見ないとわからない
どの値を持ちうるかはDB定義を見なければならず、次のようにDESC
を用いて確認できます。
1 2 3 4 5 6 7 |
mysql> DESC Users; +-----------+------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------------+------+-----+---------+----------------+ | user_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | user_role | enum('admin','member') | YES | | NULL | | +-----------+------------------------+------+-----+---------+----------------+ |
後からENUMの持ちうる値を更新するのが大変
ENUM
型の欠点は後から値を修正したり、削除するのが手間かかることです。
ENUMに新しい値を追加
新しい値を追加するのは比較的簡単です、例えばゲストとしてguest
を追加してみます。
1 2 3 |
mysql> ALTER TABLE Users MODIFY COLUMN user_role ENUM ( 'admin', 'member', 'guest' ); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 |
値を追加。
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> INSERT INTO Users(user_role) VALUES ('guest'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM Users; +---------+-----------+ | user_id | user_role | +---------+-----------+ | 1 | admin | | 2 | member | | 3 | guest | +---------+-----------+ 3 rows in set (0.00 sec) |
ENUMの既存の値を更新
やっぱり、guest
ではなくてcustomer
にしたい場合。
1 2 |
mysql> ALTER TABLE Users MODIFY COLUMN user_role ENUM ( 'admin', 'member', 'customer' ); ERROR 1265 (01000): Data truncated for column 'user_role' at row 3 |
エラー起きました。原因は既にguest
の値を持つレコードがある状態でENUM値からそれを削除することができないからです。
じゃあどうやって、やるのかというと
- 変更先の
customer
をENUMに追加 - 既存の
guest
をcustomer
に更新 - ENUMから
guest
を削除
とします。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
// customerをENUMに追加 mysql> ALTER TABLE Users MODIFY COLUMN user_role ENUM ( 'admin', 'member', 'guest', 'customer' ); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 // guestをcustomerに更新 mysql> UPDATE Users SET user_role = 'customer' WHERE user_role = 'guest'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 // guestをENUMから削除 mysql> ALTER TABLE Users MODIFY COLUMN user_role ENUM ( 'admin', 'member', 'customer' ); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM Users; +---------+-----------+ | user_id | user_role | +---------+-----------+ | 1 | admin | | 2 | member | | 3 | customer | +---------+-----------+ 3 rows in set (0.00 sec) |
つまり、合計3つの処理が必要です。
しかもテーブル定義のSQLはトランザクションで管理されないので、この間に他の処理が入って予期せぬ不具合が生じることも否定できないです。
解決策:新たにマスターテーブルを作成し、そこへの参照キーとして列を定義する
解決策としては、ENUMで取りうる値を別のテーブルにレコードとして管理しておき、そのテーブルへの参照キーとして列を定義することです。
このシステムで管理するための特定の値のレコードを格納しているテーブルはマスターテーブル
と呼ばれます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
// マスターテーブル User_Role_Types を作成 mysql> CREATE TABLE User_Role_Types ( -> type VARCHAR(30), -> PRIMARY KEY (type) -> ); Query OK, 0 rows affected (0.01 sec) // マスターテーブルにレコードを追加 mysql> INSERT INTO User_Role_Types(type) VALUES ('admin'),('member'),('guest'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 // Usersテーブルを作成し、user_roleをUser_Role_Types.typeへの参照キーにする mysql> CREATE TABLE Users ( -> user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, -> user_role VARCHAR(30), -> PRIMARY KEY (user_id), -> FOREIGN KEY (user_role) REFERENCES User_Role_Types(type) ON UPDATE CASCADE ON DELETE CASCADE -> ); Query OK, 0 rows affected (0.01 sec) |
マスターテーブルで取りうる値を定義することで以下のようなメリットがあります。
取りうる値をSELECTで全て取得できる
マスターテーブルに入っているレコードが取りうる値の全てなので、
SELECT
文で取得できます。
1 2 3 4 5 6 7 8 9 |
mysql> SELECT * FROM User_Role_Types; +--------+ | type | +--------+ | admin | | guest | | member | +--------+ 3 rows in set (0.00 sec) |
取りうる値の追加、更新が容易
例えば、新しくowner
を追加したい場合はUser_Role_Types
にINSERT
文で追加すれば良いです。
1 2 3 4 5 |
mysql> INSERT INTO User_Role_Types(type) VALUES ('owner'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Users(user_role) VALUES ('owner'); Query OK, 1 row affected (0.01 sec) |
また、guest
をcustomer
に変更したい場合はUser_Role_Type
にUPDATE
文を実行するだけです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
// guestの値を持つレコードを追加 mysql> INSERT INTO Users(user_role) VALUES ('guest'); Query OK, 1 row affected (0.00 sec) // guestをcustomerに更新 mysql> UPDATE User_Role_Types SET type = 'customer' WHERE type = 'guest'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 // 既存のguestがcustomerに更新されている CASCADEによって mysql> SELECT * FROM Users; +---------+-----------+ | user_id | user_role | +---------+-----------+ | 2 | customer | | 1 | owner | +---------+-----------+ 2 rows in set (0.00 sec) |
参照整合制約を定義する際にON UPDATE CASCADE
としておくことでマスターテーブルの値を更新すると、更新前のレコードも新しい値に書き換わります。
また、クエリも一回で済みますし、元に戻すことも容易です。
まとめ
- 限定的な値を取りうる列で
ENUM
やCHECK
制約は後から値の追加や更新が簡単にはいかないことがある - 取りうる値をマスターテーブルで管理する手法の方が後から値に追加や更新が簡単
本書はDB設計やSQL記述の際に避けるべき事柄を1章で1つ、25個紹介する書籍です。
リレーショナルデータベースを中心に据えたシステム開発には、様々な場面で陥りやすい失敗(アンチパターン)があります。
本書はデータベース論理設計、データベース物理設計、クエリの記述、アプリケーション開発という4つのカテゴリに分かれて、それぞれの分野におけるアンチパターンを紹介し、失敗を避けるためのより良い方法を紹介します。
複数の値を持つ属性や再帰的なツリー構造の格納から、小数値の丸めやNULLの扱いに起因する問題、全文検索やSQLインジェクション、MVCアーキテクチャなど、実践的かつ幅広いトピックを網羅します。
データベースに関わるすべてのエンジニア必携の一冊です。