前言：
遇到朋友提问，如下：
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，准备测试数据的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 allselect \'liuer\',\'1985-08-30\',\'13998786543\' union allselect \'zhangsan\',\'1981-01-01\',\'13998786543\' union allselect \'lisi\',\'1983-01-02\',\'13998786543\' union allselect \'wangwu\',\'1984-11-01\',\'13998786543\' union allselect \'zhaoliu\',\'1984-11-01\',\'13998786543\' union allselect \'songqi\',\'1986-08-31\',\'13998786543\' union allselect \'huangba\',\'1989-09-01\',\'13998786543\' union allselect \'zengjiu\',\'1989-09-02\',\'13998786543\' union allselect \'luoshi\',\'1985-09-03\',\'13998786543\' union allselect \'tom\',\'1995-09-05\',\'13998786543\' union allselect \'licy\',\'1991-12-30\',\'13998286543\' union allselect \'cari\',\'1992-12-31\',\'13998286543\' union allselect \'mark\',\'1992-01-03\',\'13998286543\' union allselect \'ruby\',\'1992-01-04\',\'13998286547\';1.2，在数据库命令行执行sql
mysql> use test;database changedmysql> 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: 0mysql> 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，写出查询sqlselect * from ali_users where2,1，跨年问题分析因为跨年的时候生日字段通常月份比较小是1月，所以如果利用datediff来判断要与月份比较大12月来比较得到相差天数在n天之内的话，就需要year(now())+1，当年年份+1再加上月份才能与now()比较得出真实的相差天数。2.2，5天之内的设定n天之内，用 between 0 and n 来判断，如果是5天之内(包含今天)那么n值就是4，就是 between 0 and 43，验证数据比如提醒最近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，总结