Go to Qiita Advent Calendar 2024 Top
LoginSignup
20
19

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@Inagawa

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

Posted at

少し変わった名前の章だが、これは本章の問題を、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           | いいえ  | はい   | はい    | はい                |
+----------------------+--------+--------+---------+--------------------+

[参照元: データ型サポート] (http://www-01.ibm.com/support/knowledgecenter/SSBLQQ_8.5.1/com.ibm.rational.rtvs.ref.doc/topics/c_rtvs_db_stubs_data_type_support.html?cp=SSBLQQ_8.5.1%2F1-6-6-3-1-4-0-3&lang=ja)

結論: これらの方法が、他の 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では使えない...といった事がない。

まとめ

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

20
19
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
20
19

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?