MySQL5.1の「トリガ」について質問です。


クエリした結果を再クエリするようなSQL文が自力で書けなくて困っています。
またデータが増えると処理に時間がかかってしまいます。

そこで「トリガ」を使って予めバックグランドでデータを処理しておけば簡単なクエリで済むと考えました。

質問1)
トリガやストアドプロシージャについて解説した情報源を教えてください。
本を見てもあまり載っていません。とりあえずここは見ましたが、ちょっと情報が少なくて困っています。
http://dev.mysql.com/doc/refman/5.1/ja/triggers.html

質問2)
phpMyAdmin2.8.2を使っていますが、ここからトリガ文を実行できるのでしょうか?何度か試したのですがエラーになってしまいます。

どちらかの質問だけで構いません。よろしくお願いします。

回答の条件
  • 1人2回まで
  • 登録:2007/03/31 17:05:12
  • 終了:2007/04/03 15:06:42

ベストアンサー

id:kurukuru-neko No.2

kurukuru-neko回答回数1844ベストアンサー獲得回数1552007/04/01 15:31:42

ポイント100pt

(1)

前回と同様のテーブルで例にすると

TRIGGER等で書く場合は、tagについて

は処理が遅くなるので文字でなく数字

カラムにします。

tagの意味も評価をでソートにも使えるように

正の整数とします。

tagの意味

100 面白い

50 非常につまらない

0 その他

Table_Aに集計用にはtagの修正用のカラム

とtag更新日を追加したと仮定

rank1 ( tag=100 集計)

rank2 ( tag= 50 集計)

rank3 ( tag=100,50以外値集計)

rankdate (tagの登録された日)

alter table Table_A add rank1 int default 0;

alter table Table_A add rank2 int default 0;

alter table Table_A add rank3 int default 0;

alter table Table_A add rankdate datetime;

TRIGGERは動作確認しにくいので

処理は、PROCEDUREで作成する。

DELIMITER ||

CREATE TRIGGER TRIG_Insert AFTER INSERT ON Table_B

FOR EACH ROW BEGIN

CALL PROC_Add(NEW.book_id,NEW.tag);

END;

||

CREATE TRIGGER TRIG_Update AFTER UPDATE ON Table_B

FOR EACH ROW BEGIN

if OLD.tag != NEW.tag then

CALL PROC_Sub(OLD.book_id,OLD.tag);

CALL PROC_Add(NEW.book_id,NEW.tag);

end if;

END;

||

CREATE TRIGGER TRIG_Delete AFTER DELETE ON Table_B

FOR EACH ROW BEGIN

CALL PROC_Sub(OLD.book_id,OLD.tag);

END;

||

DELIMITER ;

PROCEDUREで各カラム処理を行う。

DELIMITER ||

create procedure PROC_Add(in in_book_id int,in in_tag int)

begin

declare setcol int default 3;

case in_tag

when 100 then set setcol=1;

when 50 then set setcol=2;

else set setcol=3;

end case;

update Table_A set rank1=IF(setcol=1,rank1+1,rank1),

rank2=IF(setcol=2,rank2+1,rank2),

rank3=IF(setcol=3,rank3+1,rank3),

rankdate=now()

where book_id = in_book_id;

end;

||

create procedure PROC_Sub(in in_book_id int,in in_tag int)

begin

declare setcol int default 3;

case in_tag

when 100 then set setcol=1;

when 50 then set setcol=2;

else set setcol=3;

end case;

update Table_A set rank1=IF(setcol=1,if(rank1>0,rank1-1,rank1),rank1),

rank2=IF(setcol=2,if(rank2>0,rank2-1,rank2),rank2),

rank3=IF(setcol=3,if(rank3>0,rank3-1,rank3),rank3)

where book_id = in_book_id;

END;

||

DELIMITER ;

動作確認は、SQLで

SELECT * FROM Table_A where book_id=1234;

CALL PROC_Add(1234,100);

SELECT * FROM Table_A where book_id=1234;

CALL PROC_Sub(1234,100);

SELECT * FROM Table_A where book_id=1234;

等すれば可能。

登録済みのデータや不整合に

なった場合のや、何らかの不具合で

再度値を計算しなおす処理

(プログラムで CALL PROC_Rebuildを呼び出す

か手動)

DELIMITER ||

create procedure PROC_Rebuild()

begin

update Table_A set rank1=0,rank2=0,rank3=0;

update Table_A as a

left join (select book_id,tag,count(tag) as tagc from Table_B where tag=100 group by book_id) as b

using(book_id) set rank1 =b.tagc where tagc is not null;

update Table_A as a

left join (select book_id,tag,count(tag) as tagc from Table_B where tag=50 group by book_id) as b

using(book_id) set rank2=b.tagc where tagc is not null;

update Table_A as a

left join (select book_id,tag,count(tag) as tagc from Table_B group by book_id) as b

on a.book_id=b.book_id set rank3=b.tagc-rank1-rank2 where tagc is not null;

end;

||

DELIMITER ;

例えば

RANK1が一番多いものを検索する場合は、

select * from Table_A where greatest(rank1,rank2,rank3)=rank1;

RANK1が一番少ないのを検索する場合は、

select * from Table_A where least(rank1,rank2,rank3)=rank1;

応用すればrankdateを検索条件に追加して

何日以内に更新があった場合なども可能。


(2)

SQLを手動で入力すれば全て可能

削除のしかた

drop procedure IF EXISTS 名前;

drop function IF EXISTS 名前;

drop trigger 名前;

登録の確認

select TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_OBJECT_TABLE,ACTION_TIMING from information_schema.TRIGGERS;

select db,name,type,specific_name from mysql.proc order by type;

定義済み内容の確認

show create procedure 名前;

show create function 名前;

show triggers

ERRORを見る

show errors;

show warnings;

show logs;

http://dev.mysql.com/doc/refman/5.1/en/show.html

id:tokyosmash

いつも丁寧にありがとうございます!kurukuru-nekoさんの回答を見るだけで一通りの事はできそうです。


phpMyAdminからでも使えるのですね。手動でSQLを入力したのですがエラーで出てしまって・・。何か文法が間違っていたのでしょうか。


コマンドラインで作ったTriggerをshow triggersで見る事はできました。今度は作るところからphpMyAdminでやってみようと思います。


とにかくTriggerを使えばSQL文が簡単になるのでは、と期待しています。その一方思いもよらない動作をしてしまってデータが壊れてしまうのも危惧しています。バックグラウンドで動作するのでわかりづらいですよね。


最初はあまり重要じゃないデータを使って練習してから本番運用してみます。


それにしてもTriggerって凄く便利だと思うのですが、検索してもあまり情報が出てきません。みなさんあまり使ってないのでしょうか。

2007/04/02 00:55:21

その他の回答(1件)

id:hamster001 No.1

hamster001回答回数474ベストアンサー獲得回数142007/03/31 20:13:39

id:tokyosmash

ありがとうございます。参考にさせて頂きます。

2007/03/31 20:15:17
id:kurukuru-neko No.2

kurukuru-neko回答回数1844ベストアンサー獲得回数1552007/04/01 15:31:42ここでベストアンサー

ポイント100pt

(1)

前回と同様のテーブルで例にすると

TRIGGER等で書く場合は、tagについて

は処理が遅くなるので文字でなく数字

カラムにします。

tagの意味も評価をでソートにも使えるように

正の整数とします。

tagの意味

100 面白い

50 非常につまらない

0 その他

Table_Aに集計用にはtagの修正用のカラム

とtag更新日を追加したと仮定

rank1 ( tag=100 集計)

rank2 ( tag= 50 集計)

rank3 ( tag=100,50以外値集計)

rankdate (tagの登録された日)

alter table Table_A add rank1 int default 0;

alter table Table_A add rank2 int default 0;

alter table Table_A add rank3 int default 0;

alter table Table_A add rankdate datetime;

TRIGGERは動作確認しにくいので

処理は、PROCEDUREで作成する。

DELIMITER ||

CREATE TRIGGER TRIG_Insert AFTER INSERT ON Table_B

FOR EACH ROW BEGIN

CALL PROC_Add(NEW.book_id,NEW.tag);

END;

||

CREATE TRIGGER TRIG_Update AFTER UPDATE ON Table_B

FOR EACH ROW BEGIN

if OLD.tag != NEW.tag then

CALL PROC_Sub(OLD.book_id,OLD.tag);

CALL PROC_Add(NEW.book_id,NEW.tag);

end if;

END;

||

CREATE TRIGGER TRIG_Delete AFTER DELETE ON Table_B

FOR EACH ROW BEGIN

CALL PROC_Sub(OLD.book_id,OLD.tag);

END;

||

DELIMITER ;

PROCEDUREで各カラム処理を行う。

DELIMITER ||

create procedure PROC_Add(in in_book_id int,in in_tag int)

begin

declare setcol int default 3;

case in_tag

when 100 then set setcol=1;

when 50 then set setcol=2;

else set setcol=3;

end case;

update Table_A set rank1=IF(setcol=1,rank1+1,rank1),

rank2=IF(setcol=2,rank2+1,rank2),

rank3=IF(setcol=3,rank3+1,rank3),

rankdate=now()

where book_id = in_book_id;

end;

||

create procedure PROC_Sub(in in_book_id int,in in_tag int)

begin

declare setcol int default 3;

case in_tag

when 100 then set setcol=1;

when 50 then set setcol=2;

else set setcol=3;

end case;

update Table_A set rank1=IF(setcol=1,if(rank1>0,rank1-1,rank1),rank1),

rank2=IF(setcol=2,if(rank2>0,rank2-1,rank2),rank2),

rank3=IF(setcol=3,if(rank3>0,rank3-1,rank3),rank3)

where book_id = in_book_id;

END;

||

DELIMITER ;

動作確認は、SQLで

SELECT * FROM Table_A where book_id=1234;

CALL PROC_Add(1234,100);

SELECT * FROM Table_A where book_id=1234;

CALL PROC_Sub(1234,100);

SELECT * FROM Table_A where book_id=1234;

等すれば可能。

登録済みのデータや不整合に

なった場合のや、何らかの不具合で

再度値を計算しなおす処理

(プログラムで CALL PROC_Rebuildを呼び出す

か手動)

DELIMITER ||

create procedure PROC_Rebuild()

begin

update Table_A set rank1=0,rank2=0,rank3=0;

update Table_A as a

left join (select book_id,tag,count(tag) as tagc from Table_B where tag=100 group by book_id) as b

using(book_id) set rank1 =b.tagc where tagc is not null;

update Table_A as a

left join (select book_id,tag,count(tag) as tagc from Table_B where tag=50 group by book_id) as b

using(book_id) set rank2=b.tagc where tagc is not null;

update Table_A as a

left join (select book_id,tag,count(tag) as tagc from Table_B group by book_id) as b

on a.book_id=b.book_id set rank3=b.tagc-rank1-rank2 where tagc is not null;

end;

||

DELIMITER ;

例えば

RANK1が一番多いものを検索する場合は、

select * from Table_A where greatest(rank1,rank2,rank3)=rank1;

RANK1が一番少ないのを検索する場合は、

select * from Table_A where least(rank1,rank2,rank3)=rank1;

応用すればrankdateを検索条件に追加して

何日以内に更新があった場合なども可能。


(2)

SQLを手動で入力すれば全て可能

削除のしかた

drop procedure IF EXISTS 名前;

drop function IF EXISTS 名前;

drop trigger 名前;

登録の確認

select TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_OBJECT_TABLE,ACTION_TIMING from information_schema.TRIGGERS;

select db,name,type,specific_name from mysql.proc order by type;

定義済み内容の確認

show create procedure 名前;

show create function 名前;

show triggers

ERRORを見る

show errors;

show warnings;

show logs;

http://dev.mysql.com/doc/refman/5.1/en/show.html

id:tokyosmash

いつも丁寧にありがとうございます!kurukuru-nekoさんの回答を見るだけで一通りの事はできそうです。


phpMyAdminからでも使えるのですね。手動でSQLを入力したのですがエラーで出てしまって・・。何か文法が間違っていたのでしょうか。


コマンドラインで作ったTriggerをshow triggersで見る事はできました。今度は作るところからphpMyAdminでやってみようと思います。


とにかくTriggerを使えばSQL文が簡単になるのでは、と期待しています。その一方思いもよらない動作をしてしまってデータが壊れてしまうのも危惧しています。バックグラウンドで動作するのでわかりづらいですよね。


最初はあまり重要じゃないデータを使って練習してから本番運用してみます。


それにしてもTriggerって凄く便利だと思うのですが、検索してもあまり情報が出てきません。みなさんあまり使ってないのでしょうか。

2007/04/02 00:55:21
  • id:kurukuru-neko

    >Triggerって凄く便利

    物は使い方しだいです。
    MYSQLは最新の5.xからなので
    まだ使われていないのでしょう。

    Mysql以外では、Trigger/Stored Procedure
    普通に使います。

    但し、Triggerであまり複雑な処理は
    考えものです。 単純なものに限定するか
    Stored Procedureを呼び出す方法に
    すべきです。

    通常Trigger/Stored Procedureを
    使う場合、Transaction処理を普通前提
    とはしています。

    今回の処理でもTriggerを使わず
    Stored Procedureだけでも同様の
    処理は出来ます。
    あとはシステムの設計方針でどのようにDB処理
    を行うかを明確にしないと収集がつかなくなり
    ます。

    何れを使わなくてもPHPでプログラムで処理
    する方法でも当然出来ます。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません