Posted at

10章 31 Flavors(31のフレーバー)

More than 3 years have passed since last update.

少し変わった名前の章だが、これは本章の問題を、31アイスクリームの歴史をメタファーに、コラムで取り扱ったためである。バスキン・ロビンスに学べ、という事らしい。


連絡先情報テーブルに敬称(salutation)を指定可能にする

予め定義した値だけを列に入力させたいケース、例えば連絡先情報テーブルに、決めた敬称だけを入力可能にする場合を想定してみる。


31-Flavors/intro/create-table.sql

CREATE TABLE PersonalContacts (

name VARCHAR(20) NOT NULL,
salutation VARCHAR(4) NOT NULL
CHECK (salutation IN ('さん', '君', '様', '殿', '氏'))
);

値リストの指定は、「列のデータ型」としてや、「制約の宣言時」に行える。

これによって、無効な文字列の入力も防止出来る。


31-Flavors/intro/input-check-ok

mysql> INSERT INTO PersonalContacts (name, salutation) VALUES ("ゆきお", "様");

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM PersonalContacts;
+-----------+------------+
| name | salutation |
+-----------+------------+
| ゆきお | 様 |
+-----------+------------+
1 row in set (0.00 sec)


問題無く INSERT できる。

次はCHECK制約にないテキストを入力してみる。


31-Flavors/intro/input-check-ng-1

mysql> INSERT INTO PersonalContacts (name, salutation)

-> VALUES ("ゆきお", "ちゃん"); -- 「ちゃん」はエラーになるはず
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM PersonalContacts;
+-----------+------------+
| name | salutation |
+-----------+------------+
| ゆきお | 様 |
| ゆきお | ちゃん |
+-----------+------------+
2 rows in set (0.00 sec)


成功しちゃった! ヽ(`Д´)ノ

MySQLでは Check制約が無視されるため、意味がないのであった。(2014年10月18日現在)


The CHECK clause is parsed but ignored by all storage engines.


ここでは無理矢理 trigger で制約を作ってみることにする。


31-Flavors/intro/input-check-trigger

mysql> delimiter $$

mysql> CREATE TRIGGER Insert_Personnel BEFORE INSERT ON PersonalContacts
-> FOR EACH ROW
-> BEGIN
-> IF (NEW.salutation NOT IN ('さん', '君', '様', '殿', '氏')) THEN
-> SET NEW.salutation = NULL;
-> ELSE
-> BEGIN END;
-> END IF;
-> END;$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;


予めテーブル作成時に NOT NULL 指定をしておいて、規定の敬称以外が来たら NULL に置き換えてエラーにするトリガーである。


31-Flavors/intro/input-check-ng-3

mysql> INSERT INTO PersonalContacts (name, salutation) VALUES ("ゆきお", "氏");

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PersonalContacts (name, salutation) VALUES ("ゆきお", "ちゃま");
ERROR 1048 (23000): Column 'salutation' cannot be null
mysql> SELECT * FROM PersonalContacts;
+-----------+------------+
| name | salutation |
+-----------+------------+
| ゆきお | 様 |
| ゆきお | ちゃん |
| ゆきお | 氏 |
+-----------+------------+
3 rows in set (0.00 sec)


無事、ゆきおちゃまをエラーに出来たようである。

しかし、これだけだと INSERT 時にしか対応出来ていないので、別途 UPDATE 用の trigger も用意しなければいけないし、おまけに、敬称の仕様が変わればロジックの修正が必要になり……頭の痛い問題である。


10.1 目的:列を特定の値に限定する

列に格納できる値を限定出来ると、無効な値が含まれていない事を保証でき、列をシンプルに使える。

例えば、この本のサンプルとして登場する Bugsテーブル内の status列で、バグの状態を


  • NEW

  • IN PROGRESS

  • FIXED

などで指定しているとすると、status列には必ず、このどれかの値が格納されているという事。

データベースが無効なデータ入力を受け付けないのが理想なのである。


10.2 アンチパターン:限定する値を列定義で指定する

そのためにすぐに思いつく解決策は、有効なデータ値を列の定義時に指定するというもの。

前段では CHECK制約や trigger を試したので、今度は ENUM で指定してみる。


31-Flavors/obj/create-table-enum.sql

CREATE TABLE Bugs (

-- 他の列...
status ENUM('NEW', 'IN PROGRESS', 'FIXED')
);

だが、ENUMは内部的には文字列として格納されておらず、ソート結果は列での値の定義順になってしまう。


31-Flavors/obj/create-table-enum-check

mysql> INSERT INTO Bugs (status) VALUES ('NEW'); -- OK

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Bugs (status) VALUES ('IN PROGRESS'); -- OK
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Bugs (status) VALUES ('FIXED'); -- OK
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Bugs (status) VALUES ('BANANA'); -- エラー!
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT status FROM Bugs ORDER BY status;
+-------------+
| status |
+-------------+
| |
| NEW |
| IN PROGRESS |
| FIXED |
+-------------+
4 rows in set (0.00 sec)


ENUM の定義に無い値は NULL になったが、SELECT での結果のソート順序は分かりにくい。

他の解決策には、ドメインやユーザー定義型 (UDT)を利用したりする方法も考えられるが、これらには、そもそもの欠点が存在するのであった。


10.2.1 中身は何だろう

Bugs テーブル内の ENUM で定義された status 値を、使用可能な値として抽出しようとした場合...


31-Flavors/anti/information-schema-ex

mysql> SELECT column_type FROM information_schema.columns WHERE table_name = 'Bugs' AND column_name = 'status';

+-----------------------------------+
| column_type |
+-----------------------------------+
| enum('NEW','IN PROGRESS','FIXED') |
+-----------------------------------+
1 row in set (0.00 sec)

この文字列をパースして、各値を抽出して...ってオイ。抽出するクエリも結果も複雑。

ちなみに...

最初の例で作った trigger に至っては、SHOW TRIGGERS でしか抽出出来ず、更に悲惨。


31-Flavors/anti/information-schema-ex2

mysql> SHOW TRIGGERS LIKE 'PersonalContacts'\G

*************************** 1. row ***************************
Trigger: Insert_Personnel
Event: INSERT
Table: PersonalContacts
Statement: BEGIN
IF (NEW.salutation NOT IN ('さん', '君', '様', '殿', '氏')) THEN
SET NEW.salutation = NULL;
ELSE
BEGIN END;
END IF;
END
Timing: BEFORE
Created: NULL
sql_mode:
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)

結論: 入力可能な値の抽出が辛い


10.2.2 新しいフレーバーの追加

Bugs テーブル内の ENUM で定義された status 値に、DUPLICATE を追加したい場合...


31-Flavors/anti/add-enum-value

mysql> ALTER TABLE Bugs MODIFY COLUMN status ENUM('NEW', 'IN PROGRESS', 'FIXED', 'DUPLICATE');

Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESCRIBE Bugs;
+--------+-----------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------------------------------+------+-----+---------+-------+
| status | enum('NEW','IN PROGRESS','FIXED','DUPLICATE') | YES | | NULL | |
+--------+-----------------------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)


列定義を変えようと思ったら ALTER TABLE するしかない。

参考: ALTER TABLE実行する時は注意しましょう

結論: DBのメンテナンス時間が必要になるし、変更のコストが高い


10.2.3 昔ながらの味は色褪せない

Bugs テーブル内の status 値から、FIXED を CODE COMPLETE と VERIFIED に分けたい場合...


31-Flavors/anti/remove-enum-value

mysql> ALTER TABLE Bugs MODIFY COLUMN status ENUM('NEW', 'IN PROGRESS', 'CODE COMPLETE', 'VERIFIED');

Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 1

mysql> SELECT status FROM Bugs ORDER BY status;
+-------------+
| status |
+-------------+
| |
| |
| NEW |
| IN PROGRESS |
+-------------+
4 rows in set (0.00 sec)


元々 FIXED だったステータス値は、そのままやってしまうと NULL になってしまう。

そして、入力は制限したいが、過去の status 値はそのまま残しておきたいケースでは、前述の ENUM 定義の取得だけでは、どれが使ってよい有効な値なのか分からなくなってしまう。

結論: 値の廃止に柔軟性が無い


10.2.4 移植が困難

+----------------------+--------+--------+--------+---------------------+

| DBMS | ENUM | CHECK | DOMAIN | UDT サポート |
+----------------------+--------+--------+--------+---------------------+
| ANSI SQL標準 | なし | SQL89 | SQL92 | SQL99 |
+----------------------+--------+--------+--------+---------------------+
| MySQL | はい | いいえ | いいえ | いいえ |
+----------------------+--------+--------+--------+---------------------+
| IBM DB2® | いいえ | はい | いいえ | はい (単純および複雑)|
+----------------------+--------+--------+--------+---------------------+
| Microsoft SQL Server | いいえ | はい | いいえ | はい (単純および複雑)|
+----------------------+--------+--------+--------+---------------------+
| Oracle | いいえ | はい | いいえ | はい (複雑) |
+----------------------+--------+--------+--------+---------------------+
| PostgreSQL | いいえ | はい | はい | はい |
+----------------------+--------+--------+---------+--------------------+

参照元: データ型サポート

結論: これらの方法が、他の RDBMS でも使えるとは限らない


10.3 アンチパターンの見つけ方

ENUM や CHECK制約を使っていて問題に遭遇するのは、値が変わる可能性がある場合である。

アンチパターンの利用を示唆しているのは...


  • メニュー追加のような変更のために、DBをオフラインにしようとしている

  • status列は、この値のうち1つを持つ事が出来る。リストの修正が必要になることは、まずないはずだ

  • アプリケーションコード側の値リストが、ビジネスルールと同期していない


10.4 アンチパターンを用いても良い場合

値セットが変わらないと断言できるような場合。

特に、ENUM に最適なのは、相互排他的な2つの値を指定するような場合。


  • 左/右

  • 有効/無効

  • オン/オフ

  • 内部/外部


10.5 解決策:限定する値をデータで指定する

status が持ちうる値を持った参照テーブルを作成し、Bugs.status に外部キー制約を宣言して参照させる。


31-Flavors/soln/create-lookup-table

mysql> DROP TABLE Bugs;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE BugStatus (
-> status VARCHAR(20) PRIMARY KEY
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO BugStatus (status) VALUES ('NEW'), ('IN PROGRESS'), ('FIXED');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> CREATE TABLE Bugs (
-> -- 他の列...
-> status VARCHAR(20),
-> FOREIGN KEY (status) REFERENCES BugStatus(status)
-> ON UPDATE CASCADE
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT status FROM BugStatus;
+-------------+
| status |
+-------------+
| FIXED |
| IN PROGRESS |
| NEW |
+-------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO Bugs (status) VALUES ('NEW'); -- OK
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Bugs (status) VALUES ('BANANA'); -- エラー!
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dev`.`Bugs`, CONSTRAINT `Bugs_ibfk_1` FOREIGN KEY (`status`) REFERENCES `BugStatus` (`status`) ON UPDATE CASCADE)
mysql> SELECT status FROM Bugs;
+--------+
| status |
+--------+
| NEW |
+--------+
1 row in set (0.00 sec)



10.5.1 値セットの取得

楽だね。ソート順序もアルファベット順。


31-Flavors/soln/query-canonical-values

mysql>  SELECT status FROM BugStatus ORDER BY status;

+-------------+
| status |
+-------------+
| FIXED |
| IN PROGRESS |
| NEW |
+-------------+
3 rows in set (0.00 sec)


10.5.2 参照テーブルの値の更新

瞬殺。


31-Floavors/soln/insert-value

mysql> INSERT INTO BugStatus (status) VALUES ('DUPLICATE');

Query OK, 1 row affected (0.00 sec)

ON UPDATE CASCADEを使っていれば、 値の Rename も楽々。


10.5.3 廃止された値のサポート

BugsStatus に、その値が ACTIVE か INACTIVE かを保持するようにすれば解決。


31-Floavors/soln/inactive

mysql> ALTER TABLE BugStatus ADD COLUMN active

-> ENUM('INACTIVE', 'ACTIVE') NOT NULL DEFAULT 'ACTIVE';
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> DESCRIBE BugStatus;
+--------+---------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------------+------+-----+---------+-------+
| status | varchar(20) | NO | PRI | NULL | |
| active | enum('INACTIVE','ACTIVE') | NO | | ACTIVE | |
+--------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


値を廃止するときは、DELETE ではなく UPDATE で INACTIVE にする。

mysql> UPDATE BugStatus SET active = 'INACTIVE' WHERE status = 'DUPLICATE';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT status FROM BugStatus WHERE active = 'ACTIVE';
+-------------+
| status |
+-------------+
| FIXED |
| IN PROGRESS |
| NEW |
+-------------+
3 rows in set (0.00 sec)

mysql> SELECT status FROM BugStatus WHERE active = 'INACTIVE';
+-----------+
| status |
+-----------+
| DUPLICATE |
+-----------+
1 row in set (0.00 sec)

これで過去に使っていて廃止された値だけを除外するのも楽々。


10.5.4 移植が容易

MySQLしか使えない、MySQLでは使えない...といった事がない。


まとめ

列に入力する値を限定するときは、値セットが固定されている場合はメタデータを、流動的な場合はデータを用いましょう。