还在苦恼MySQL如何根据日期精确计算年龄？看这一篇，就够了！

程序员小助手

发布于 2020-04-08 11:21:20

8.6K0

发布于 2020-04-08 11:21:20

文章被收录于专栏：程序员小助手

转译自 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的可选项如下:

YEAR
QUARTER
MONTH
WEEK
DAY
HOUR
MINUTE
SECOND
MICROSECOND

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

SELECT TIMESTAMPDIFF(YEAR, '2018-01-20', '2019-01-21');

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

修改一下参数:

SELECT TIMESTAMPDIFF(DAY, '2018-01-20', '2019-01-21');

返回的是 366

SQL 计算年龄 —— 组合起来

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

TIMESTAMPDIFF(YEAR, dob, CURDATE())

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

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

SELECT 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 计算天部分：

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

SELECT CONCAT(TIMESTAMPDIFF( YEAR, dob_ field, CURDATE() ),' Years,', TIMESTAMPDIFF( MONTH, dob_ field, CURDATE()) % 12,' Months,',FLOOR( TIMESTAMPDIFF( DAY, dob_ field, CURDATE() ) % 30.4375 ),' Days')

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

举例说明

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

DROP 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。

写入一些随机数据:

INSERT INTO t1(student_name, dob) values ('Jennifer Brit', '1990-02-20');
INSERT INTO t1(student_name, dob) values ('David Dunkrick', '1988-12-20');
INSERT INTO t1(student_name, dob) values ('Michelle Morris', '2001-08-03');
INSERT INTO t1(student_name, dob) values ('Ubaid Ali', '1987-10-19');
INSERT INTO t1(student_name, dob) values ('Juliet Cross', '2019-01-25');

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

UPDATE T1
SET
AGE_FORMATTED =
    CONCAT(
        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 );