mysql根据年 月 日进行查询

根据年查询

select * FROM school_file_info WHERE file_type=2 AND video_type=1 AND user_no=64288
AND DATE_FORMAT( gmt_create, '%Y' )= DATE_FORMAT('2020-01-01', '%Y') order by 

根据月查询

select * FROM school_file_info WHERE file_type=2 AND video_type=1 AND user_no=#{userNo,jdbcType=BIGINT}
AND DATE_FORMAT( gmt_create, '%Y%m' )= DATE_FORMAT(#{times}, '%Y%m') order by gmt_create desc  

根据日查询

select * FROM school_file_info WHERE file_type=2 AND video_type=1 AND user_no=#{userNo,jdbcType=BIGINT}
AND DATE_FORMAT( gmt_create, '%Y%m%d' )= DATE_FORMAT(#{times}, '%Y%m%d') order by gmt_create desc  要注意的是这个前台times一定要是完整年月日格式：2020-01-01 这种格式 mysql会根据你定义的格式自动转化 

gmt_create是你数据库时间的字段名