Is there an infinity or wild card for use of BETWEEN ranges with MySQL?

Question

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!

Answer 1

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)

Answer 2

i would suggest using this when lower == null then it will be replaced by 0 and when upper is null then it will be replaced by a max bigint

SELECT * FROM `mytable` WHERE 50 between ifnull(lower,0) and ifnull(upper,~0)