mysql通过生日计算年龄

mysql通过生日计算年龄(同理可得通过18位身份证号计算年龄)
– 生日类型是String yyyy-mm-dd 按一年365天计算年龄

SELECT
  birthday,
  FLOOR(PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m%d'), DATE_FORMAT(birthday,'%Y%m%d'))/365)AS age
FROM
  persons;

– 生日类型是Date

SELECT
  birthday,
  TIMESTAMPDIFF(YEAR, birthday, CURDATE())AS age
FROM
  persons;

– 生日类型是String yyyy-mm 按一年12个月计算年龄

SELECT
  m.sex,
    m.card_no,
    m.birth_date,
  FLOOR(PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'), DATE_FORMAT(CONCAT(m.birth_date,'-01'),'%Y%m'))/12)AS age
FROM
  tb_cloud_member m
    LEFTJOIN tb_cloud_laborun l on l.laborun_code=m.laborun_code
  LEFTJOIN tb_cloud_enterprises tce ON tce.id = m.unit_id
        and l.is_deleted=0and tce.is_deleted=0where1=1and m.is_deleted=0and  l.is_laborun =1and m.is_new_occupation=2AND( m.retirement_flag ISNULLOR m.retirement_flag =''OR m.retirement_flag ='0'OR m.retirement_flag ='2')AND( m.job_status ISNULLOR m.job_status notin(4,5,6));