mysql生日提醒

如果您的算法取决于该人的出生年份,则显然存在问题.要解决此问题,首先在当前日期之后找到每个人的下一个生日,然后计算该日期与现在之间的差异.

SELECT u_birth, DATEDIFF(next_birthday, NOW()) AS distance FROM (  #计算下次生日距离当前时间相差多少天

SELECT *, ADDDATE(birthday, INTERVAL birthday < DATE(NOW()) YEAR) AS next_birthday  #计算下次生日日期

FROM (

SELECT *, ADDDATE(u_birth, INTERVAL YEAR(NOW()) - YEAR(u_birth) YEAR) AS birthday  #计算今年生日日期

FROM blog_users

WHERE s_agehide = 0

) AS T1

) AS T2

ORDER BY distance ASC

LIMIT 5

结果：

'1992-02-29', 20

'1993-03-01', 21

'1987-05-15', 96

'1988-05-15', 96

'1988-09-18', 222

测试数据：

CREATE TABLE blog_users (u_birth NVARCHAR(100) NOT NULL, s_agehide INT NOT NULL);

INSERT INTO blog_users (u_birth, s_agehide) VALUES

('1987-05-15', 0),

('1988-05-15', 0),

('1988-09-20', 0),

('2000-01-02', 0),

('2000-01-03', 1),

('1988-09-19', 0),

('1988-09-18', 0),

('1992-02-29', 0),

('1993-03-01', 0);

请注意,假设在闰日出生的人在非闰年有2月28日的生日.