Born Too Late

MySQL でメールアドレスのドメインのみを抽出する

以下のような、メールアドレス等の情報を保持したテーブルがあるとします。

mysql> SELECT * FROM users;
+----+---------+------------------+
| id | name    | mail             |
+----+---------+------------------+
|  1 | foo     | foo@yuyat.jp     |
|  2 | bar     | bar@yuyat.jp     |
|  3 | baz     | baz@example.com  |
|  4 | hoge    | hoge@example.org |
|  5 | moge    | moge@example.com |
|  6 | foobar  | foobar@yuyat.jp  |
|  7 | test    | test@example.net |
|  8 | example | example@yuyat.jp |
|  9 | mage    | mage@example.org |
| 10 | huga    | huga@example.com |
| 11 | piyo    | piyo@yuyat.jp    |
| 12 | hige    | hige@yuyat.jp    |
+----+---------+------------------+
12 rows in set (0.00 sec)

ここで、以下のような SQL を用意します。

SELECT
  SUBSTRING_INDEX(mail, '@', -1) AS domain
FROM
  users

すると、このような結果が得られます。

mysql> SELECT SUBSTRING_INDEX(mail, '@', -1) AS domain FROM users;
+-------------+
| domain      |
+-------------+
| yuyat.jp    |
| yuyat.jp    |
| example.com |
| example.org |
| example.com |
| yuyat.jp    |
| example.net |
| yuyat.jp    |
| example.org |
| example.com |
| yuyat.jp    |
| yuyat.jp    |
+-------------+
12 rows in set (0.00 sec)

また、以下の用にグルーピングすることで、ドメイン数順にソートすることも簡単です。

SELECT
  SUBSTRING_INDEX(mail, '@', -1) AS domain,
  COUNT(id) AS count
FROM
  users
GROUP BY
  domain
ORDER BY
  count DESC

mysql> SELECT SUBSTRING_INDEX(mail, '@', -1) AS domain, COUNT(id) AS count FROM users GROUP BY domain ORDER BY count DESC;
+-------------+-------+
| domain      | count |
+-------------+-------+
| yuyat.jp    |     6 |
| example.com |     3 |
| example.org |     2 |
| example.net |     1 |
+-------------+-------+
4 rows in set (0.00 sec)

これらの SQL で重要なのは SUBSTRING_INDEX という関数です。この関数は、ある文字列をデリミタで区切り、その n 番目を取得する というものなのですが、n に負の数を与えることで、末尾の要素を取得しています。