前幾天接到一個需求,要根據使用者的出生日期算出他的年齡.

模擬資料如下,使用了數字輔助表建立資料.
http://blog.itpub.net/29254281/viewspace-1362897/

drop table if exists t;
create table t
(
id int primary key auto_increment,
birthday datetime
);

truncate table t;

insert into t(birthday)
select date_add('2011-12-15',interval id day) from nums limit 10;

insert into t(birthday)
select date_add('2010-2-26',interval id day) from nums limit 10;

insert into t(birthday)
select date_add('2008-2-26',interval id day) from nums limit 10;

今天是2014-12-19 ,t表模擬使用者表,birthday模擬使用者的出生日期

一開始我把問題想簡單了,就想用year(now())-year(birthday)
但是當前日期在生日之前和生日之後,會有1年的誤差.

最後處理的方式如下
select id,birthday,
case when
    date_add(birthday,interval year(now())-year(birthday) year)>=now()
then
    year(now())-year(birthday)-1
else
    year(now())-year(birthday)
end age
from t;

如果需要計算使用者的下次生日日期,參考MySQL技術內幕實現如下
1.先計算當前和出生日期的年份差值
2.出生日期加年份差值,和年份差值+1
3.如果生日是閏年29日,而當前年不是閏年，則判定生日為3月1日

select id,birthday,if(cur>today,cur,next) as target
from
(
select id,birthday,today,
date_add(cur,interval if(day(birthday)=29&&day(cur)=28,1,0) day) as cur,
date_add(next,interval if(day(birthday)=29&&day(next)=28,1,0) day) as next
from
(
select id,birthday,today,
date_add(birthday,interval diff year) as cur,
date_add(birthday,interval diff+1 year) as next
from
(
select id,birthday,(year(now())-year(birthday)) as diff,now() as today from t
) a
) b
) c;

可以製作一個函式

SET GLOBAL log_bin_trust_function_creators = 1;
delimiter $$
create function age(birthday datetime)
returns datetime
begin
return (
select if(cur>today,cur,next) as target
from
(
select birthday,today,
date_add(cur,interval if(day(birthday)=29&&day(cur)=28,1,0) day) as cur,
date_add(next,interval if(day(birthday)=29&&day(next)=28,1,0) day) as next
from
(
select birthday,today,
date_add(birthday,interval diff year) as cur,
date_add(birthday,interval diff+1 year) as next
from
(
select birthday,(year(now())-year(birthday)) as diff,now() as today from dual
) a
) b
) c
);
end$$
delimiter ;

這個函式可以計算下次生日的日期.

MySQL年齡日期問題

相關文章