How does the SQL paging query data be sorted by date of birth from near to far?

A user data sheet exists user_tableThere's a birthdayfield holds the user's date of birth (e.g. 1994-01-01、1996-07-1) The corresponding timestamp data

Now you need to sort by birthdays from near to far to find user data
For example today 2016-07-28 , birthdays are dated
1995-07-30, 1993-11-31 , 1997-08-29 , 1995-07-27
After four user data is sorted
1995-07-30, 1997-08-29 , 1993-11-31 ,1995-07-27

How can SQL statements be written to satisfy such query criteria?
Or should I change the structure of my birthday data table to fit?

Reply content:

user_tableThere is a field in the User data table birthday 1994-01-01、1996-07-1 that holds the timestamp data for the user's date of birth (e.g.)

Now you need to sort by birthdays from near to far to find user data
For example today 2016-07-28 , birthdays are dated
1995-07-30, 1993-11-31 , 1997-08-29 , 1995-07-27
After four user data is sorted
1995-07-30, 1997-08-29 , 1993-11-31 ,1995-07-27

How can SQL statements be written to satisfy such query criteria?
Or should I change the structure of my birthday data table to fit?

Simple practices that do not consider data volume and performance issues:

1. Add a field, save dayofyear information, such as the field name is called Day_of_year, then you insert the statement is like this:

insert into user_table set ... = ..., day_of_year = DAYOFYEAR('1995-07-30');

1. Query the time query two times, first, the Query day_of_year field is larger than your current value, and then query this field is less than the current value, sorted by Day_of_year:

select * from user_table where day_of_year >= DAYOFYEAR(CURDATE()) order by day_of_year asc;select * from user_table where day_of_year < DAYOFYEAR(CURDATE()) order by day_of_year asc;

Look at one less condition before
MySQL put the time of 1995-07-30, first converted into the day of the Year DayOfYear (' 1995-07-30 '), minus today is the day of the year, get a difference and then order by a bit on the line.

If the birthday is the same as the month, you can increment the order by year.

select * from user_table order by (DAYOFYEAR(birthday)-DAYOFYEAR(curdate())),year(birthday) ASC

Idea: You this is actually first by subtracting after the month row, after the row and then by subtracting after the day row

The above code can achieve 1 months after the current month of birthday data and according to the ascending order of the day, you write a repeat to take the back out of the good

DROP TEMPORARY TABLE IF EXISTS aa;DROP TEMPORARY TABLE IF EXISTS bb;create temporary table aa(select birthday from user_table WHERE substring(birthday,6,10)>'07-28' ORDER BY substring(birthday,6,10) asc);create temporary table bb(select birthday from user_table WHERE substring(birthday,6,10)<'07-28' ORDER BY substring(birthday,6,10) asc );SELECT * FROM aa Union All SELECT * FROM bb测试了一下，效果

1995-07-30
1997-08-29
1993-11-31
1999-01-01
1995-07-27

