[MySQL]--)查詢5天之內過生日的同事中的閏年2月29日問題的解決過程
前言：
上次寫了查詢5天之內過生日的同事中的跨年問題的解決過程，網址為：http://blog.csdn.net/mchdba/article/details/38952033 ，其中漏了一個閏年2月29日生日的細節問題，現在補充一下這個問題的處理過程：
5，補充閏年判斷
有朋友提醒，閏年2月29日生日的話，可能查詢不到，想到確實沒有考慮到這個特殊的日期。
5.1，准備測試數據SQL，包含1980-02-29這一天生日的朋友。
INSERT INTO ali_users SELECT 'Jeff','1980-02-29','13998786549'
5.2，錄入測試數據
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> INSERT INTO ali_users SELECT \'Jeff\',\'1980-02-29\',\'13998786549\' UNION ALL SELECT \'XiaoTeng\',\'1980-03-01\',\'13998786549\'
-> UNION ALL SELECT \'HeSheng\',\'1980-03-02\',\'13998786549\'
-> UNION ALL SELECT \'JingPan\',\'1980-03-03\',\'13998786549\'
-> UNION ALL SELECT \'WuHong\',\'1986-03-04\',\'13998786549\';
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
5.3，執行原來的舊版本的SQL查詢檢查結果
mysql> SELECT * FROM ali_users WHERE
-> DATEDIFF(CAST(CONCAT(YEAR(\'2014-02-28 00:10:10\'),DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2014-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
-> OR/* or後面的是捎帶解決跨年問題*/
-> DATEDIFF(CAST(CONCAT(YEAR(\'2014-02-28 00:10:10\')+1,DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2014-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
-> ;
+----------+------------+-------------+
| username | birthday | iphone |
+----------+------------+-------------+
| XiaoTeng | 1980-03-01 | 13998786549 |
| HeSheng | 1980-03-02 | 13998786549 |
| JingPan | 1980-03-03 | 13998786549 |
| WuHong | 1986-03-04 | 13998786549 |
+----------+------------+-------------+
4 rows in set, 2 warnings (0.00 sec)
mysql>
5.4，先建立一個存儲函數f_isleap_year判斷當年年份是否是閏年
DELIMITER $$
USE `test`$$
DROP FUNCTION IF EXISTS `f_not_leap_year`$$
CREATE FUNCTION `f_not_leap_year`(p_year BIGINT) RETURNS BOOLEAN
BEGIN
/*是閏年則返回0(false)，不是閏年則返回1(true)*/
DECLARE v_flag INT DEFAULT 0;
/*①、普通年能被4整除且不能被100整除的為閏年。（如2004年就是閏年,1901年不是閏年）*/
IF (p_year%4)=0 AND (p_year%100)>0 THEN
SET v_flag=0;
/*②、世紀年能被400整除的是閏年。(如2000年是閏年，1900年不是閏年) */
ELSEIF (p_year%400)=0 THEN
SET v_flag=0;
/*③、對於數值很大的年份,這年如果能整除3200,並且能整除172800則是閏年。如172800年是閏年，
86400年不是閏年(因為雖然能整除3200，但不能整除172800)（此按一回歸年365天5h48\'45.5\'\'計算）。
*/
ELSEIF (p_year%3200)=0 AND (p_year%172800)=0 THEN
SET v_flag=0;
ELSE
SET v_flag=1;
END IF;
RETURN v_flag;
END$$
DELIMITER ;
存儲函數執行如下圖所示：
5.4.2 准備SQL語句
SELECT * FROM ali_users WHERE
DATEDIFF(CAST(CONCAT(YEAR(NOW()),DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(NOW(),\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
OR/* or後面的是捎帶解決跨年問題*/
DATEDIFF(CAST(CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(NOW(),\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
OR /*補充閏年2月29日的生日問題*/
(
f_not_leap_year(YEAR(NOW()))
AND DATE_FORMAT(birthday,\'-%m-%d\')=\'-02-29\'
AND DATEDIFF(CAST(CONCAT(\'2000\',DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(CONCAT(\'2000\',DATE_FORMAT(NOW(),\'-%m-%d\')) AS DATE))
BETWEEN 0 AND 4
);
5.4.3 在非閏年的時候，驗證閏年2月29日生日，選擇2014年非閏年測試
SELECT * FROM ali_users WHERE
執行SQL檢驗成果，如果當天是2014-02-28，看到已經有2月29日的生日的同事被記錄進來了，其實包含了2月28日、2月29日、3月1日、3月2日、3月3日、3月4日的生日的同事。
PS：因為2月29日在當年不存在，所以不算這5天之內的范疇，執行結果如下所示：
mysql> SELECT * FROM ali_users WHERE
-> DATEDIFF(CAST(CONCAT(YEAR(\'2014-02-28 00:10:10\'),DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2014-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
-> OR/* or後面的是捎帶解決跨年問題*/
-> DATEDIFF(CAST(CONCAT(YEAR(\'2014-02-28 00:10:10\')+1,DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2014-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
-> OR /*補充閏年2月29日的生日方法*/
-> (
-> f_not_leap_year(YEAR(\'2014-02-28 00:10:10\'))
-> AND DATE_FORMAT(birthday,\'-%m-%d\')=\'-02-29\'
-> AND DATEDIFF(CAST(CONCAT(\'2000\',DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(CONCAT(\'2000\',DATE_FORMAT(\'2000-02-28 00:10:10\',\'-%m-%d\')) AS DATE))
-> BETWEEN 0 AND 4
-> );
+----------+------------+-------------+
| username | birthday | iphone |
+----------+------------+-------------+
| Jeff | 1980-02-29 | 13998786549 |
| XiaoTeng | 1980-03-01 | 13998786549 |
| HeSheng | 1980-03-02 | 13998786549 |
| JingPan | 1980-03-03 | 13998786549 |
| WuHong | 1986-03-04 | 13998786549 |
| WeiYa | 1980-02-28 | 13998786549 |
+----------+------------+-------------+
6 rows in set, 2 warnings (0.00 sec)
mysql>
5.4.4 在閏年的時候，驗證閏年2月29日生日，選擇2004年閏年測試
把Step#2中的SQL的NOW()改成'2004-02-28 00:10:10'來進行測試，SQL如下所示：
SELECT * FROM ali_users WHERE
DATEDIFF(CAST(CONCAT(YEAR(\'2004-02-28 00:10:10\'),DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2004-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
OR/* or後面的是捎帶解決跨年問題*/
DATEDIFF(CAST(CONCAT(YEAR(\'2004-02-28 00:10:10\')+1,DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2004-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
OR /*補充閏年2月29日的生日方法*/
(
f_not_leap_year(YEAR(\'2004-02-28 00:10:10\'))
AND DATE_FORMAT(birthday,\'-%m-%d\')=\'-02-29\'
AND DATEDIFF(CAST(CONCAT(\'2000\',DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(CONCAT(\'2000\',DATE_FORMAT(\'2004-02-28 00:10:10\',\'-%m-%d\')) ASDATE))
BETWEEN 0 AND 4
);
mysql> SELECT * FROM ali_users WHERE
-> DATEDIFF(CAST(CONCAT(YEAR(\'2004-02-28 00:10:10\'),DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2004-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
-> OR/* or後面的是捎帶解決跨年問題*/
-> DATEDIFF(CAST(CONCAT(YEAR(\'2004-02-28 00:10:10\')+1,DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2004-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
-> OR /*補充閏年2月29日的生日方法*/
-> (
-> f_not_leap_year(YEAR(\'2004-02-28 00:10:10\'))
-> AND DATE_FORMAT(birthday,\'-%m-%d\')=\'-02-29\'
-> AND DATEDIFF(CAST(CONCAT(\'2000\',DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(CONCAT(\'2000\',DATE_FORMAT(\'2004-02-28 00:10:10\',\'-%m-%d\')) AS DATE))
-> BETWEEN 0 AND 4
-> );
+----------+------------+-------------+
| username | birthday | iphone |
+----------+------------+-------------+
| Jeff | 1980-02-29 | 13998786549 |
| XiaoTeng | 1980-03-01 | 13998786549 |
| HeSheng | 1980-03-02 | 13998786549 |
| JingPan | 1980-03-03 | 13998786549 |
| WeiYa | 1980-02-28 | 13998786549 |
+----------+------------+-------------+
5 rows in set (0.00 sec)
mysql>
