生活随笔
收集整理的這篇文章主要介紹了
[MySQL]--gt;查询5天之内过生日的同事中的闰年2月29日问题的解决过程
小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.
前言：
上次寫了查詢5天之內(nèi)過(guò)生日的同事中的跨年問(wèn)題的解決過(guò)程 ，網(wǎng)址為：http://blog.csdn.net/mchdba/article/details/38952033?，當(dāng)中漏了一個(gè)閏年2月29日生日的細(xì)節(jié)問(wèn)題，如今補(bǔ)充一下這個(gè)問(wèn)題的處理過(guò)程：
5，補(bǔ)充閏年推斷
有朋友提醒，閏年2月29日生日的話，可能查詢不到，想到確實(shí)沒(méi)有考慮到這個(gè)特殊的日期。 5.1，準(zhǔn)備測(cè)試數(shù)據(jù)SQL，包括1980-02-29這一天生日的朋友。 INSERT INTO ali_users ?SELECT 'Jeff','1980-02-29','13998786549'? UNION ALL SELECT 'WeiYa','1980-02-28','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'; 5.2，錄入測(cè)試數(shù)據(jù) 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，運(yùn)行原來(lái)的舊版本號(hào)的SQL查詢檢查結(jié)果 把Step#2中的SQL的NOW()改成'2014-02-28 00:10:10'來(lái)進(jìn)行測(cè)試，確實(shí)沒(méi)有包括2月29日生日的同事，例如以下所看到的： 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后面的是捎帶解決跨年問(wèn)題*/ ? ????-> ?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，先建立一個(gè)存儲(chǔ)函數(shù)f_isleap_year推斷當(dāng)年年份是否是閏年 5.4.1 準(zhǔn)備推斷是否閏年的SQL DELIMITER $$ USE?`test` $ $ DROP ?FUNCTION IF EXISTS?`f_not_leap_year` $ $ CREATE ?FUNCTION?`f_not_leap_year` ( p_year?BIGINT ) ?RETURNS BOOLEANBEGIN /*是閏年則返回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; ?/*②、世紀(jì)年能被400整除的是閏年。(如2000年是閏年，1900年不是閏年) */ ELSEIF?( p_year% 400) = 0?THEN SET ?v_flag= 0; ?/*③、對(duì)于數(shù)值非常大的年份,這年假設(shè)能整除3200,而且能整除172800則是閏年。如172800年是閏年， 86400年不是閏年(由于盡管能整除3200，但不能整除172800)（此按一回歸年365天5h48\'45.5\'\'計(jì)算）。 */ 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?; 存儲(chǔ)函數(shù)運(yùn)行例如以下圖所看到的： ? 5.4.2 準(zhǔn)備SQL語(yǔ)句 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后面的是捎帶解決跨年問(wèn)題*/ ?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 ?/*補(bǔ)充閏年2月29日的生日問(wèn)題*/ ( 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 在非閏年的時(shí)候，驗(yàn)證閏年2月29日生日，選擇2014年非閏年測(cè)試 把Step#2中的SQL的NOW()改成'2014-02-28 00:10:10'來(lái)進(jìn)行測(cè)試，SQL例如以下所看到的： 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后面的是捎帶解決跨年問(wèn)題*/? 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 /*補(bǔ)充閏年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 ); 運(yùn)行SQL檢驗(yàn)成果，假設(shè)當(dāng)天是2014-02-28，看到已經(jīng)有2月29日的生日的同事被記錄進(jìn)來(lái)了，事實(shí)上包括了2月28日、2月29日、3月1日、3月2日、3月3日、3月4日的生日的同事。 PS：由于2月29日在當(dāng)年不存在，所以不算這5天之內(nèi)的范疇，運(yùn)行結(jié)果例如以下所看到的： 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后面的是捎帶解決跨年問(wèn)題*/ ? ????-> ?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 ?/*補(bǔ)充閏年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 在閏年的時(shí)候，驗(yàn)證閏年2月29日生日，選擇2004年閏年測(cè)試 把Step#2中的SQL的NOW()改成'2004-02-28 00:10:10'來(lái)進(jìn)行測(cè)試，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 ?4OR /* or后面的是捎帶解決跨年問(wèn)題*/ ?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 ?4OR ?/*補(bǔ)充閏年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) ; 運(yùn)行SQL檢驗(yàn)成果，假設(shè)當(dāng)天是2004-02-28，看到已經(jīng)有2月29日的生日的同事被記錄進(jìn)來(lái)了，事實(shí)上包括了2月28日、2月29日、3月1日、3月2日、3月3日的生日的同事。 PS：由于2月29日在當(dāng)年存在，所以算這5天之內(nèi)的范疇，運(yùn)行結(jié)果例如以下所看到的： 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后面的是捎帶解決跨年問(wèn)題*/ ? ????-> ?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 ?/*補(bǔ)充閏年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> 總結(jié)
以上是生活随笔 為你收集整理的[MySQL]--gt;查询5天之内过生日的同事中的闰年2月29日问题的解决过程 的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
如果覺(jué)得生活随笔 網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔 推薦給好友。
