MySQL 计算年龄

根据出生日期计算年龄

 SELECT DATEDIFF(YEAR,birthday,GETDATE()) as age
 FROM user

报错如下

1582 - Incorrect parameter count in the call to native function 'DATEDIFF'

原因 MySQL DATEDIFF函数只支持两个参数修改为

 SELECT DATEDIFF(birthday,GETDATE()) as age
 FROM user

结果报错

1305 - FUNCTION tendon7.GETDATE does not exist

原因 MySQL 没有 GETDATE 函数 

将 GETDATE() 修改为 NOW()

 SELECT DATEDIFF(birthday,NOW()) as age
 FROM user

输出结果为负数 这是时间差值 以秒为单位的  调换参数位置即可 

但是为了 计算年龄 更换函数

SELECT TIMESTAMPDIFF(YEAR,birthday,NOW()) as age
FROM user

附另一种方法：

SELECT 
	IF(DATEDIFF(CURRENT_DATE,CONCAT(DATE_FORMAT(CURRENT_DATE,"%Y"),"-",DATE_FORMAT(birthday,"%m-%d")))>=0,
	DATE_FORMAT(CURRENT_DATE,"%Y") - DATE_FORMAT(birthday,"%Y"),
	DATE_FORMAT(CURRENT_DATE,"%Y") - DATE_FORMAT(birthday,"%Y")-1) as age
FROM user