在 MySQL 中根据出生日期计算年龄?
mysqlmysqli database
要在 MySQL 中根据出生日期计算年龄，您可以使用以下语法 −
SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(yourColumnName) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(yourColumnName, 5)) as anyVariableName from yourTableName;
为了理解上述概念，让我们创建一个表。以下是创建表的查询。
mysql> create table AgeCalculatesDemo −> ( −> YourDateOfBirth datetime −> ); Query OK, 0 rows affected (0.50 sec)
使用 insert 命令在表中插入一些记录。这些记录是我们将用来计算年龄的出生日期。以下是查询 −
mysql> insert into AgeCalculatesDemo values(date_add(now(),interval 22 year));
Query OK, 1 row affected (0.23 sec)
mysql> truncate table AgeCalculatesDemo;
Query OK, 0 rows affected (0.89 sec)
mysql> insert into AgeCalculatesDemo values(date_add(now(),interval -22 year));
Query OK, 1 row affected (0.16 sec)
mysql> insert into AgeCalculatesDemo values(date_add(now(),interval -12 year));
Query OK, 1 row affected (0.18 sec)
mysql> insert into AgeCalculatesDemo values(date_add(now(),interval -19 year));
Query OK, 1 row affected (0.15 sec)
mysql> insert into AgeCalculatesDemo values('2010-4-05');
Query OK, 1 row affected (0.25 sec)
现在您可以借助 select 语句显示所有记录。以下是查询 −
mysql> select *from AgeCalculatesDemo;
以下是输出 −
+---------------------+ | YourDateOfBirth | +---------------------+ | 1996-12-06 23:35:26 | | 2006-12-06 23:35:32 | | 1999-12-06 23:35:42 | | 2010-04-05 00:00:00 | +---------------------+ 4 rows in set (0.00 sec)
以下是可用于计算年龄的查询。
mysql> SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(YourDateOfBirth) −> - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(YourDateOfBirth, 5)) as YourAge −> from AgeCalculatesDemo;
以下是显示年龄的输出 −
+---------+ | YourAge | +---------+ | 22 | | 12 | | 19 | | 8 | +---------+ 4 rows in set (0.00 sec)
