mysql 范围内日期列表,mysql – 将日期列表条件中的日期转换为日期范围列表

我想查找特定日期发生的所有记录.

SELECT *

FROM table1

WHERE date(column) in ($date1, $date2, ...);

但是,正如你们中的许多人所知,这种比较与索引相处并不相符.所以,我想知道是否有一种简单的方法可以将此查询转换为以下查询的样式,而无需太多努力(即：不使用外部工具).

SELECT *

FROM table1

WHERE (column >= $date1 AND column < $date1 + interval 1 day)

OR (column >= $date2 AND column < $date2 + interval 1 day)

...

因此优化器仍然可以使用索引. (我正在使用MySQL,但ANSI SQL会很棒)

解决方法:

建议#1

SELECT A.* FROM table1 A INNER JOIN

(

SELECT '2015-03-01' dtcolumn

UNION SELECT '2015-03-15'

UNION SELECT '2015-04-01'

UNION SELECT '2015-04-15'

UNION SELECT '2015-05-01'

) B ON

A.dtcolumn >= B.dtcolumn

AND A.dtcolumn < B.dtcolumn + INTERVAL 1 DAY;

建议#2

SELECT * FROM table1 WHERE

(column >= '2015-03-01' AND

column < '2015-03-01' + INTERVAL 1 DAY)

UNION

SELECT * FROM table1 WHERE

(column >= '2015-03-15' AND

column < '2015-03-15' + INTERVAL 1 DAY)

UNION

SELECT * FROM table1 WHERE

(column >= '2015-04-01' AND

column < '2015-04-01' + INTERVAL 1 DAY)

UNION

SELECT * FROM table1 WHERE

(column >= '2015-04-15' AND

column < '2015-04-15' + INTERVAL 1 DAY)

UNION

SELECT * FROM table1 WHERE

(column >= '2015-05-01' AND

column < '2015-05-01' + INTERVAL 1 DAY);

标签：query-performance,mysql,date,condition

来源： https://codeday.me/bug/20190805/1591023.html