mysql - curdate() 的时间？

Question

我有一个惊人的观察 - 谁能解释一下？

mysql> explain select * from game_instance where start_datetime between STR_TO_DATE(CONCAT(DATE_SUB(CURDATE(), INTERVAL 2 DAY), ' ', '00:00:00'), '%Y-%m-%d %H:%i:%s') and STR_TO_DATE(CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), ' ', '23:59:59'), '%Y-%m-%d %H:%i:%s');
+----+-------------+---------------+-------+------------------------------+------------------------------+---------+------+---------+-------------+
| id | select_type | table         | type  | possible_keys                | key                          | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+-------+------------------------------+------------------------------+---------+------+---------+-------------+
|  1 | SIMPLE      | game_instance | range | game_instance_start_datetime | game_instance_start_datetime | 4       | NULL | 1707666 | Using where |
+----+-------------+---------------+-------+------------------------------+------------------------------+---------+------+---------+-------------+
1 row in set (0.00 sec)

对比:

mysql> explain select * from game_instance where start_datetime between DATE_SUB(CURDATE(), INTERVAL 2 DAY) and DATE_SUB(CURDATE(), INTERVAL 1 DAY);
+----+-------------+---------------+-------+------------------------------+------------------------------+---------+------+--------+-------------+
| id | select_type | table         | type  | possible_keys                | key                          | key_len | ref  | rows   | Extra       |
+----+-------------+---------------+-------+------------------------------+------------------------------+---------+------+--------+-------------+
|  1 | SIMPLE      | game_instance | range | game_instance_start_datetime | game_instance_start_datetime | 4       | NULL | 812538 | Using where |
+----+-------------+---------------+-------+------------------------------+------------------------------+---------+------+--------+-------------+
1 row in set (0.00 sec)

当我查看 WHERE 中使用的两个表达式的输出时，我确实看到了差异，但我认为它们本质上是相同的:

mysql> select STR_TO_DATE(CONCAT(DATE_SUB(CURDATE(), INTERVAL 2 DAY), ' ', '00:00:00'), '%Y-%m-%d %H:%i:%s') d1,DATE_SUB(CURDATE(), INTERVAL 2 DAY) d2;
+---------------------+------------+
| d1                  | d2         |
+---------------------+------------+
| 2019-08-28 00:00:00 | 2019-08-28 |
+---------------------+------------+
1 row in set (0.00 sec)

显然存在显着差异，但差异是 2 倍？

Answer 1

第一个查询实际上是:

BETWEEN 2019-08-28 00:00:00 AND 2019-08-29 23:59:59

并且，第二个查询将是:

BETWEEN 2019-08-28 AND 2019-08-29

start_datetime 是一个datetime 类型的字段。

因此，在第二个查询中，MySQL 会将常量日期文字 2019-08-28 和 2019-08-29 类型转换为日期时间文字，2019 -08-28 00:00:00 和 2019-08-29 00:00:00(2019 年 8 月 29 日开始)，用于比较。

您可以清楚地看到，第二个查询将有效地忽略 2019-08-29 全天(24 小时周期)的数据。因此，这两个查询将分析的行数不同。