在MySQL中是否可以使用BETWEEN范围的无穷大或通配符? [英] Is there an infinity or wild card for use of BETWEEN ranges with MySQL?
问题描述
我有一个程序，例如，我接受一个范围为1到100的MySQL查询的输入.用户可以从网页中选择一个范围，以使查询介于100和(无论无限大)之间.除非必须，否则我不想仅出于无限选择而更改MySQL查询.是否在100和*之间?如果是这样，语法是什么?谢谢！
I have a program where I'm accepting a range of input for a MySQL query of BETWEEN 1 and 100, for example. A user can select a range from the web page to cause a query to be BETWEEN 100 and (whatever infinity) would be. I don't want to change the MySQL query just for an infinity selection unless I have to. Is there a BETWEEN 100 and *? If so, what is the syntax? Thanks!
推荐答案
否，无法在BETWEEN子句中使用通配符.但是您可以为该类型使用最小值/最大值，这将达到相同的效果.
No, there is no way to use wildcards in a BETWEEN clause. But you can use the minimum/maximum possible value for the type and this will achieve the same effect.
例如，如果您的列的类型为BIGINT(带符号)，则可以使用9223372036854775807作为上限，因为这是该数据类型可能的最大值.
For example if you have a column that has type BIGINT (signed) then you can use 9223372036854775807 as the upper limit because this is the largest value possible for that datatype.
WHERE x BETWEEN 100 AND 9223372036854775807
另一种明显的解决方案是，当两端不限时使用>=或<=代替BETWEEN.但是正如您所说，这需要更改查询.
The other obvious solution is to use >= or <= instead of BETWEEN when one of the ends is unlimited. But as you said this requires changing the query.
WHERE x >= 100
还有一些方法可以避免更改查询，方法是使用更复杂的查询，并在您表示无限制时提供NULL.
There are also ways of avoiding changing the query by using a more complex query and providing NULL when you mean unlimited.
WHERE (x >= @lowerbound OR @lowerbound IS NULL)
AND (x <= @upperbound OR @upperbound IS NULL)
这篇关于在MySQL中是否可以使用BETWEEN范围的无穷大或通配符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
