Database JUNKY

MySQL,MariaDBを中心としたブログです

MySQL,MariaDBでタグ機能(TAG)を実現する(TOXI法)①タグ関連のスキーマ設計

f:id:hit10231023:20161026113033j:plain

MySQLでタグのしくみを作る?

f:id:hit10231023:20180309104332j:plain

MySQLタグの仕組みを作る場合は、どのような設計をすればいいのでしょうか? タグの設計にはいくつかの種類があり

  • Licious:コンテンツ系のデーブル1つの中にタグフィールドを持たせる方法
  • Scuttle: コンテンツ系とタグ系のテーブル2つでまかなうお不法
  • TOXI : コンテンツテーブル - 中間テーブル、タグテーブルの三種類からなる、TOXI等があります。

色々と調べた結果、TOXI方式が一番、納得感がありましたので、以降TOXI方を中心に説明していきます。

TOXI法では、

  • コンテンツ(日記とか、画像とか動画でもいいや)等のメインのテーブル
  • コンテンツと対象のタグのid
  • タグ名

の3つのテーブルを使ってタグ管理していきます。

Logicool ロジクール ワイヤレス ミニマウス ホワイト M187WH

Logicool ロジクール ワイヤレス ミニマウス ホワイト M187WH

テーブル構成

例えば、以下のようなテーブル構成があったとします。

  • 日記テーブル
CREATE TABLE `diaries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) not null default '0',
  `title` varchar(128) NOT NULL DEFAULT '',
  `detail` text ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
  • タグマップテーブル
CREATE TABLE `tag_maps` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `diary_id` bigint(20) unsigned NOT NULL,
  `tag_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
  • タグテーブル
CREATE TABLE `tags` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix01_tags` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

データを投入してみる

ちょっと、定義だけですとわからないので、データを投入してみます。

  • diary
> SELECT * FROM diary \G
*************************** 1. row ***************************
        id: 1
created_at: 2016-10-25 19:58:11
updated_at: 2016-10-25 19:58:11
   user_id: 0
     title: 今日の天気
    detail: 今日はいい天気だ、ポカポカ気持ちいい
1 row in set (0.00 sec)
  • tags
> SELECT * FROM tags \G
*************************** 1. row ***************************
        id: 1
created_at: 2016-10-25 19:59:53
updated_at: 2016-10-25 19:59:53
      name: 天気
*************************** 2. row ***************************
        id: 2
created_at: 2016-10-25 19:59:53
updated_at: 2016-10-25 19:59:53
      name: 晴れ
2 rows in set (0.17 sec)
  • tag_maps
> SELECT * FROM tag_maps \G
*************************** 1. row ***************************
        id: 1
created_at: 2016-10-25 20:02:02
updated_at: 2016-10-25 20:02:02
  diary_id: 1
    tag_id: 1
*************************** 2. row ***************************
        id: 2
created_at: 2016-10-25 20:02:02
updated_at: 2016-10-25 20:02:02
  diary_id: 1
    tag_id: 2
2 rows in set (0.00 sec)

もうデータ見るとわかっちゃうかもしれませんが、ここでのポイントは、tag_maps テーブルになります。tag_maps は、diary テーブルのidを把握し、それに関連する、tag名称と紐付けています。 これをSQLで結合して出しますと、この日記には、どのタグが入っているか確認することができます。

SQL

SELECT
  d.id AS diary_id,
  d.title AS title ,
  d.detail AS detail,
  GROUP_CONCAT(tag.name SEPARATOR ',') AS tag
FROM
  diary d
INNER JOIN
  tag_maps map
ON
  d.id = map.diary_id
INNER JOIN
  tags tag
ON
  map.tag_id = tag.id
GROUP BY
  d.id \G
*************************** 1. row ***************************
diary_id: 1
   title: 今日の天気
  detail: 今日はいい天気だ、ポカポカ気持ちいい
     tag: 天気,晴れ
1 row in set (0.00 sec)

特に注目いただきたいのが、tagというカラムですね。この日記には、天気と晴れというタグが設定できていると表現できるわけです。 こちらの例は、日記というコンテンツを起点としたSQLになりますが、角度を変えれば、

  • タグに"晴れ"を設定している日記の一覧を出すSQL

とか出すこともTOXI法であれば、柔軟にできますよね。

後ほど、細かく書きますので、気長にお待ち下さい。