読者です 読者をやめる 読者になる 読者になる

Database JUNKY

MySQL,MariaDBを中心としたブログです

SQLだけで、メールアドレスの@以前の文字を置き換える

MySQL MariaDB SQL

SQLって奥が深いです。SQLだけで、ほぼデータの加工が行えます。 今回はメールアドレスをマスキングかけるというのを試してみたいと思います

f:id:hit10231023:20160510153145p:plain

考えられるケースとしては、 顧客のメールアドレスをマスクしたいなどの要件かな?と思います。但し!メールの@以降のドメインはそのままでという形で試してみます 例としてこんなテーブルがあったとします

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は、文字列結合です。対象のテーブルを更新するSQLSQLから生成しているのがわかるかと思います。ま、実際はこれだけでは更新することはできないので、すこし手間になってしまうのですが、上記の出力結果を、ファイルに出力し、そのファイルを、再度読み込ませて実行するなんていうことをする必要があります。

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

参考になりましたでしょうか?