裏MySQLクエリー入門(14) 番外編1 複数カテゴリーの正しいデータの持ち方

mysql

　裏MySQLクエリー入門(13)で出したサンプルですがデータ構造としては最悪です。これは実際に遭遇したデータ構造なのですが、これは、プログラマーがメインの人が良くやってしまうデータ構造ですね。カテゴリーIDを文字列として、カンマ区切りで格納...。気持ちは分からなくないですが、FIND_IN_SETを使ったトリッキーな結合*1を行う必要があったり、かえって大変な事になります。
また、varchar(255)*2がカテゴリーの格納領域となる為、以下のように最大88カテゴリーしか保存できません。　
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88

それでは、これを正しく修正してみましょう。

  • まずカテゴリ用テーブルを新規に作成します。
CREATE TABLE IF NOT EXISTS `platform_program` (
  `platform_id` int(11) NOT NULL COMMENT 'プラットフォームID',
  `program_id`  int(11) NOT NULL COMMENT 'プログラムID',
  PRIMARY KEY (`platform_id`,`program_id`),
  KEY `platform_id` (`platform_id`),
  KEY `program_id` (`program_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp932 COMMENT='プラットフォームプログラム';
  • カテゴリー用のデータを作ります。
SELECT platform_id,program_id 
FROM  `platform` pf,  `program` pg
WHERE FIND_IN_SET( pg.program_id, pf.program_ids ) >0
platform_id program_id
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
3 4
4 4
5 1
5 2
  • 作ったデータをカテゴリーテーブルに流し込みます。
INSERT INTO  platform_program (platform_id,program_id) 
SELECT platform_id,program_id 
FROM  `platform` pf,  `program` pg
WHERE FIND_IN_SET( pg.program_id, pf.program_ids ) >0
  • プラットフォームテーブルからprogram_idsを削除
ALTER TABLE `platform` 
DROP `program_ids`
  • 以上3つのテーブルを結合して上げれば完成です。
SELECT pf.platform_id,pf.platform_name,pg.program_id,pg.program_name 
FROM  `platform` pf, platform_program pp, program pg 
WHERE pf.platform_id = pp.platform_id 
AND pp.program_id = pg.program_id 
ORDER BY pf.platform_id
platform_id platform_name program_id program_name
1 Windows 1 JAVA
1 Windows 2 ActionScript
1 Windows 3 PHP
2 Linux 1 JAVA
2 Linux 2 ActionScript
2 Linux 3 PHP
3 Mac OS X 1 JAVA
3 Mac OS X 2 ActionScript
3 Mac OS X 3 PHP
3 Mac OS X 4 Objective-C
4 iphone 4 Objective-C
5 Android 1 JAVA
5 Android 2 ActionScript
  • 3テーブル結合はちと面倒くさいので、この程度のレベルであればviewを作るのもアリかと
CREATE OR REPLACE 
ALGORITHM = UNDEFINED
VIEW  `v_platform` AS 
SELECT pf.platform_id, pf.platform_name, pg.program_id, pg.program_name
FROM  `platform` pf, platform_program pp, program pg
WHERE pf.platform_id = pp.platform_id
AND pp.program_id = pg.program_id
ORDER BY pf.platform_id

*1:他のRDBMSだともっと面倒なことになります

*2:最近のMySQLでは65535文字まで拡張されていますが、255バイト消耗している時点で無駄でしょう。