mysql根据身份证号计算出生日期和年龄，并查询筛选

假设计算年龄只考虑年份的话，这样写即可

SELECT
  YEAR(CURDATE()) - SUBSTRING(ID_CARD, 7, 4) AS age,SUBSTRING(ID_CARD, 7, 4) as year
FROM
  users
HAVING
     age between 20 and 30

假如需要考虑月份和年份一起的话，可以这样写：

SELECT
  YEAR(CURDATE()) - SUBSTRING(ID_CARD, 7, 4) - IF(SUBSTRING(ID_CARD, 10, 2) < 10, 0, 1) AS age,ID_CARD
FROM
 users
HAVING
     age between 20 and 30

拓展知识：

根据身份证号生成出生日期

SELECT
  CONCAT(
        SUBSTRING(ID_CARD, 7, 4),
        '-',
        SUBSTRING(ID_CARD, 11, 2),
        '-',
        SUBSTRING(ID_CARD, 13, 2)
    ) AS birthday,ID_CARD
FROM
  users

根据身份证号生成出生日期和年龄

SELECT
  @birthday:=CONCAT(
        SUBSTRING(ID_CARD, 7, 4),
        '-',
        SUBSTRING(ID_CARD, 11, 2),
        '-',
        SUBSTRING(ID_CARD, 13, 2)
    ) as birthday,
    TIMESTAMPDIFF(YEAR, @birthday, CURDATE()) as age,ID_CARD
FROM
  users