在MySQL中计算年龄(InnoDb) [英] Calculate Age in MySQL (InnoDb)

问题描述

如果我的人的出生日期以dd-mm-yyyy格式存储在表格中，并且从当前日期中减去该日期，那么返回的日期是什么格式?

If I have a persons date of birth stored in a table in the form dd-mm-yyyy and I subtract it from the current date, what format is the date returned in?

如何使用这种返回的格式来计算某人的年龄?

How can I use this returned format to calculate someone's age?

推荐答案

如果该值存储为DATETIME数据类型:

If the value is stored as a DATETIME data type:

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

在考虑leap年时，不够精确:

Less precise when you consider leap years:

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

这篇关于在MySQL中计算年龄(InnoDb)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！