Mysql INTERVAL
From w3cyberlearnings
Contents |
MySQL INTERVAL Function
This function uses a binary search to check a list of number (very fast). It returns 0 if N < N1, 1 if N < N2, 2 if N < N3 and so on, and it returns -1 if N is NULL.
Syntax INTERVAL
- N: First value
- N1: Second Value
- N2: Third Value
- N3: Four Value
INTERVAL(N, N1, N2, N3);
Note
In order for this function to work correctly, it expects N1 < N2 < N3 ...
Example 1
mysql> SELECT INTERVAL(30,50,14,44); +-----------------------+ | INTERVAL(30,50,14,44) | +-----------------------+ | 0 | +-----------------------+ 1 row in set (0.02 sec) mysql> SELECT INTERVAL(30,0,14,44); +----------------------+ | INTERVAL(30,0,14,44) | +----------------------+ | 2 | +----------------------+ 1 row in set (0.00 sec)
Example 2
mysql> SELECT INTERVAL(30,2,14,4,40); +------------------------+ | INTERVAL(30,2,14,4,40) | +------------------------+ | 3 | +------------------------+ 1 row in set (0.00 sec)
Example 3
mysql> SELECT * from item;
+---------+--------+-------+
| item_id | item | price |
+---------+--------+-------+
| 1 | CD | 5 |
| 2 | PHONE | 45 |
| 3 | Laptop | 880 |
+---------+--------+-------+
3 rows in set (0.00 sec)
mysql> SELECT * from item
-> WHERE item_id=INTERVAL(115,54,880);
+---------+------+-------+
| item_id | item | price |
+---------+------+-------+
| 1 | CD | 5 |
+---------+------+-------+
1 row in set (0.00 sec)
