MySQL Between Like用于日期

Question

以下作品：

SELECT * FROM myTable WHERE date_created BETWEEN '2014-10-05' AND '2015-12-31';

然而，我尝试了以下方法：

SELECT * FROM myTable WHERE date_created BETWEEN '2014-10%' AND '2015-12%';

这不起作用，有没有办法用通配符选择两个日期？

SELECT * FROM myTable WHERE year(date_created) BETWEEN 2014 AND 2015 AND month(date_created) BETWEEN 10 AND 12

我也试过像上面那样做，也不选择范围，只选择2014和2015的所有内容，以及只有月份10和12的所有内容。

有没有一种方法可以让我完成以下工作？

SELECT * FROM myTable WHERE date_created BETWEEN '2014-10%' AND '2015-12%';

Answer 1

使用LAST_DAY函数获取日期月份的最后一天。

WHERE date_created BETWEEN DATE_FORMAT('2014-10-05', '%Y-%m-01') AND LAST_DAY('2015-12-02')

这比在date_created列上使用DATE_FORMAT的解决方案要好，因为它可以利用该列上的索引。

Answer 2

您可以对此使用DATE_FORMAT

SELECT * FROM myTable WHERE DATE_FORMAT(date_created,"%Y-%m") BETWEEN 
DATE_FORMAT('2014-10-05',"%Y-%m") AND DATE_FORMAT('2015-12-31',"%Y-%m") ;

Answer 3

虽然@GoudaElalfy解决方案完成了这项工作，但该查询可能存在性能问题。它不会在列date_created上使用简单的索引。

---

参考您的评论，您在评论中表示，实际上您需要的是一种构建覆盖整个月的查询的方法：

我希望我可以在SQL中获得一系列月份，这样我就不必在java中找到一个月的最后一天。因为这样做：在"2014-10-01"one_answers"2015-12-31"之间，我需要知道上个月的第几天。

您可以计算这些值，并将该列留在WHERE子句中，这样它就可以从上面的简单索引中受益

1. 你知道每个月都有第一天，这将是你扫描索引/表格的起点
2. 您需要计算一个月的最后一天，因为它不是静态的，这将是表/索引扫描过程的终点

一种方法是使用LAST_DAY()函数

SELECT * 
FROM myTable 
WHERE date_created BETWEEN DATE_FORMAT('2014-10-05', '%Y-%m-01') AND LAST_DAY('2015-12-31');

第二种方法是使用一些简单的计算

SELECT * 
FROM myTable 
WHERE date_created BETWEEN DATE_FORMAT('2014-10-05', '%Y-%m-01') AND DATE_FORMAT('2015-12-31' + INTERVAL 1 MONTH, '%Y-%m-01') - INTERVAL 1 DAY;

Answer 4

对不起，在WHERE子句"DATE_FORMAT（DATE_created，"%Y-%m"）"中使用表中的函数或字段不是一个好主意。因此，数据库必须对表中的每一行执行该功能。这将是一次完整的表格扫描。

我已经创建了一个有3000行的测试表。下面是这个查询的执行计划，您可以看到它们读取了所有（3000）行。

MariaDB > EXPLAIN
    -> SELECT * FROM myTable WHERE date_created BETWEEN
    -> DATE_FORMAT('2015-01-05',"%Y-%m") AND DATE_FORMAT('2015-01-31',"%Y-%m") ;
+------+-------------+---------+-------+---------------+--------------+---------+------+------+--------------------------+
| id   | select_type | table   | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |
+------+-------------+---------+-------+---------------+--------------+---------+------+------+--------------------------+
|    1 | SIMPLE      | myTable | index | date_created  | date_created | 4       | NULL | 3000 | Using where; Using index |
+------+-------------+---------+-------+---------------+--------------+---------+------+------+--------------------------+

将开始和结束条件构建为常量会更好，就像这个一样

创建开始日期：

SELECT DATE_FORMAT('2015-01-05','%Y-%m-01');

创建结束日期：

增加一个月，设置在每月的第一天，低于1秒

SELECT DATE_FORMAT(DATE_FORMAT('2015-02-07' + INTERVAL 1 MONTH,'%Y-%m-01') -INTERVAL 1 SECOND,'%y-%m-%d');

完整的查询如下：

SELECT * FROM myTable WHERE date_created
  BETWEEN
    DATE_FORMAT('2015-01-05','%Y-%m-01')
  AND
    DATE_FORMAT(DATE_FORMAT('2015-02-07' + INTERVAL 1 MONTH,'%Y-%m-01')
    -INTERVAL 1 SECOND,'%y-%m-%d');

在执行计划中，你可以看到他们使用索引，只选择177行

+------+-------------+---------+-------+---------------+--------------+---------+------+------+--------------------------+
| id   | select_type | table   | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |
+------+-------------+---------+-------+---------------+--------------+---------+------+------+--------------------------+
|    1 | SIMPLE      | myTable | range | date_created  | date_created | 4       | NULL |  177 | Using where; Using index |
+------+-------------+---------+-------+---------------+--------------+---------+------+------+--------------------------+