Is there an infinite or wild map to use BETWEEN ranges with MySQL?

Question

I have a program in which I accept the input range for the MySQL query BETWEEN 1 and 100, for example. The user can select a range from a web page to request a BETWEEN 100 and (regardless of infinity). I do not want to modify the MySQL query just to select infinity, unless you need to. Is there BETWEEN 100 and *? If so, what is the syntax? Thanks!

Answer 1

No, you cannot use wildcards in a BETWEEN clause. But you can use the minimum / maximum possible value for the type, and this will provide the same effect.

For example, if you have a column of type BIGINT (signed), you can use 9223372036854775807 as the upper limit, because this is the highest value possible for this data type.

 WHERE x BETWEEN 100 AND 9223372036854775807 

The limits for integer values ​​are listed here .

---

Another obvious solution is to use >= or <= instead of BETWEEN when one of the ends is unbounded. But, as you said, this requires a request change.

 WHERE x >= 100 

---

There are also ways to avoid modifying the query with a more complex query and providing NULL if you mean unlimited.

 WHERE (x >= @lowerbound OR @lowerbound IS NULL) AND (x <= @upperbound OR @upperbound IS NULL)