【MySQL】電話番号カラムがINSERT or UPDATEされた時に同テーブルの別カラムにハイフンを除外した電話番号を保存する

Question

件名のようなことは可能でしょうか？もし可能であればどのような方法でできますか？

少し調べてMySQLのトリガを使えばできそうだと思ったのですが、
詳しく教えていただけないでしょうか。
http://www.dbonline.jp/mysql/trigger/

※背景ですが、ECサイト運営しているのですが、
自社開発の管理画面の顧客電話番号検索でハイフンをREPLACEで置換して消して検索かけているのが原因で検索処理が重たいことが判明しました。（※今はハイフンと数字の組合せを1つのinputボックスに対してお客様が自由入力できる。）

そこでハイフン無しの電話番号を保存する新しいカラムを用意したいと思っています。
SELECTに関してはプログラムを書き換えて新しいカラムをサーチするように随時対応していけば良いのですが、
関連プログラムのINSERT／UPDATEを漏れ無く確認→修正というと結構大変です。

バッチ処理も考えましたが、リアルタイムでは無くなりますし、
もっとスマートな方法がないかと調べていました。
トリガは今まで一度も使用したことがないので教えてください。

Answer 1

Triggerについては
Kosuke_Shibuyaさんが仰っている内容になると思います。

WEBシステムになるとDBへの負荷とかも気になるところです。。。

できればqueryを投げる前にシステム側で手を打つのが理想かと思われます。

ECのシステムにもよりますが、大抵の場合はORMやPDO(Driverにもよりますが)部分はこういう時のために纏めて呼んでいる部分があると思うので。

Comments

[limit]

ご回答ありがとうございました。

Answer 2

関数インデックス(式インデックス)のようなことを実現したいということですね。
式インデックス - Wikipedia

式インデックス (英: expression index) または 関数インデックス は関係データベース管理システムで用いられるインデックスの中で、列の組み合わせの代わりに任意の式の結果をキーとするものを指す。式インデックスを使うと、テーブルに格納された実際のデータとは異なるキーでの検索が可能になる。

機能的には、MySQL 5.7.6で導入された**Generated Columns**で実現できそうです。
日々の覚書: MySQL 5.7.6のgenerated columnは関数インデックスの夢を見るか
InnoDBにおける効果的な関数インデックス(MySQL Server Blogより) | Yakst

ただ、おそらく、お使いのMySQLはもっと前のバージョンですよね？

MySQLには関数インデックスそのものも無い様なので、
トリガーでの実現が現実的ではないかと思います。
以下のサイトで、INSERTだけですが、似たようなことを実現しているようです。
裏MySQLクエリー入門(25) 基礎編14 俺々インデックスの作り方(関数インデックス) - イノベートな非日常

Comments

[limit]

ご回答ありがとうございます。はい、仰る通り使用しているMySQLは前のバージョン（5．1系）です。参考URLのご提示もありがとうございました！！

Answer 3

view を作る方が楽だと思いますが。

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tel` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE VIEW vw_users AS SELECT id, tel, REPLACE(tel, '-', '') AS tel_num FROM users

---

トリガを作成する場合、以下のようなSQLで行うわけですが…

DELIMITER $$
CREATE TRIGGER trigger_up AFTER UPDATE
 ON users FOR EACH ROW
 BEGIN
   UPDATE users SET tel_num = REPLACE(tel, '-', '');
 END;
$$
DELIMITER ;

Can't update table 'users' in stored function/trigger because it is already used by statem> ent which invoked this stored function/trigger.

マニュアルに以下の記載があります。

「ストアド ファンクションまたはトリガ内では、その関数やトリガを実行したステートメントが ( 読み取り、または書込みに ) すでに使用しているテーブルを改変することはできない。」

となると、ハイフンなしのデータを保存するために、別のテーブルを用意しなければなりません。それもまた、プログラムの修正にコストがかかるのではないかと。

Comments

[yuba]

MySQLのビューはマテリアライズドビューではないですから、今回の目標であるパフォーマンスの改善には役立たないですよね。

[limit]

Kosuke_Shibuya様、yuba様、ご回答ありがとうございます。
viewが何かわからなかったので、それを調べていた最中だったのですが、yuba様の仰るとおりパフォーマンス改善しないのではあれば今回のケースでは使えないですね。

[退会済みユーザー]

パフォーマンスの改善の観点からは確かにNG ですねー。
実データに「-」「ー」とか混ざっているんかなあ…

何れにしても、CREATE 文くらいは提示してほしいものだ。

[limit]

ありがとうございました。