MySQL“between”语句不包含边界？

Question

如果我使用 between 子句运行一个查询，它似乎会排除结束值。
例如：

select * from person where dob between '2011-01-01' and '2011-01-31'

这会获取所有从'2011-01-01'到'2011-01-30'的dob结果，并跳过dob为'2011-01-31'的记录。 有人可以解释一下为什么这个查询会以这种方式运作，以及我如何修改它以包括dob为'2011-01-31'的记录？（不要添加1到结束日期，因为它是由用户选择的。）

- ASD

---

不是的。在我的MySQL安装（版本？）中，“BETWEEN”对于两个值都是包含的。我在Windows 10上使用的是“MySQL Server 5.7”。 - Green

Answer 1

来自MySQL手册：

这相当于表达式(min <= expr AND expr <= max)。

（说明：本次翻译旨在将英语翻译成中文，并要求保留HTML标记。）

- Frank Heikens

---

这个回答中链接的手册指出，在比较 DATE 和 DATETIME 对象时，最好使用强制转换。所以我猜 @tiagoinu 的回答在最严格的意义上是最完整的，但两个回答都很准确。 - Kingsolmn

可能是因为答案来自劲敌-postgres的人:P - nawfal

简而言之，“between”是包含的，这就是为什么这个答案很棒。 - Rafael

旧评论，但我想将其与特定查询相关联。 "BETWEEN"是包容性的，但没有指定时间的日期会填充为00:00:00。因此，在日期范围上进行比较将丢失最后一天。要么调用DATE（dob），要么指定当天的结束时间。 - wintermute92

他们说实践是黄金，但从我的使用情况来看，这并不总是适用的。我想知道为什么会发生这种情况。我尝试过，有时它有效，有时则无效。我是在时间数据字段上使用它的。 - Jeffery ThaGintoki

Answer 2

dob字段可能带有时间组件。

要将其截断：

select * from person 
where CAST(dob AS DATE) between '2011-01-01' and '2011-01-31'

- tiago2014

---

你可以使用更简洁的DATE(dob)代替CAST(dob AS DATE)。 - jkndrkn

使用“>=”和“<”代替“between”可以获得更好的性能，尽管目前的方法仍然可行。 - David Harkness

使用“dob BETWEEN '2011-01-01 00:00:00' AND '2011-01-31 23:59:59'”可以获得更好的性能。这是因为“DATE（dob）”需要为每一行计算一个值，无法使用该字段上的任何索引。 - joshuahedlund

@joshuahedlund 请使用这个解决方案添加一个答案。CAST 并不是非常高效的。 - doc_id

这可以运作，直到你有包含时间 t > 23:59:59 and t < 24:00:00 的数据。为什么要处理不明确的 BETWEEN 呢？最好按照 David 的建议使用：WHERE dob >= '2011-01-01' AND dob < '2011-02-01'。这样可以获得最佳性能，并且每次都可以正常工作。 - Disillusioned

Answer 3

问题在于2011-01-31实际上是2011-01-31 00:00:00。也就是说这一天的开始时间是零点。因此在这一天内的所有事件都不包括在内。

- Daniel Hilgarth

---

这真正解释了正在发生的事情并回答了问题。 - Ivan P

这个答案多年来仍然是最好的。非常感谢。 - Strabek

Answer 4

select * from person where dob between '2011-01-01 00:00:00' and '2011-01-31 23:59:59'

- Gaurav

---

Daniel Hilgarth的答案解释了问题，Guarav提供了快速简便的解决方案。 - Geoff Kendall

我认为值得注意的是，这将不包括日期为2011-01-31 23:59:59的数据，但会包括到2011-01-31 23:59:58的数据，即一天中的最后一秒不包括在内。这可能是微小的，但对某些人会有所帮助。 - doc_id

从MySQL文档中，我可以确认最后一秒将被包括在内，因为BETWEEN是双向包容的。请参考http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#operator_between。 - Felype

是的，@Felype，你说得对。我在mysql数据库中自己检查了一下。它也包括23:59:59在结果中。因此，它是双向包容的。 - Lucky

如果“dob”列是带有亚秒精度的时间戳，那么除非使用“2011-02-01 00:00:00”，否则“BETWEEN”仍将错过当天最后一秒内的事件，对吗？ - nitrogen

-1. 不包括 2011-01-31 23:59:59.003。使用 2011-02-01 000:00:00 的氮会错误地包括2月1日的零点……这就是为什么应该使用 >= 和 <。 - Disillusioned

Answer 5

你查询的字段是Date类型还是类型？

你描述的行为常见原因是使用DateTime类型而实际上应该使用Date类型。也就是说，除非你真的需要知道某人出生的时间，否则只需使用Date类型。

导致结果中最后一天未包含在内的原因是查询假定了你在查询中没有指定的日期时间部分。

换句话说：你的查询被解释为2011-01-30和2011-01-31之间午夜之前的数据，但数据可能在2011-01-31当天的其他时间有值。

建议：如果该字段是DateTime类型，请将其更改为Date类型。

- JohnFx

Answer 6

嗨，这个查询对我有效，

select * from person where dob between '2011-01-01' and '2011-01-31 23:59:59'

- infinito84

---

你有日期字段或日期时间字段吗？ - Rinto George

Answer 7

select * from person where DATE(dob) between '2011-01-01' and '2011-01-31'

令人惊讶的是，这种转换在MySQL中是解决许多问题的方法。

- betty.88

---

令人惊讶的是，这正是被接受的答案（以及其他几个答案）在你之前两年就已经说过了。 - Chris Baker

Answer 8

在MySQL中，between运算符的取值是包含边界的，因此当你查询介于 '2011-01-01' 和 '2011-01-31' 之间的数据时，会包括从 2011-01-01 00:00:00 到 2011-01-31 00:00:00 的所有数据，所以实际上并没有包含 '2011-01-31' 这一天的所有时间，因为它的时间范围应该是从 2011-01-31 00:00:00 ~ 2011-01-31 23:59:59。

如果你想要查询到 '2011-01-31' 这一天的数据，可以将查询条件的上限改为 2011-02-01，这样就可以获取到截止到 2011-01-31 23:59:59 的所有数据。

- haneulkim

Answer 9

您可以按如下方式运行查询：

select * from person where dob between '2011-01-01' and '2011-01-31 23:59:59'

正如其他人指出的那样，如果您的日期是硬编码的。

另一方面，如果日期存储在另一个表中，您可以添加一天并减去一秒钟（如果日期没有保存秒/时间），例如：

select * from person JOIN some_table ... where dob between some_table.initial_date and (some_table.final_date + INTERVAL 1 DAY - INTERVAL 1 SECOND)

避免对 dob 字段进行转换（如接受的答案中所示），因为这可能会导致巨大的性能问题（比如无法在 dob 字段上使用索引，假设存在索引）。如果像 DATE(dob) 或 CAST(dob AS DATE) 这样做，执行计划可能会从 using index condition 更改为 using where，因此要小心！

- Lucas Basquerotto

Answer 10

将上限日期设置为日期+1天，因此在您的情况下，将其设置为2011-02-01。

- Rafal

---

这将会错误地包括2月1日的零点时间... 这就是为什么应该忽略 BETWEEN，而应该使用 >= 和 <。 - Disillusioned