ユーザーの年齢を取得したいという場合に、DB上では誕生日を持っておいて、取得時に計算して年齢を出す、というのが一般的だと思います。
表示で使う際は、プログラム側で関数を作っておいて計算すればいいですが、分析用途やリストの出力のためにSQLでさくっと年齢を出したい！という場合に使える方法をご紹介します。
よりよい方法やバグ等ございましたら、アドバイスいただけると光栄です。
事前準備
年齢計算のクエリを投げるに当たって、テーブル・レコードを作成します。
テーブル
今回は、友達の誕生日一覧のテーブルを作成します。
mysql> DESCRIBE friends;
+----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(100) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+------------------+------+-----+---------+-------+
レコード
mysql> SELECT * FROM friends;
+----+-------+------------+
| id | name | birthday |
+----+-------+------------+
| 1 | Aaron | 1988-10-15 |
| 2 | Aimee | 1990-04-24 |
| 3 | Lily | 1998-08-08 |
| 4 | Bryan | 1995-09-02 |
| 5 | Harry | 1993-05-12 |
| 7 | Jimmy | 1978-04-08 |
| 8 | Robin | 1999-01-11 |
| 9 | Sally | 1991-07-12 |
+----+-------+------------+
年齢を取得
誕生日から年齢を取得するには、TIMESTAMPDIFF() 関数を使用します。
引数は、結果を表現する単位、および差を求める 2 つの日付です。
今回は、「現在の日付」と「誕生日」の差分から、その「年数」を取得できればいいので以下のように使用します。
TIMESTAMPDIFF(YEAR, `birthday`, CURDATE()) AS age
それでは、これを使って先程のfriendsテーブルで、各友達の年齢を取得してみましょう。
mysql> SELECT id, name, birthday,
-> TIMESTAMPDIFF(YEAR, `birthday`, CURDATE()) AS age
-> FROM friends;
+----+-------+------------+------+
| id | name | birthday | age |
+----+-------+------------+------+
| 1 | Aaron | 1988-10-15 | 29 |
| 2 | Aimee | 1990-04-24 | 27 |
| 3 | Lily | 1998-08-08 | 19 |
| 4 | Bryan | 1995-09-02 | 22 |
| 5 | Harry | 1993-05-12 | 24 |
| 7 | Jimmy | 1978-04-08 | 39 |
| 8 | Robin | 1999-01-11 | 19 |
| 9 | Sally | 1991-07-12 | 26 |
+----+-------+------------+------+
結構シンプルに取り出せます。
気になったこと
ただ、これについて調べていると、以下のような取得の仕方が多くみられました。
(YEAR(CURDATE()) - YEAR(birthday)) - (RIGHT(CURDATE(), 5) < RIGHT(birthday, 5))
これは、
「現在の日付と誕生日の年の差分を取得して、さらに誕生日に達していない場合は1を引く」
って感じになっているんですが、なんでこっちの例が多いんでしょう…。
知っている方がいらっしゃれば、教えて頂きたいです。
※追記(2018/1/23)
コメントで参考になる記事を紹介頂いたので、共有させて頂きます。
この記事によれば、生年月日から年齢を計算する簡単な計算式というのがあるそうです。
年齢 = (今日の日付 - 誕生日) / 10000の小数点以下切捨て。
今回のTIMESTAMPDIFF()関数は、まさにこの式に当てはめて作られている関数です。
この計算式を知っていれば、
「現在の日付と誕生日の年の差分を取得して、さらに誕生日に達していない場合は1を引く」
という、面倒な計算をさわさわしなくても問題なさそうです。
※自治体などの法令のしばりなども関係はしてきますが、今回はそれを考えないものとします。