Hatena::Groupasakura

浅倉卓司@blog風味? このページをアンテナに追加 RSSフィード

Error : RSSが取得できませんでした。

2008-02-27(Wed)

久々にトリガとかストアドプロシージャとか書いたよ。

| 17:39 | 久々にトリガとかストアドプロシージャとか書いたよ。 - 浅倉卓司@blog風味? を含むブックマーク 久々にトリガとかストアドプロシージャとか書いたよ。 - 浅倉卓司@blog風味? のブックマークコメント

 そういやMySQLは5.0からトリガとかストアドとか使えるようになったんだよね、ということで久々にそういうのを書いてみた。

DELIMITER //
CREATE TABLE `vote` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `target_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `target_id` (`target_id`)
)
//

CREATE TABLE `vote_count` (
  `target_id` int(10) unsigned NOT NULL,
  `total_count` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`target_id`)
)
//

CREATE TRIGGER _vote_after_insert
  AFTER INSERT ON vote FOR EACH ROW
    REPLACE INTO vote_count
    SET target_id   = NEW.target_id,
        total_count = (SELECT COUNT(*) FROM vote WHERE target_id = NEW.target_id)
//

 このほうが2回SQLを発行するより確実で速い。


 UPDATEやDELETEに対応させるなら、

DELIMITER //
CREATE PROCEDURE `_update_vote_count`( _target_id int )
BEGIN
  REPLACE INTO vote_count
  SET target_id = _target_id,
      total_count = (SELECT COUNT(*) FROM vote WHERE target_id = _target_id);
END
//
CREATE TRIGGER _vote_after_insert
  AFTER INSERT ON vote FOR EACH ROW
  CALL _update_vote_count( NEW.target_id )
//
CREATE TRIGGER _vote_after_delete
  AFTER DELETE ON vote FOR EACH ROW
  CALL _update_vote_count( OLD.target_id )
//
CREATE TRIGGER _vote_after_update
  AFTER UPDATE ON vote FOR EACH ROW
  BEGIN
    CALL _update_vote_count( OLD.target_id );
    CALL _update_vote_count( NEW.target_id );
  END
//

――とかすればいい。

 ただ、毎回 SELECT COUNT(*) されるのでちょっと重い。


 いちおう

DELIMITER //
CREATE PROCEDURE `_vote_count_inc`( _target_id int )
BEGIN
  DECLARE _exist int DEFAULT NULL;
  SELECT total_count INTO _exist
    FROM vote_count WHERE target_id = _target_id;
  IF _exist THEN
    UPDATE vote_count
    SET total_count = total_count + 1
    WHERE target_id = _target_id
    ;
  ELSE
    INSERT INTO vote_count
    SET target_id = _target_id,
        total_count = ( SELECT COUNT(*) FROM vote WHERE target_id = _target_id )
    ;
  END IF;
END
//
CREATE TRIGGER _vote_after_insert
  AFTER INSERT ON vote FOR EACH ROW
  CALL _vote_count_inc( NEW.target_id )
//

――みたいにすればいいんだけど、vote_countを他で更新されると整合性がとれないという欠点もある。


 こういう場合、一般的にはどうしてるんでしょ。

トラックバック - http://asakura.g.hatena.ne.jp/asakura-t/20080227
2004 | 01 | 02 | 03 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2005 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2006 | 01 | 02 | 03 | 04 | 05 | 06 | 08 | 09 | 10 | 11 | 12 |
2007 | 02 | 03 | 04 | 05 | 06 | 07 | 10 | 11 | 12 |
2008 | 02 | 03 | 04 | 06 | 09 | 10 | 11 | 12 |
2009 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 09 | 10 | 11 | 12 |
2010 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2011 | 01 | 02 | 03 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2012 | 02 | 03 | 04 | 05 | 07 | 08 | 10 | 11 | 12 |
2013 | 01 | 05 | 07 |
2014 | 01 | 02 |
2016 | 01 |
2017 | 01 | 05 |