MySQL“之间”子句不包括在内？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5080824/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL "between" clause not inclusive?
提问by ASD
If I run a query with a betweenclause, it seems to exclude the ending value.
For example:
如果我使用between子句运行查询，它似乎排除了结束值。
例如：
select * from person where dob between '2011-01-01' and '2011-01-31'
This gets all results with dobfrom '2011-01-01' till '2011-01-30'; skipping records where dobis '2011-01-31'. Can anyone explain why this query behaves this way, and how I could modify it to include records where dobis '2011-01-31'? (without adding 1 to the ending date because its been selected by the users.)
这将获得dob从 '2011-01-01' 到 '2011-01-30' 的所有结果；跳过dob“2011-01-31”的记录。谁能解释为什么这个查询会这样，以及我如何修改它以包含dob'2011-01-31'的记录？（没有在结束日期上加 1，因为它是由用户选择的。）
回答by Frank Heikens
From the MySQL-manual:
从MySQL 手册：
This is equivalent to the expression (min <= expr AND expr <= max)
这相当于表达式 (min <= expr AND expr <= max)
回答by tiago2014
The field dobprobably has a time component.
该字段dob可能具有时间分量。
To truncate it out:
截断它：
select * from person
where CAST(dob AS DATE) between '2011-01-01' and '2011-01-31'
回答by Daniel Hilgarth
The problem is that 2011-01-31 really is 2011-01-31 00:00:00. That is the beginning of the day. Everything during the day is not included.
问题是 2011-01-31 真的是 2011-01-31 00:00:00。那是一天的开始。白天的一切都不包括在内。
回答by Gaurav
select * from person where dob between '2011-01-01 00:00:00' and '2011-01-31 23:59:59'
回答by JohnFx
Is the field you are referencing in your query a Datetype or a DateTimetype?
您在查询中引用的字段是Date类型还是DateTime类型？
A common cause of the behavior you describe is when you use a DateTime type where you really should be using a Date type. That is, unless you really need to know what time someone was born, just use the Date type.
您描述的行为的一个常见原因是当您使用 DateTime 类型时，您确实应该使用 Date 类型。也就是说，除非您真的需要知道某人的出生时间，否则只需使用 Date 类型。
The reason the final day is not being included in your results is the way that the query is assuming the time portion of the dates that you did not specify in your query.
结果中未包含最后一天的原因是查询假设您未在查询中指定的日期的时间部分。
That is: Your query is being interpreted as up to Midnight between 2011-01-30 and 2011-01-31, but the data may have a value sometime later in the day on 2011-01-31.
也就是说：您的查询被解释为 2011-01-30 和 2011-01-31 之间的午夜，但数据可能在 2011-01-31 当天晚些时候的某个时间具有值。
Suggestion: Change the field to the Date type if it is a DateTime type.
建议：如果是DateTime类型，则将该字段更改为Date类型。
回答by infinito84
Hi this query works for me,
嗨，这个查询对我有用，
select * from person where dob between '2011-01-01' and '2011-01-31 23:59:59'
回答by betty.88
select * from person where DATE(dob) between '2011-01-01' and '2011-01-31'
Surprisingly such conversions are solutions to many problems in MySQL.
令人惊讶的是，这种转换是 MySQL 中许多问题的解决方案。
回答by Rafal
Set the upper date to date + 1 day, so in your case, set it to 2011-02-01.
将上限日期设置为 date + 1 天，因此在您的情况下，将其设置为 2011-02-01。
回答by Lucas Basquerotto
You can run the query as:
您可以将查询运行为：
select * from person where dob between '2011-01-01' and '2011-01-31 23:59:59'
like others pointed out, if your dates are hardcoded.
就像其他人指出的那样，如果您的日期是硬编码的。
On the other hand, if the date is in another table, you can add a day and subtract a second (if the dates are saved without the second/time), like:
另一方面，如果日期在另一个表中，您可以添加一天并减去一秒（如果保存的日期没有秒/时间），例如：
select * from person JOIN some_table ... where dob between some_table.initial_date and (some_table.final_date + INTERVAL 1 DAY - INTERVAL 1 SECOND)
Avoid doing casts on the dobfiels (like in the accepted answer), because that can cause huge performance problems (like not being able to use an index in the dobfield, assuming there is one). The execution plan may change from using index conditionto using whereif you make something like DATE(dob)or CAST(dob AS DATE), so be careful!
避免对dob字段进行强制转换（如在已接受的答案中），因为这会导致巨大的性能问题（例如无法在dob字段中使用索引，假设有索引）。执行计划可能会改变从using index condition到using where，如果你做的东西像DATE(dob)或者CAST(dob AS DATE)，所以一定要小心！
回答by Ambleu
In MySql between the values are inclusive therefore when you give try to get between '2011-01-01' and '2011-01-31'
在 MySql 中，值之间是包含的，因此当您尝试在 '2011-01-01' 和 '2011-01-31' 之间获取时
it will include from 2011-01-01 00:00:00upto 2011-01-31 00:00:00therefore nothing actually in 2011-01-31 since its time should go from 2011-01-31 00:00:00 ~ 2011-01-31 23:59:59
它将包括 from 2011-01-01 00:00:00up 2011-01-31 00:00:00to 因此实际上在 2011-01-31 中没有任何内容，因为它的时间应该从2011-01-31 00:00:00 ~ 2011-01-31 23:59:59
For the upper bound you can change to 2011-02-01then it will get all data upto 2011-01-31 23:59:59
对于上限，您可以更改为，2011-02-01然后它将获得所有数据2011-01-31 23:59:59
