在MySQL（InnoDB）中计算年龄

Question

如果我在一个表中以 dd-mm-yyyy 的格式存储了某个人的出生日期，并将其从当前日期中减去，那么返回的日期格式是什么？

我如何使用这个返回的格式来计算某人的年龄？

- user559142

---

这是针对哪个关系型数据库？据我所知，日期函数并没有很好的标准化。而且你说的以“dd-mm-yyyy”格式存储是什么意思？如果它是“date”数据类型，它可能会以数值格式存储（例如作为自某一起始点以来的天数整数）。 - Martin Smith

嗨，我正在使用带有phpMyAdmin的InnoDb...它被存储为日期变量。我认为它是yyyymmdd格式-上面犯了一个错误。 - user559142

如果数据以DATE或DATETIME数据类型存储，则它将作为二进制值存储，而不是以任何特定的人类可读格式存储。格式化仅在输出到文本时发生，并且取决于诸如操作系统区域设置或您提供的显式格式指令等因素。尽可能使用期望DATE和DATETIME类型值的函数处理DATE和DATETIME值。然后您就不会遇到日期格式的问题。您可能仍然需要处理时区，但至少您的计算只会略微出错，而不是完全失效。 - gwideman

Answer 1

你可以使用TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)函数：

SELECT TIMESTAMPDIFF(YEAR, '1970-02-01', CURDATE()) AS age

演示

- Glavić

---

小巧精准且易用的解决方案。 - user2377782

看起来这是明显的胜者。我不知道为什么还有人费心使用将日期转换为字符串等复杂且容易出错的方法。 - gwideman

最佳解决方案，但很难找到，因此在本页面末尾。 - Erik van de Ven

@ErikvandeVen 按照投票数量而不是最早时间对答案进行排序。 - Leandro Bardelli

Answer 2

如果该值以DATETIME数据类型存储：

SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(dob) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(dob, 5)) as age 
  FROM YOUR_TABLE

考虑闰年时，精度较低：

SELECT DATEDIFF(CURRENT_DATE, STR_TO_DATE(t.birthday, '%d-%m-%Y'))/365 AS ageInYears
  FROM YOUR_TABLE t 

- OMG Ponies

---

官方 MySQL 文档中有正确的答案：http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html。 - mojuba

@mojuba：这是一种不同的方式，不一定正确--如果不令人满意，我认为原帖作者不会将其标记为答案。鉴于此，请您考虑撤回您的负评，并且该问题已经存在近两年了。 - OMG Ponies

对不起，@OMG Ponies。假设一年有365天的前提无法正确计算年龄。例如，对于一个40岁的人，在他/她生日后的10天内，你会给出错误的年龄。 - mojuba

@OMG Ponies 不行，不行，还是不行。你公式中的错误在于年龄/4，因为这是你公式中未计算的闰年数量（即额外天数的数量）。请尝试使用以下链接进行验证：http://www.timeanddate.com/date/duration.html - mojuba

谢谢，我一直在寻找这个。请问您能解释一下这部分代码的作用吗？(RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(dob, 5)) - Abhishek Salian

Answer 3

SELECT TIMESTAMPDIFF (YEAR, YOUR_COLUMN, CURDATE()) FROM YOUR_TABLE AS AGE

请查看下面的演示图片：

简单而优雅...

- Ferd Shinoda

Answer 4

使用:

select *,year(curdate())-year(dob) - (right(curdate(),5) < right(dob,5)) as age from your_table

这样做可以考虑出生的月份和日期，以便进行更准确的年龄计算。

- Nicola Cossu

---

哇，这个太棒了，你能解释一下 (right(curdate(),5) < right(dob,5)) 这部分的作用吗？ - user559142

是的。Right（...，5）从当前日期和出生日期中提取mm-dd。你可以用"<"符号将它们与当前日期进行比较，如果出生日期的月份和日子小于当前日期，则返回0，否则返回1。所以你可以从年份差中减去它（0或1）。 - Nicola Cossu

我会进行一个大的修改。在我的MySQL中，使用right(dob,5)返回日期的小时-分钟部分。这可以通过使用year(curdate())-year(dob) - (dayofyear(curdate()) < dayofyear(dob)) as age来修复，我认为dayofyear更加优雅。 - regilero

当然，如果dob是作为datetime存储的，所有这些都会失败。只需使用TIMESTAMPDIFF()。 - gwideman

Answer 5

select floor(datediff (now(), birthday)/365) as age

- lobster1234

---

抱歉..刚刚修好了。他可以根据需求使用floor或ceil。 - lobster1234

是的，如果 OP 想要那种精度，它不会起作用。然而，你需要有 365 个闰年才能使年龄相差 1 年，对吧？ - lobster1234

Answer 6

简单来说：

DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(`birthDate`)), '%Y')+0 AS age

- Damonsson

---

与TIMESTAMPDIFF()相比，这并不简单。 - gwideman

Answer 7

由于这个问题被标记为 mysql ，我有以下的实现方法，对我来说很管用，我希望其他关系型数据库也有类似的替代方案。以下是SQL语句：

select YEAR(now()) - YEAR(dob) - ( DAYOFYEAR(now()) < DAYOFYEAR(dob) ) as age 
from table 
where ...

- mickeymoon

Answer 8

试试这个：

SET @birthday = CAST('1980-05-01' AS DATE);
SET @today = CURRENT_DATE();

SELECT YEAR(@today) - YEAR(@birthday) - 
  (CASE WHEN
    MONTH(@birthday) > MONTH(@today) OR 
    (MONTH(@birthday) = MONTH(@today) AND DAY(@birthday) > DAY(@today)) 
      THEN 1 
      ELSE 0 
  END);

它返回今年-出生年份（人在生日后今年的年龄），并根据这个人今年是否已经过生日进行调整。

它不会受到其他方法所遇到的舍入误差的影响。

^{自此处自由改编}

- Sklivvz

Answer 9

简单地做

SELECT birthdate, (YEAR(CURDATE())-YEAR(birthdate)) AS age FROM `member` 

birthdate是一个字段名，用于保存出生日期。 使用YEAR()命令将CURDATE()转换为年份， 再从出生日期字段中减去该年份。

- Kuzmiraun

---

如果不考虑月份和日期，这个数字可能会与实际年龄相差一年。 - Juan

Answer 10

这是如何在MySQL中计算年龄的方法：

select
  date_format(now(), '%Y') - date_format(date_of_birth, '%Y') - 
  (date_format(now(), '00-%m-%d') < date_format(date_of_birth, '00-%m-%d'))
as age from table

- Mosty Mostacho