[MySQL]--)查询5天之内过生日的同事中的闰年2月29日问题的解决过程

首頁

資料庫

mysql教程

[MySQL]--)查询5天之内过生日的同事中的闰年2月29日问题的解决过程_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:02 PM

過程閏年

前言：
上次写了查询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>

陳述

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

MySQL的許可與其他數據庫系統相比如何？Apr 25, 2025 am 12:26 AM

MySQL使用的是GPL許可證。 1）GPL許可證允許自由使用、修改和分發MySQL，但修改後的分發需遵循GPL。 2）商業許可證可避免公開修改，適合需要保密的商業應用。

您什麼時候選擇InnoDB而不是Myisam，反之亦然？Apr 25, 2025 am 12:22 AM

選擇InnoDB而不是MyISAM的情況包括：1)需要事務支持，2)高並發環境，3)需要高數據一致性；反之，選擇MyISAM的情況包括：1)主要是讀操作，2)不需要事務支持。 InnoDB適合需要高數據一致性和事務處理的應用，如電商平台，而MyISAM適合讀密集型且無需事務的應用，如博客系統。

在MySQL中解釋外鍵的目的。Apr 25, 2025 am 12:17 AM

在MySQL中，外鍵的作用是建立表與表之間的關係，確保數據的一致性和完整性。外鍵通過引用完整性檢查和級聯操作維護數據的有效性，使用時需注意性能優化和避免常見錯誤。

MySQL中有哪些不同類型的索引？Apr 25, 2025 am 12:12 AM

MySQL中有四種主要的索引類型：B-Tree索引、哈希索引、全文索引和空間索引。 1.B-Tree索引適用於範圍查詢、排序和分組，適合在employees表的name列上創建。 2.哈希索引適用於等值查詢，適合在MEMORY存儲引擎的hash_table表的id列上創建。 3.全文索引用於文本搜索，適合在articles表的content列上創建。 4.空間索引用於地理空間查詢，適合在locations表的geom列上創建。

您如何在MySQL中創建索引？Apr 25, 2025 am 12:06 AM

toCreateAnIndexinMysql，usethecReateIndexStatement.1）forasingLecolumn，使用“ createIndexIdx_lastNameEnemployees（lastName）; 2）foracompositeIndex，使用“ createIndexIndexIndexIndexIndexDx_nameOmplayees（lastName，firstName，firstName）;” 3）forauniqe instex，creationexexexexex，

MySQL與Sqlite有何不同？Apr 24, 2025 am 12:12 AM

MySQL和SQLite的主要區別在於設計理念和使用場景：1.MySQL適用於大型應用和企業級解決方案，支持高性能和高並發；2.SQLite適合移動應用和桌面軟件，輕量級且易於嵌入。

MySQL中的索引是什麼？它們如何提高性能？Apr 24, 2025 am 12:09 AM

MySQL中的索引是數據庫表中一列或多列的有序結構，用於加速數據檢索。 1）索引通過減少掃描數據量提升查詢速度。 2）B-Tree索引利用平衡樹結構，適合範圍查詢和排序。 3）創建索引使用CREATEINDEX語句，如CREATEINDEXidx_customer_idONorders(customer_id)。 4）複合索引可優化多列查詢，如CREATEINDEXidx_customer_orderONorders(customer_id,order_date)。 5）使用EXPLAIN分析查詢計劃，避