mysql截取年份比较好,以整数形式获取MySQL中两个日期之间的年份差异

I am trying to calculate how old is a person in a database.

Let's suppose to have this simple table:

student(id, birth_date);

Where id is the primary key (truly the table is more complex but I have simplified it).

I want to get how much old is a single person:

select id, datediff(curdate(),birth_date)

from student

But it returns a result in days, and not in years.I could divide it by 365:

select id, datediff(curdate(),birth_date) / 365

from student

But it returns a floating point value, and I want an integer.

So I could calculate the years:

select id, year(curdate())-year(birth_date)

from student

But there is a problem: for instance now is May, if a person war born in June, 1970 he has still 31 years and not 32, but the expression returns 32.

I can't come out of this problem, can someone help me?

解决方案

For anyone who comes across this:

another way this can be done is:

SELECT TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS difference FROM student

For differences in months, replace YEAR with MONTH, and for days replace YEAR with DAY

Hope that helps!