SQLだけで、メールアドレスの＠以前の文字を置き換える
SQLって奥が深いです。SQLだけで、ほぼデータの加工が行えます。 今回はメールアドレスをマスキングかけるというのを試してみたいと思います
- 出版社/メーカー: Lenovo
- 発売日: 2015/05/22
- メディア: Personal Computers
- この商品を含むブログを見る
考えられるケースとしては、 顧客のメールアドレスをマスクしたいなどの要件かな？と思います。但し！メールの＠以降のドメインはそのままでという形で試してみます 例としてこんなテーブルがあったとします
CREATE TABLE `mail_table` ( `mail` varchar(100) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8
そして中身はこんな感じです
mysql> select * from mail_table; +----------------------------------------------+ | mail | +----------------------------------------------+ | abcdefgrhtyghjiaaaaaaa@ezweb.ne.jp | | abcdefgrhtyghjiaaaaaaa@docomo.ne.jp | | abcdefgrhtyghjiaaaaaaa@isoftbank.jp | | djaslkjdlakjdklsajdlkasdj@isoftbank.jp | | abcdefgrhcdededtyghjiaaaaaaa@docomo.ne.jp | | abcdefgrhtyghjfrsfsrfsreiaaaaaaa@ezweb.ne.jp | | djaslkjdlakjdklsajdlkasdj@softbank.ne.jp | +----------------------------------------------+ 7 rows in set (0.00 sec)
今回は、このメールアドレスの＠より前の部分をすべて、内緒に置き換えて結果を返すといったサンプルです（SQLのみで） 以下のように、SQLを書きます
select replace(mail,left(mail,instr(mail,'@')- 1),'内緒') from mail_table;
簡単に説明しますと、文字列抽出:leftと、文字列検索:instr を利用して@より前の文字列を、文字列置き換え:replaceで"内緒"に変更するSQLです。
上記SQLの実行結果は以下の通りとなります
mysql> select -> replace(mail,left(mail,instr(mail,'@')- 1),'内緒') -> from -> mail_table; +------------------------------------------------------+ | replace(mail,left(mail,instr(mail,'@')- 1),'内緒') | +------------------------------------------------------+ | 内緒@ezweb.ne.jp | | 内緒@docomo.ne.jp | | 内緒@isoftbank.jp | | 内緒@isoftbank.jp | | 内緒@docomo.ne.jp | | 内緒@ezweb.ne.jp | | 内緒@softbank.ne.jp | +------------------------------------------------------+ 7 rows in set (0.00 sec)
ただし要件としては、表示にマスクをかけるだけでなくそもそもの元データをマスクして置き換えろといった依頼のほうが多いかと思います。 であれば上記の応用で、SQLからSQLを生成してしまえば良いのです。以下は上記を応用した例です。
mysql> select concat('update mail_table set mail = ''',replace(mail,left(mail,instr(mail,'@')- 1),'内緒'),''' where mail =
''',mail,''';') from mail_table;
+--------------------------------------------------------------------------------------------------------------------------------+
| concat('update mail_table set mail = ''',replace(mail,left(mail,instr(mail,'@')- 1),'内緒'),''' where mail = ''',mail,''';') |
+--------------------------------------------------------------------------------------------------------------------------------+
| update mail_table set mail = '内緒@ezweb.ne.jp' where mail = 'abcdefgrhtyghjiaaaaaaa@ezweb.ne.jp'; |
| update mail_table set mail = '内緒@docomo.ne.jp' where mail = 'abcdefgrhtyghjiaaaaaaa@docomo.ne.jp'; |
| update mail_table set mail = '内緒@isoftbank.jp' where mail = 'abcdefgrhtyghjiaaaaaaa@isoftbank.jp'; |
| update mail_table set mail = '内緒@isoftbank.jp' where mail = 'djaslkjdlakjdklsajdlkasdj@isoftbank.jp'; |
| update mail_table set mail = '内緒@docomo.ne.jp' where mail = 'abcdefgrhcdededtyghjiaaaaaaa@docomo.ne.jp'; |
| update mail_table set mail = '内緒@ezweb.ne.jp' where mail = 'abcdefgrhtyghjfrsfsrfsreiaaaaaaa@ezweb.ne.jp'; |
| update mail_table set mail = '内緒@softbank.ne.jp' where mail = 'djaslkjdlakjdklsajdlkasdj@softbank.ne.jp'; |
+--------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.01 sec)
上記の応用で、concatというものが増えました。concatは、文字列結合です。対象のテーブルを更新するSQLをSQLから生成しているのがわかるかと思います。ま、実際はこれだけでは更新することはできないので、すこし手間になってしまうのですが、上記の出力結果を、ファイルに出力し、そのファイルを、再度読み込ませて実行するなんていうことをする必要があります。
echo "select concat('update mail_table set mail = ''',replace(mail,left(mail,instr(mail,'@')- 1),'内緒')),''' where mail = ''',mail,''';') from mail_table;" | mysql -N -h myhost -u myuser mydb -pmypassword > /tmp/mail_table_mail_update.sql
mysql -h myhost -u myuser mydb -pmypassword < /tmp/mail_table_mail_update.sql
参考になりましたでしょうか？
