MySQL -- 查询5天之内过生日的同事中的跨年问题的解决过程

最新推荐文章于 2022-11-25 14:33:11 发布
最新推荐文章于 2022-11-25 14:33:11 发布
阅读量1w 收藏 1
点赞数
分类专栏： mysql problem
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/csdnhsh/article/details/93376765
版权

前言：
        遇到朋友提问，如下：
SELECT * FROM ali_users WHERE  DATEDIFF(CAST(CONCAT(DATE_FORMAT(NOW(),'%y'),DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) <=1
上面的可以找到今天或者明天过生日的
假设今天是12月31日，这时候来年的1月1日的就找不到了，怎么办？

1，准备测试数据，需要包含跨年的数据

1.1，准备测试数据的SQL

 

USE test;

DROP TABLE IF EXISTS ali_users;

CREATE TABLE ali_users (username VARCHAR(10),birthday DATE NOT NULL,iphone VARCHAR(16));

INSERT INTO ali_users SELECT \'MaoYi\',\'1985-09-04\',\'13998786543\' UNION ALL

SELECT \'LiuEr\',\'1985-08-30\',\'13998786543\' UNION ALL

SELECT \'ZhangSan\',\'1981-01-01\',\'13998786543\' UNION ALL

SELECT \'LiSi\',\'1983-01-02\',\'13998786543\' UNION ALL

SELECT \'WangWu\',\'1984-11-01\',\'13998786543\' UNION ALL

SELECT \'ZhaoLiu\',\'1984-11-01\',\'13998786543\' UNION ALL

SELECT \'SongQi\',\'1986-08-31\',\'13998786543\' UNION ALL

SELECT \'HuangBa\',\'1989-09-01\',\'13998786543\' UNION ALL

SELECT \'ZengJiu\',\'1989-09-02\',\'13998786543\' UNION ALL

SELECT \'LuoShi\',\'1985-09-03\',\'13998786543\' UNION ALL

SELECT \'Tom\',\'1995-09-05\',\'13998786543\' UNION ALL

SELECT \'Licy\',\'1991-12-30\',\'13998286543\' UNION ALL

SELECT \'Cari\',\'1992-12-31\',\'13998286543\' UNION ALL

SELECT \'Mark\',\'1992-01-03\',\'13998286543\' UNION ALL

SELECT \'Ruby\',\'1992-01-04\',\'13998286547\';


1.2，在数据库命令行执行SQL

 

mysql> USE test;

DATABASE CHANGED

mysql> DROP TABLE IF EXISTS ali_users;

QUERY OK, 0 ROWS affected (0.00 sec)

 

mysql> CREATE TABLE ali_users (username VARCHAR(10),birthday DATE NOT NULL,iphone VARCHAR(16));

QUERY OK, 0 ROWS affected (0.01 sec)

 

mysql> INSERT INTO ali_users SELECT \'MaoYi\',\'1985-09-04\',\'13998786543\' UNION ALL

    -> SELECT \'LiuEr\',\'1985-08-30\',\'13998786543\' UNION ALL

    -> SELECT \'ZhangSan\',\'1981-01-01\',\'13998786543\' UNION ALL

    -> SELECT \'LiSi\',\'1983-01-02\',\'13998786543\' UNION ALL

    -> SELECT \'WangWu\',\'1984-11-01\',\'13998786543\' UNION ALL

    -> SELECT \'ZhaoLiu\',\'1984-11-01\',\'13998786543\' UNION ALL

    -> SELECT \'SongQi\',\'1986-08-31\',\'13998786543\' UNION ALL

    -> SELECT \'HuangBa\',\'1989-09-01\',\'13998786543\' UNION ALL

    -> SELECT \'ZengJiu\',\'1989-09-02\',\'13998786543\' UNION ALL

    -> SELECT \'LuoShi\',\'1985-09-03\',\'13998786543\' UNION ALL

    -> SELECT \'Tom\',\'1995-09-05\',\'13998786543\' UNION ALL

    -> SELECT \'Licy\',\'1991-12-30\',\'13998286543\' UNION ALL

    -> SELECT \'Cari\',\'1992-12-31\',\'13998286543\' UNION ALL

    -> SELECT \'Mark\',\'1992-01-03\',\'13998286543\' UNION ALL

    -> SELECT \'Ruby\',\'1992-01-04\',\'13998286547\';

QUERY OK, 15 ROWS affected (0.01 sec)

Records: 15 Duplicates: 0 WARNINGS: 0

 

mysql> SELECT * FROM ali_users;

+----------+------------+-------------+

| username | birthday | iphone |

+----------+------------+-------------+

| MaoYi | 1985-09-04 | 13998786543 |

| LiuEr | 1985-08-30 | 13998786543 |

| ZhangSan | 1981-01-01 | 13998786543 |

| LiSi | 1983-01-02 | 13998786543 |

| WangWu | 1984-11-01 | 13998786543 |

| ZhaoLiu | 1984-11-01 | 13998786543 |

| SongQi | 1986-08-31 | 13998786543 |

| HuangBa | 1989-09-01 | 13998786543 |

| ZengJiu | 1989-09-02 | 13998786543 |

| LuoShi | 1985-09-03 | 13998786543 |

| Tom | 1995-09-05 | 13998786543 |

| Licy | 1991-12-30 | 13998286543 |

| Cari | 1992-12-31 | 13998286543 |

| Mark | 1992-01-03 | 13998286543 |

| Ruby | 1992-01-04 | 13998286547 |

+----------+------------+-------------+

15 ROWS IN SET (0.00 sec)

 

mysql>


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;

2,1，跨年问题分析
因为跨年的时候生日字段通常月份比较小是1月，所以如果利用DATEDIFF来判断要与月份比较大12月来比较得到相差天数在N天之内的话，就需要YEAR(NOW())+1，当年年份+1再加上月份才能与NOW()比较得出真实的相差天数。

打个比方，今天是2013年12月30日，要查询出来5天之内的过生日的同事，那么2014年1月1日2日3日的也在查询范围之内，这个时候就需要CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,'-%m-%d')年份加1形成2014-01-01才能与今天的2013-12-30进行比较得出正确的相差天数。

2.2，5天之内的设定
        N天之内，用 BETWEEN 0 AND N 来判断，如果是5天之内(包含今天)那么N值就是4，就是 BETWEEN 0 AND 4

3，验证数据
比如提醒最近5天之内(包括今日)过生日的同事，生日快乐。

3.1，查询的数据都在今年之内的，比如今天是8月30日，那么需要执行的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;

查询的结果应该是从今天8月30日到9月3日之间过生日的同事，包括LiuEr,SongQi,HuangBa,ZengJiu,LuoShi;
执行如下所示，符合预期，如下所示：

 

mysql> 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

    -> ;

+----------+------------+-------------+

| username | birthday | iphone |

+----------+------------+-------------+

| LiuEr | 1985-08-30 | 13998786543 |

| SongQi | 1986-08-31 | 13998786543 |

| HuangBa | 1989-09-01 | 13998786543 |

| ZengJiu | 1989-09-02 | 13998786543 |

| LuoShi | 1985-09-03 | 13998786543 |

+----------+------------+-------------+

5 ROWS IN SET (0.00 sec)

 

mysql>


3.2，查询的生日有跨年的
        比如今天是2013年12月30日，要查询5天之内过生日的同事，那么就有2013年的12月30日31日过生日的，也有2014年1月1日2日3日过生日的同事，因为今天是8月30日，所以要把Step#2中的SQL的NOW()改成'2013-12-30 00:10:10'来进行测试，SQL整理如下：
SELECT * FROM ali_users WHERE 
DATEDIFF(CAST(CONCAT(YEAR('2013-12-30 00:10:10'),DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT('2013-12-30 00:10:10','%y-%m-%d') AS DATE)) BETWEEN 0 AND 4
OR/* or后面的是捎带解决跨年问题*/ 
DATEDIFF(CAST(CONCAT(YEAR('2013-12-30 00:10:10')+1,DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT('2013-12-30 00:10:10','%y-%m-%d') AS DATE)) BETWEEN 0 AND 4;

查询的结果应该是从今天8月30日到9月3日之间过生日的同事，包括Licy,Cari,ZhangSan,LiSi,Mark。
执行结果如下所示，符合预期，如下所示：

 

mysql> SELECT * FROM ali_users WHERE 

    -> DATEDIFF(CAST(CONCAT(YEAR(\'2013-12-30 00:10:10\'),DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2013-12-30 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4

    -> OR/* or后面的是捎带解决跨年问题*/ 

    -> DATEDIFF(CAST(CONCAT(YEAR(\'2013-12-30 00:10:10\')+1,DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2013-12-30 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4

    -> ;

+----------+------------+-------------+

| username | birthday | iphone |

+----------+------------+-------------+

| ZhangSan | 1981-01-01 | 13998786543 |

| LiSi | 1983-01-02 | 13998786543 |

| Licy | 1991-12-30 | 13998286543 |

| Cari | 1992-12-31 | 13998286543 |

| Mark | 1992-01-03 | 13998286543 |

+----------+------------+-------------+

5 ROWS IN SET (0.00 sec)

 

mysql>


4，总结
    这种类似的场景，涉及到查询多少天之内的数据，以及涉及到跨度范围比较大的查询(比如跨年或者跨部门，跨省、跨国、跨公司等等)，都可以用这个思路来需求解决问题的方法。
    
    

林震南
关注
专栏目录
Linux环境下MySQL-python安装过程分享
09-10
在Linux环境中，MySQL-python是Python的一个重要库，用于连接MySQL数据库。这个库使得Python程序员能够方便地与MySQL服务器进行交互，执行SQL查询，处理数据库事务等。以下是对Linux环境下安装MySQL-python过程的...
MySQL查询7天内过生日的用户 查询生日是今天的用户 解决跨年问题
guochanof的专栏
07-03 4243
MySQL查询生日是今天的用户 select * from customer where MONTH(birthday) = MONTH(NOW()) and DAY(birthday) = DAY(NOW()) 查询7天内生日的用户 select * from customer where DATE_FORMAT(birthday,'%m-%d') >= DATE_FORMA...
MySql查询生日的两种方式
weixin_30640291的博客
12-27 594
需要是要查询日期段内过生日的会员,分为两种情况: 1. 不跨年 例如: 查询2017-01-01到2017-01-20之间过生日的会员 (假定今天是2017-01-01则这种也可以描述为20天内过生日的会员) select * from user where DATE_FORMAT(birthday,'%m-%d') between '01-01' and '01-20' ...
Mysql SQL查询今天、昨天、n天内、第n天的数据
weixin_51933432的博客
12-31 507
查询5分钟前的数据select * from table where end_date between date_add(now(), interval - 300 SECOND) and NOW()查询当天的所有数据 SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())=0 查询昨天的所有数据 SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())=-1 查询未来第n天的所有数据 //当n为负数时，表示过去第n天的数据 SELECT * F..
mysql实现生日倒计时
一个喜欢诗和远方的程序媛
11-21 869
首先获取今年和来年的生日日期，今年生日过了则计算下一年生日距离今天多少天；mysql默认没有29号时取28号。EG:客户表customer_info 查询生日倒计时N天内生日提醒。eg：当前日期是2022-11-21。
关于Mysql中的生日提醒
qq_41331645的博客
12-26 980
Mysql中的生日提醒功能在有的项目中或许会使用到，那么我最近在写项目中就遇到此类的问题 ，在网上寻遍以后找到一个相对合理的解决办法，那就是使用Mysql的datediff函数对日期进行加减，下面我把sql语句公布出来： SELECT ccu.custom_name as CustomName, ccu.id as customId, ccu.gender as sex, DATE_FORMAT(...
mysql-connector-java-8.0.22.jar
08-14
包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-8.0.22.jar包含mysql-connector-java-...
mysql-8.0.28-winx64.zip + mysql80-community-release-el7-5.noarch
03-25
在本案例中，我们有两个不同平台的安装包：一个是针对Windows操作系统的“mysql-8.0.28-winx64.zip”，另一个是用于Linux（特别是基于Red Hat Enterprise Linux 7）的“mysql80-community-release-el7-5.noarch”。...
mysql-connector-java-8.0.17.zip
08-15
java操作MySQL数据库所必需的一个连接器，下载完毕后将mysql-connector-java-8.0.17.jar导入到项目中即可，并且add as library，就可以使用jar包实现与MySQL的连接
mysql连接包mysql-connector-java-5.1.27.jar
05-31
MySQL是世界上最流行的开源关系型数据库管理系统之一，而`mysql-connector-java`是MySQL官方提供的用于Java应用程序连接到MySQL服务器的驱动程序。`mysql-connector-java-5.1.27.jar`是这个驱动的一个特定版本，它...
mysql 查询本周、上周、本月
08-29
mysql 查询本周、上周、本月、上个月份的数据 mysql 查询本周、上周、本月、上个月份的数据 mysql 查询本周、上周、本月、上个月份的数据
sql server 生日提醒
11-27
sql server 数据库 生日提醒 整体sql语句，num就是相差的天数，假设num=9 select * from Table1 where DateDiff("d",{ fn NOW() },dateadd("yyyy",DateDiff("yyyy",Birth,{ fn NOW() }),Birth))between 0 and 9
Sql Server判断生日是否跨年
村里的程序员
05-09 1876
ALTER function CheckBirthday(@mybirthday datetime,@nowdatetime datetime,@daycount int) returns intasbegindeclare @i  int,@j  int, @tempdate datetime set @i=0  --i=0表示生日跨年set @j=0  --j=0表示当前日前跨年 
生日提醒--还有多久生日（mysql）
有一棵枣树在庭前
01-09 3278
考虑两点： 1. 年底时，倒计时 2. 闰年2月29日出生的 针对以上，首先获取今年和来年的生日日期，今年生日过了则计算下一年生日距离今天多少天；闰年2月29生日的，设在非闰年时2月28日过生日，恰巧，mysql默认没有29号时取28号。 SELECT datediff( IF (cur >= today, cur, next),-- 今年的生日是否已过
mysql 查询大于多少天的数据
热门推荐
Hello World专栏
06-20 1万+
SELECT * FROM 表名 WHERE DATE_ADD(createdate,INTERVAL 7 DAY) 上面语句查询7天之前的数据
mysql 生日提醒
daoshud1的专栏
10-15 229
[code="java"] select datediff(DATE_ADD("1989-10-02",INTERVAL ceil(TIMESTAMPDIFF(MONTH,"1989-10-02",NOW())/12) YEAR),NOW()) [/code] 计算出 生日的日期 与现在的日期，之间的月份差，除12向上取整...
mysql计算两个日期之间的工作日（可跨年，排除周六日，不排除节日）
最新发布
chunHeng2400的博客
11-25 1843
mysql计算两个日期之间的工作日（可跨年，排除周六日，不排除节日）
mysql 查询5天内过生日的会员
qq369389714的专栏
10-17 1843
SELECT count(*) as c FROM `mycms_member_card` left join mycms_member on mycms_member_card.member_id = mycms_member.id left join mycms_member_card_type on mycms_member_card.type_id = mycms_member_card_
解决MySQL中文显示与插入问题
确保客户端与服务器之间的通信也使用了正确的字符集，否则中文数据可能会在传输过程中出现问题。 6. **系统环境变量**： - 在某些情况下，系统的环境变量（如`LC_ALL`、`LANG`）也可能影响到MySQL对中文字符的处理...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值