Summarize the five methods of calculating age in MySQL and their respective advantages and disadvantages

I didn’t use mysql much before, and I wasn’t very familiar with mysql functions. When I encountered this problem, I immediately searched Baidu and found these two methods. These two methods are ranked first in Baidu’s blogs.

Method 1

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)), '%Y')+0 AS age

Method 1, the author also pointed out the flaw, that is, when the date is a future date, the result is 0, not a negative number; 5 functions and two operations are used here symbol.

Method 2

SELECT DATE_FORMAT(NOW(), &#39;%Y&#39;) - DATE_FORMAT(birthday, &#39;%Y&#39;) - (DATE_FORMAT(NOW(), &#39;00-%m-%d&#39;) < DATE_FORMAT(birthday, &#39;00-%m-%d&#39;)) AS age

Method 2 solves the problem of negative numbers in Method 1, but it looks more complicated; 6 functions and 3 operators are used here.

After reading this post, I was stunned. How could it be so complicated? It was very simple to use Sql Server before. I firmly believe there must be a simple and efficient way. An improved method based on the above method was quickly found.

Improved method one and method two

SELECT year( from_days( datediff( now( ), birthdate)));
SELECT YEAR(CURDATE())-YEAR(birthday)-(RIGHT(CURDATE(),5)<RIGHT(birthday,5));

Improved method one, one less function and one operator is missing. When the date is a future date, the calculation result is still 0;
The improved method two still has 6 functions and 3 operators, which looks simpler; take the five right digits of the date, and when the date format is '2013-01-01', you get '01-01'. No problem; when the date format is '2013-1-1' abbreviated format, the five digits on the right are taken out as '3-1-1', which will cause an error.

Then I thought of the third method based on the date function in the MYSQL help document:

Method 3

SELECT FLOOR(DATEDIFF(CURDATE(), @birthday)/365.2422)

Take the birthday and the number of days before the current date divided by one The actual number of days in the year (365 days, 5 hours, 48 ​​minutes and 46 seconds), then rounded. This only uses three functions and one operator.

Then, I quickly found the fourth method on foreign websites:

Method 4

 SELECT  TIMESTAMPDIFF(YEAR, @birthday, CURDATE())

This method only uses two functions to get it done, it should be The best way.

Tested the above four methods. If the current date is '2017-1-13', and the birthday is '2013-1-14', the birthday is one day away, which is 4 years old. It's only one day away, and the result is still 3 years old, which doesn't feel very reasonable; modify method three and round to get method five:

method5

SELECT ROUND(DATEDIFF(CURDATE(), @birthday)/365.2422)

The age calculated in this way is one year closer to the actual age It's the closest, but maybe method 4 is the most consistent with the definition of age.

【Related recommendations】

1. Free mysql online video tutorial

2. MySQL latest manual tutorial

3. Boolean Education Yan Shiba mysql introductory video tutorial

The above is the detailed content of Summarize the five methods of calculating age in MySQL and their respective advantages and disadvantages. For more information, please follow other related articles on the PHP Chinese website!