Mysql - Date() 似乎在 WHERE 子句中工作异常

Question

我有一个 MySQL表为

| id | user_id | steps_date | steps | target | created_at          |
|----|---------|------------|-------|--------|---------------------|
| 1  | 3       | 2017-01-07 | 3000  | 6000   | 2017-01-07 20:45:33 |
| 2  | 3       | 2017-01-09 | 2545  | 6000   | 2017-01-08 22:15:47 |
| 3  | 3       | 2017-01-10 | 5687  | 6000   | 2017-01-08 21:11:22 |

steps_date列的数据类型为 date和 created_at是timestamp .

而且，我正在触发查询以获取 steps_date >= 2017-01-07 的记录和 steps_date <= 2017-01-09使用查询:

SELECT * FROM user_steps_data
WHERE user_id = 3 
AND DATE(steps_date) >= '2017-01-07' 
AND DATE(steps_date) <= '2017-01-09';

它只返回带有 steps_date=2017-01-09 的记录, 不适用于 steps_date=2017-01-07 .为什么？

Answer 1

使用BETWEEN

SELECT * FROM user_steps_data WHERE user_id = 3 and steps_date between '2017-01-07' AND '2017-01-09';