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!
解决方案
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.
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
The limits for integer values are listed here.
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
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)