在MySQL中使用DATE BETWEEN检索多行

Question

我有下面的表结构。

ID   |   FromDate   |    ToDate
1    |  2020-01-02  |  2020-06-01
2    |  2020-08-01  |  2020-12-01
3    |  2020-01-02  |  2020-11-28
4    |  2020-04-01  |  2020-05-28

当我传递两个名为fromDate和toDate的输入参数时，它应该选择相关的记录。

例如，如果FromDate = 2020-01-01和ToDate = 2020-06-01，它应该返回ID为1和4的记录，因为这两个记录位于所提供参数的日期之间。

我现在正在使用下面的方法，我觉得这浪费了很多资源。

AND ToDate IN (select * from 
(select adddate('2000-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 
union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6                 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2021-01-02 00:00:00' and '2022-03-02 00:00:00') ORDER BY name ASC,lt.ID ASC<code>

还有其他方法可以完成这项任务吗

Answer 1

select * from table_name where fromDate>'2020-xx-xx' AND ToDate<'2020-xx-xx';

该查询格式可以获取插入日期之间的记录