2008-10-24

動的に属性を拡張できるテーブルの設計メモ

SQL

データベースを使って、運用中に動的に属性を拡張できるような仕組みを作りたい場合の設計メモです。

拡張属性に対応する「キー」と「値」のテーブルを追加で用意して、
拡張元のテーブルと関連づける

感じで実現します。

テーブル設計

kittenテーブルの属性を拡張できるようにしたいとして、テーブルは次のような感じになります。

名前	説明
kitten	拡張元のテーブル。あらかじめ決まっている属性をカラムとして持つ。
ex_key	拡張属性のキーを格納するテーブル。
ex_value	拡張属性の値を格納するテーブル。外部参照キーとして、拡張属性IDとkittenのIDを持ち、「kitten+拡張属性+値」を関連づける。

MySQLで作る場合のSQL文は以下の通りです。

create table kitten (
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL DEFAULT '',
  PRIMARY KEY(id)
);

create table ex_key (
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL DEFAULT '',
  PRIMARY KEY(id)
);

create table ex_value (
  kitten_id BIGINT NOT NULL,
  key_id BIGINT NOT NULL,
  value VARCHAR(20) NOT NULL DEFAULT '',
  FOREIGN KEY (kitten_id) REFERENCES kitten(id) ON DELETE CASCADE,
  FOREIGN KEY (key_id) REFERENCES ex_key(id) ON DELETE CASCADE,
  PRIMARY KEY(kitten_id,key_id)
);

拡張属性の取り出し

指定された拡張属性をkittenの属性と共に取り出すには、
JOINするか、

select 
  id, 
  name,
  ex1.value as ex1_value,
  ex2.value as ex2_value
from kitten 
  left join ex_value as ex1 on ex1.kitten_id = kitten.id and ex1.key_id = <1つめの拡張属性キーのID>
  left join ex_value as ex2 on ex2.kitten_id = kitten.id and ex2.key_id = <2つめの拡張属性キーのID>;

サブクエリを使ってもできます。

select 
  id, 
  name,
  (select value from ex_value where kitten_id=id and key_id=<1つめの拡張属性キーのID> ) as ex1_value,
  (select value from ex_value where kitten_id=id and key_id=<2つめの拡張属性キーのID> ) as ex2_value
from kitten;

データベースに次のようなデータが入っている場合、

mysql> select * from kitten;
+----+-------+
| id | name  |
+----+-------+
|  1 | mii   |
|  2 | shiro |
|  3 | tora  |
+----+-------+
3 rows in set (0.00 sec)

mysql> select * from ex_key;
+----+------+
| id | name |
+----+------+
|  1 | type |
|  2 | age  |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from ex_value;
+-----------+--------+---------+
| kitten_id | key_id | value   |
+-----------+--------+---------+
|         1 |      1 | mike    |
|         2 |      1 | persian |
|         1 |      2 | 1       |
|         2 |      2 | 2       |
|         3 |      2 | 1       |
+-----------+--------+---------+
5 rows in set (0.00 sec)

実行結果は以下のようになります。

mysql> select 
    ->   id, 
    ->   name,
    ->   ex1.value as ex1_value,
    ->   ex2.value as ex2_value
    -> from kitten 
    ->   left join ex_value as ex1 on ex1.kitten_id = kitten.id and ex1.key_id = 1
    ->   left join ex_value as ex2 on ex2.kitten_id = kitten.id and ex2.key_id = 2;
+----+-------+-----------+-----------+
| id | name  | ex1_value | ex2_value |
+----+-------+-----------+-----------+
|  1 | mii   | mike      | 1         |
|  2 | shiro | persian   | 2         |
|  3 | tora  | NULL      | 1         |
+----+-------+-----------+-----------+
3 rows in set (0.04 sec)

mysql> select 
    ->   id, 
    ->   name,
    ->   (select value from ex_value where kitten_id=id and key_id=1 ) as ex1_value,
    ->   (select value from ex_value where kitten_id=id and key_id=2 ) as ex2_value
    -> from kitten;
+----+-------+-----------+-----------+
| id | name  | ex1_value | ex2_value |
+----+-------+-----------+-----------+
|  1 | mii   | mike      | 1         |
|  2 | shiro | persian   | 2         |
|  3 | tora  | NULL      | 1         |
+----+-------+-----------+-----------+
3 rows in set (0.00 sec)

拡張属性での検索とソート

↑で取り出したデータに対して、検索とソートを行なえばOK。

select * from (
  select 
    id, 
    name,
    ex1.value as ex1_value,
    ex2.value as ex2_value
  from kitten 
    left join ex_value as ex1 on ex1.kitten_id = kitten.id and ex1.key_id = <1つめの拡張属性キーのID>
    left join ex_value as ex2 on ex2.kitten_id = kitten.id and ex2.key_id = <2つめの拡張属性キーのID>
) as X where ex1_value is not null order by ex2_value;

実行結果です。

mysql> select * from (
    ->   select 
    ->     id, 
    ->     name,
    ->     ex1.value as ex1_value,
    ->     ex2.value as ex2_value
    ->   from kitten 
    ->     left join ex_value as ex1 on ex1.kitten_id = kitten.id and ex1.key_id = 1
    ->     left join ex_value as ex2 on ex2.kitten_id = kitten.id and ex2.key_id = 2
    -> ) as X where ex1_value is not null order by ex2_value;
+----+-------+-----------+-----------+
| id | name  | ex1_value | ex2_value |
+----+-------+-----------+-----------+
|  1 | mii   | mike      | 1         |
|  2 | shiro | persian   | 2         |
+----+-------+-----------+-----------+
2 rows in set (0.00 sec)

補足

↑の例のように拡張属性キーの属性が「名前」だけなら「ex_value」と統合してもよいかも。今回は拡張属性キーに他にもいろいろと属性があるので、別テーブルとなっています(たぶん)。

2007-06-02

UNION

SQL

UNIONで、複数のSQL実行結果の「和」を返します。

構文:

<SQL文1> union <SQL文2>;

サンプル:

select name from kittens where id between 1 and 2 
union 
select name from kittens where id between 2 and 3;

データ:

mysql> select * from kittens;
+----+-------+-----------+
| id | name  | friend_id |
+----+-------+-----------+
|  1 | mii   |         3 |
|  2 | tora  |         3 |
|  3 | shiro |         1 |
|  4 | kuro  |      NULL |
+----+-------+-----------+
4 rows in set (0.01 sec)

サンプルの実行結果:

mysql> select name from kittens where id between 1 and 2 union select name from kittens where id between 2 and 3;
+-------+
| name  |
+-------+
| mii   |
| tora  |
| shiro |
+-------+
3 rows in set (0.00 sec)

実行結果の比較を行うため、UNIONするSQLは同じカラムを返すSQL同士でなければなりません。カラムの数が違ったりする場合、エラーです。

mysql> select name from kittens where id = 1 union select * from kittens where id = 2;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

参考:"TECHSCORE-SQL-クエリの結合"

2007-06-01

内部結合と外部結合

SQL

内部結合と外部結合について。
例は以下のテーブルに対してSQLを発行した結果です。

mysql> select * from kittens;
+----+-------+---------+
| id | name  | type_id |
+----+-------+---------+
|  1 | mii   |       1 |
|  2 | tora  |       2 |
|  3 | shiro |       1 |
|  4 | kuro  |       4 |
+----+-------+---------+
4 rows in set (0.02 sec)

mysql> select * from types;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | mike          |
|  2 | scottish-fold |
|  3 | egyptian mau  |
+----+---------------+
3 rows in set (0.01 sec)

内部結合(INNER JOIN)

左(kittens)のテーブルの一覧を取得し、
右(types)テーブルから、ONで指定した条件にマッチするレコードを探してマージします。
マッチするレコードが複数あった場合、マージしたものをそれぞれ別のレコードにします。
左(kittens)のテーブル一覧のうち、条件にマッチするレコードがないものは削除されます。

例:

mysql> select * from kittens join types on kittens.type_id = types.id;
+----+-------+---------+----+---------------+
| id | name  | type_id | id | name          |
+----+-------+---------+----+---------------+
|  1 | mii   |       1 |  1 | mike          |
|  3 | shiro |       1 |  1 | mike          |
|  2 | tora  |       2 |  2 | scottish-fold |
+----+-------+---------+----+---------------+
3 rows in set (0.01 sec)

外部結合(LEFT OUTER JOIN)

左(kittens)のテーブルの一覧を取得し、
右(types)テーブルから、ONで指定した条件にマッチするレコードを探してマージします。
マッチするレコードが複数あった場合、マージしたものをそれぞれ別のレコードにします。
左(kittens)のテーブルの一覧のうち、条件にマッチするレコードがないものの値はNULLになります。レコードは削除されません。

例:

mysql> select * from kittens left join types on kittens.type_id = types.id;
+----+-------+---------+------+---------------+
| id | name  | type_id | id   | name          |
+----+-------+---------+------+---------------+
|  1 | mii   |       1 |    1 | mike          |
|  2 | tora  |       2 |    2 | scottish-fold |
|  3 | shiro |       1 |    1 | mike          |
|  4 | kuro  |       4 | NULL | NULL          |
+----+-------+---------+------+---------------+
4 rows in set (0.01 sec)

外部結合(RIGHT OUTER JOIN)

右側のテーブルを軸にして外部結合します。

右(types)のテーブルの一覧を取得し、
左(kittens)テーブルから、ONで指定した条件にマッチするレコードを探してマージします。
マッチするレコードが複数あった場合、マージしたものをそれぞれ別のレコードにします。
右(types)のテーブルの一覧のうち、条件にマッチするレコードがないものの値はNULLになります。レコードは削除されません。

例:

mysql> select * from kittens right join types on kittens.type_id = types.id;
+------+-------+---------+----+---------------+
| id   | name  | type_id | id | name          |
+------+-------+---------+----+---------------+
|    1 | mii   |       1 |  1 | mike          |
|    3 | shiro |       1 |  1 | mike          |
|    2 | tora  |       2 |  2 | scottish-fold |
| NULL | NULL  |    NULL |  3 | egyptian mau  |
+------+-------+---------+----+---------------+

完全結合(FULL JOIN)

MySQLでは未対応のようで、試せていません..orz。外部結合を右左両方で行うようです。

参考:

2007-05-31

select の from にテーブルを並べる

SQL

select の from にテーブルを並べると、テーブル同士が総当たりで結合されます。さらにwhereで検索条件を指定して絞り込むと、内部結合した場合と同じような出力を得ることができます。

結合するテーブル

mysql> select * from kittens;
+----+-------+---------+
| id | name  | type_id |
+----+-------+---------+
|  1 | mii   |       1 |
|  2 | tora  |       2 |
|  3 | shiro |       1 |
|  4 | kuro  |       4 |
+----+-------+---------+
4 rows in set (0.02 sec)

mysql> select * from types;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | mike          |
|  2 | scottish-fold |
|  3 | egyptian mau  |
+----+---------------+
3 rows in set (0.01 sec)

from にテーブルを並べる

二つのテーブルを総当たりした結果が返されます。

mysql> select * from kittens, types;
+----+-------+---------+----+---------------+
| id | name  | type_id | id | name          |
+----+-------+---------+----+---------------+
|  1 | mii   |       1 |  1 | mike          |
|  2 | tora  |       2 |  1 | mike          |
|  3 | shiro |       1 |  1 | mike          |
|  4 | kuro  |       4 |  1 | mike          |
|  1 | mii   |       1 |  2 | scottish-fold |
|  2 | tora  |       2 |  2 | scottish-fold |
|  3 | shiro |       1 |  2 | scottish-fold |
|  4 | kuro  |       4 |  2 | scottish-fold |
|  1 | mii   |       1 |  3 | egyptian mau  |
|  2 | tora  |       2 |  3 | egyptian mau  |
|  3 | shiro |       1 |  3 | egyptian mau  |
|  4 | kuro  |       4 |  3 | egyptian mau  |
+----+-------+---------+----+---------------+
12 rows in set (0.03 sec)

whereを追加

総当たりした結果をwhereの条件で絞り込んだ結果が返されます。

mysql> select * from kittens, types where kittens.type_id = types.id;
+----+-------+---------+----+---------------+
| id | name  | type_id | id | name          |
+----+-------+---------+----+---------------+
|  1 | mii   |       1 |  1 | mike          |
|  3 | shiro |       1 |  1 | mike          |
|  2 | tora  |       2 |  2 | scottish-fold |
+----+-------+---------+----+---------------+
3 rows in set (0.00 sec)

これは内部結合を使った以下のSQL文と同じ結果になります。

mysql> select * from kittens join types on kittens.type_id = types.id;
+----+-------+---------+----+---------------+
| id | name  | type_id | id | name          |
+----+-------+---------+----+---------------+
|  1 | mii   |       1 |  1 | mike          |
|  3 | shiro |       1 |  1 | mike          |
|  2 | tora  |       2 |  2 | scottish-fold |
+----+-------+---------+----+---------------+
3 rows in set (0.00 sec)