试图创建一个查询时大于和小于mysql表内php - trying to create a query when greater and less than inside the mysql table php

trying to create a query when greater and less than inside the mysql table php

看下面的表格。当我有一个值和cat时，我想检查id。如果我有猫a和值3，那么我将得到id 1，但如果我得到猫a和值1，那么返回false，因为值1不大于2。问题是大号在表里面。我无法找到一种方法来做到mysql查询，请帮助。如果需要，我可以修改表的值结构。

table 
id     cat       value
1       a      greater than 2
2       b       less than 2
3       c       less than 2
4       d      greater than 5
$val = 3;
SELECT id FROM my_table WHERE value=$val and cat = a

table 
id     cat       lbound       ubound
1       a         2            2147483647  (Assuming signed int)
2       b         -2147483648  2
3       c         -2147483648  2
4       d         5            2147483647
$cat = 'a';
$val = 3;
SELECT id 
FROM my_table 
WHERE cat = $cat
   AND lbound <= $val -- If you use nulls instead of default extreme values,
   AND ubound >= $val -- then these two bounds comparisons will be more complicated
ORDER BY lbound DESC LIMIT 1 -- if overlapping ranges are possible

在设计方面，我更喜欢默认的"无绑定"值为空，但使用或的表达式，如AND (lbound IS NULL OR lbound <= $val)，或函数，如IFNULL(lbound,-2147483648) <= $val往往不符合索引的使用。