MySQL 如何从D.O.B字段获取年龄

要从MySQL的D.O.B字段获取年龄，可以使用以下语法。这里，我们从当前日期中减去DOB。

select yourColumnName1,yourColumnName2,........N,year(curdate())-
   year(yourDOBColumnName) as anyVariableName from yourTableName;

要了解上述语法，首先让我们创建一个表。创建表的查询语句如下。

mysql> create table AgeDemo
-> (
-> StudentId int,
-> StudentName varchar(100),
-> StudentDOB date
-> );
Query OK, 0 rows affected (0.61 sec)

使用insert命令向表中插入一些记录。查询语句如下。

mysql> insert into AgeDemo values(1,'John','1998-10-1');
Query OK, 1 row affected (0.20 sec)

mysql> insert into AgeDemo values(2,'Carol','1990-1-2');
Query OK, 1 row affected (0.14 sec)

mysql> insert into AgeDemo values(3,'Sam','2000-12-1');
Query OK, 1 row affected (0.15 sec)

mysql> insert into AgeDemo values(4,'Mike','2010-10-11');
Query OK, 1 row affected (0.18 sec)

使用select语句显示表中的所有记录。查询语句如下。

mysql> select *from AgeDemo;

输出如下：

+-----------+-------------+------------+
| StudentId | StudentName | StudentDOB |
+-----------+-------------+------------+
| 1         | John        | 1998-10-01 |
| 2         | Carol       | 1990-01-02 |
| 3         | Sam         | 2000-12-01 |
| 4         | Mike        | 2010-10-11 |
+-----------+-------------+------------+
4 rows in set (0.00 sec)

下面是计算从D.O.B获取年龄的查询语句。查询语句如下。

mysql> select StudentName,StudentDOB,year(curdate())-year(StudentDOB) as StudentAge from AgeDemo;

输出如下，显示年龄。

+-------------+------------+------------+
| StudentName | StudentDOB | StudentAge |
+-------------+------------+------------+
| John        | 1998-10-01 | 21         |
| Carol       | 1990-01-02 | 29         |
| Sam         | 2000-12-01 | 19         |
| Mike        | 2010-10-11 | 9          |
+-------------+------------+------------+
4 rows in set (0.03 sec)

阅读更多：MySQL 教程