ど素人から毛を生やす。<延>

ダブランとTRPGを愛する、社会不適合者ぎりぎりのweb会社員が、
仕事の技術碌を書き連ねたり夢を叶えようとしたり趣味にいそしんだりする場末のブログ。

HN 餅。
好きなポケモン ダブラン・バタフリー・ピンクの悪魔ラッキー・ナマコブシ 最近モスノウが気になる。

[MySQL]トリガーを使用して別テーブルに値を保存する

Web > Other 2022年12月1日

どもです。

あるDBに登録された電話番号が、ハイフンがあったりなかったりします。
これを検索するとき、ハイフンを問わず検索できるようにしたい。

SELECT * FROM tableA WHERE REPLACE(`tel`, '-', '') = '[検索語句]'

一見するとこれで問題ないですが、もしtableAに10万件のレコードが入っていたら…
検索の度に10万件のtelにREPLACEをかけることになります。当然、INDEXも効きません。ゾッとしますね。

そこで、レコードの登録時に予めハイフンを除去したデータを保持することにしました。
tableA.tel自体をハイフン禁止にすれば楽ですが、それは仕様上できないとのことで、検索用データを保存したtableBを作成。

では、このtableBにどうやってハイフンを除去したtableA.telを保存するか、ですが、トリガーというものを登録します。
トリガーとは、まあJSとかでよく見るやつです。特定の条件を満たしたとき、自動で動く処理。

DELIMITER |
CREATE TRIGGER [適当なトリガー名] AFTER INSERT ON tableA FOR EACH ROW
BEGIN 
REPLACE INTO tableB SET 
id  = NEW.id,
tel = REPLACE(NEW.tel, '-', '');
END;
|
DELIMITER ;

INSERT を UPDATE にすれば、UPDATEをトリガーにしてtableBへのREPLACEが着火します。
2つ用意しないとなのが面倒ですね。1トリガーでUPDATEとINSERTの両方させてくれりゃいいのに。

BEGIN ~ END; の間に記載した内容が、トリガー着火で実行されるイベントです。
普通のREPLACEと異なるのは、更新されたtableAの値を NEW.○○ という形で表記することですね。

DELIMITERって要るん??となるかもですが、登録するときには微妙に要ります。たぶん。

さて、このままだとtableAからレコードが消えても、tableBに電話番号が残ってしまいます。
それは宜しくないので、tableAのDELETEにもトリガーを設置します。

DELIMITER |
CREATE TRIGGER [適当なトリガー名] BEFORE DELETE ON tableA FOR EACH ROW
BEGIN 
DELETE FROM tableB WHERE id = OLD.id;
END;
|
DELIMITER ;

ミソになるのが、トリガ名の後ろ。AFTERからBEFOREに。カラムの表記がNEW.○○からOLD.○○なりました。

これは、AFTERが「更新後のtableA」、BEFOREが「更新前のtableA」を指し、NEW・OLDはそれぞれAFTER・BEFOREのときの値を指すからです。
INSERTやUPDATEは、大体のケースでは更新後の値を参照しますが、DELETEの後では参照できる値がありません。必ずBEFOREを拾います。

また、DELETEトリガーの注意点ですが、DELETE TABLE や TRUNCATE TABLE では着火しません。
そういった処理を行う場合は、tableBも一掃するよう実行側で調整が必要です。

この記事は役に立ちましたか?
  • _(:3」∠)_ 面白かった (0)
  • (・∀・) 参考になった (0)
  • (`・ω・´) 役に立った (0)