mysql查询还没过生日的人_[MySQL]–)查询5天之内过生日的同事中的跨年问题的解决过程_MySQL...

前言：

遇到朋友提问，如下：

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

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

2,1，跨年问题分析

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

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

查询的结果应该是从今天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整理如下：

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，总结=1