正規化(縦持ち、行持ち)データに対する検索の実装(SQL編)

こんにちは、アルバイトでSymfony2を使って社内システムの開発をしているものです。

今回は、データベースに関するお話です。

MySQLで、以下のようなテーブルを考えてみましょう。

テーブル test_option

CREATE TABLE IF NOT EXISTS test_option (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  <code>name</code> varchar(255) NOT NULL,
  option_1 varchar(255) NOT NULL,
  option_2 varchar(255) NOT NULL,
  option_3 varchar(255) NOT NULL,
  UNIQUE KEY id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

このような横持ちテーブルならば、option_1がgoodかつoption_2がbadの人を抽出する場合、以下のSQL文を発行すればよいです。

SELECT name FROM test_option
WHERE
option_1 = "good"
AND
option_2 = "bad"

結果：

こちらに関しては特に難しいことはないので、特に解説をしません。

続いて、オプションに関して、

• オプションの数が可変である
• オプションに対してValueが入らないことがある
• オプションの数が膨大である

等の事態が発生し、データの横持ちが合理的ではなくなり、縦持ちをするようになったとしましょう。

テーブル test_name

CREATE TABLE IF NOT EXISTS test_name (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  <code>name</code> varchar(255) NOT NULL,
  UNIQUE KEY id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

テーブル test_option_2

CREATE TABLE IF NOT EXISTS <code>test_option_2</code> (
  <code>id</code> bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  <code>name_id</code> int(11) NOT NULL,
  <code>option_id</code> int(11) NOT NULL,
  <code>value</code> varchar(255) NOT NULL,
  UNIQUE KEY <code>id</code> (<code>id</code>),
  KEY <code>name_id</code> (<code>name_id</code>,<code>option_id</code>)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

現実には、さらにオプションの名前用のマスターテーブルがないといけませんが、ここでは省略します。

さて、ここでoption_1(option_id = 1)がgoodかつoption_2(option_id = 2)がbadであるような人を抽出するにはどうすればよいでしょうか？

ここで先ほどのSQL文をそのまま適用してみましょう。以下のSQL文を

SELECT * FROM test_option_2
WHERE 
option_id = 1
AND 
value = "good"
AND
option_id = 2
AND 
value = "bad"

を発行すると、当たり前ですが、結果セットは空になります。ちなみにOR検索の場合はうまくいきます。

ここで、以下のようなSQL文を考えてみましょう

SELECT opt_1.* FROM test_option_2 opt_1
LEFT JOIN test_option_2 opt_2
ON opt_1.name_id = opt_2.name_id
WHERE 
(
    opt_1.option_id = 1
    AND 
    opt_1.value = "good"
)
AND
(
    opt_2.option_id = 2
    AND 
    opt_2.value = "bad"
)

目的の結果ですね。後はこの結果に対し、test_nameからJOINをおこないましょう。もちろんOR検索に関してもうまくいきます。

ポイントは、縦持ちから横持ちへの変換をおこない検索をかけている点です。

縦持ちから横持ちへの変換は以下のページが参考になると思います。

https://gist.github.com/aamine/5565025

上記ページでは、単一のテーブルから集計関数を用いて取得していますが、こちらでは、JOINを利用する代わりに、集計関数を用いていません。また、検索内でWHERE文において一気に絞られるてめ、速度的にも問題はないと思います。

今回は以上です、次回は本検索をPHPで実装する方法を書きたいと思います。