Mysql计算年龄

转译自 How To Calculate Age From Date Of Birth In MySQL- Querychat。

中文转载，请注明出处。

使用SQL语句计算年龄，在事务处理和日期计算中，较为常见。MySQL提供了许多日期函数，可以自由发挥。本文中看我们尝试

SQL 年龄计算 —— 组件

MySQL没有开箱即用的工具，用于计算年龄。所以，这也阻挡不了我们求知的热情。工具不够，自己来凑。没有直接的函数，我们就用给的函数，组装出来一个。

说白了，年龄就是啷个日期差。当前日期，减去生日。那就是年龄。

MySQL已经提供的函数，下面介绍一下：

CURDATE() – 返回当前日期TIMESTAMPDIFF() – 计算时间差，差值单位自定义

这俩函数就够了，日期差，获取年差值，月差值，或者其他。

CURDATE() 返回MySQL服务器运行时间。无需传参，调用如下:

CURDATE()

TIMESTAMPDIFF() 调用格式如下：

TIMESTAMPDIFF(unit, begin_date, end_date)

单位unit决定了返回数值。unit的可选项如下:

YEARQUARTERMONTHWEEKDAYHOURMINUTESECONDMICROSECOND

那，我们试着组合起来，写个公式：

ｓｅｌｅｃｔ TIMESTAMPDIFF(YEAR, ‘2018-01-20‘, ‘2019-01-21‘);

上述SQL语句运行后返回 1.

修改一下参数:

ｓｅｌｅｃｔ TIMESTAMPDIFF(DAY, ‘2018-01-20‘, ‘2019-01-21‘);

返回的是 366

SQL 计算年龄 —— 组合起来

学习了两个函数的使用方法，下面计算两个日期相差的年份：

TIMESTAMPDIFF(YEAR, dob, CURDATE())

上式dob字段，在传递给TIMESTAMPDIFF函数之前，需要转化解析为日期格式字符串，才能正常使用。

当前日期是2019年12月20日, 假定生日是1978年8月3日，计算如下:

ｓｅｌｅｃｔ TIMESTAMPDIFF(YEAR, ‘1978-08-03‘, CURDATE());

计算结果 41.

MySQL 计算年龄 —— 提高精度

上面的式子，计算出了正确的年龄。但是如果日期格式不完整，缺少月份，或者日期，都不能计算出来结果。

对应同一个月份，或者同一年的日期，计算差值，年总是=0。因此我们提高精度:

TIMESTAMPDIFF(MONTH, dob_field, CURDATE())

计算两个日期差的月数。

以此类推:

TIMESTAMPDIFF(DAY, dob_field, CURDATE())

计算两个日期差的天数。

举一反三，我们可以相应的计算日期差的月份，和天数。 例如

TIMESTAMPDIFF( MONTH, dob_ field, CURDATE() ) % 12 计算月部分：

TIMESTAMPDIFF( DAY, dob_ field, CURDATE() ) % 30.4375 计算天部分：

组合在一起，就是完整的年龄表达式:

ｓｅｌｅｃｔ ｃｏｎｃａｔ(TIMESTAMPDIFF( YEAR, dob_ field, CURDATE() ),‘ Years,‘, TIMESTAMPDIFF( MONTH, dob_ field, CURDATE()) % 12,‘ Months,‘,FLOOR( TIMESTAMPDIFF( DAY, dob_ field, CURDATE() ) % 30.4375 ),‘ Days‘)

其中dob_ field是包含生日的字段，或变量.

举例说明

创建一张表，字段包含姓名，还有生日:

ｄｒｏｐ TABLE IF EXISTS T1;CREATE TABLE T1(student_name VARCHAR(100), dob date, age_year INT, age_month INT, age_days INT, age_formatted VARCHAR(200));

创建数据库 test_age，然后创建表 t1。

写入一些随机数据:

ｉｎｓｅｒｔ INTO t1(student_name, dob) values (‘Jennifer Brit‘, ‘1990-02-20‘);ｉｎｓｅｒｔ INTO t1(student_name, dob) values (‘David Dunkrick‘, ‘1988-12-20‘);ｉｎｓｅｒｔ INTO t1(student_name, dob) values (‘Michelle Morris‘, ‘2001-08-03‘);ｉｎｓｅｒｔ INTO t1(student_name, dob) values (‘Ubaid Ali‘, ‘1987-10-19‘);ｉｎｓｅｒｔ INTO t1(student_name, dob) values (‘Juliet Cross‘, ‘2019-01-25‘);

下面我们根据生日dob字段，分别计算并更新 age_year, age_month, age_days 和age_formatted。运行以下SQL语句:

ｕｐｄａｔｅ T1SET AGE_FORMATTED =ｃｏｎｃａｔ(TIMESTAMPDIFF( YEAR, DOB, CURDATE()),‘ YEARS,‘, TIMESTAMPDIFF(MONTH, DOB, CURDATE()) % 12,‘ MONTHS,‘,FLOOR( TIMESTAMPDIFF( DAY, DOB,CURDATE()) % 30.4375 ),‘ DAYS‘),AGE_YEAR = TIMESTAMPDIFF( YEAR, DOB, CURDATE()),AGE_MONTH = TIMESTAMPDIFF( MONTH, DOB, CURDATE()) % 12,AGE_DAYS = FLOOR( TIMESTAMPDIFF( DAY, DOB, CURDATE()) % 30.4375);

更新完毕之后，查看结果

ｓｅｌｅｃｔ * FROM T1;

OK，一切结束，拿上SQL语句去数据库上测试一下吧。