MySQL Where between
The BETWEEN operator is used in a WHERE expression to select a range of data between two values. BETWEEN together with the use of AND, the syntax is as follows:
WHERE column BETWEEN value1 AND value2 WHERE column NOT BETWEEN value1 AND value2
Normally value1 should be less than value2. When BETWEEN preceded by NOT operator, that with the opposite BETWEEN mean, that is selected outside this range of values.
Select user data with uid between 2 and 5:
SELECT * FROM user WHERE uid BETWEEN 2 AND 5
The results are as follows:
Besides numeric types, BETWEEN also supports string ranges.
For example, selecting all users with a username between a and j (and including the single letter k / K) as follows:
SELECT * FROM user WHERE username BETWEEN 'a' AND 'k'
MySQL BETWEEN The date and time
BETWEEN AND commonly used to retrieve the contents of the time or dateExample
-- int Timestamp format, query 2008-08-08 20:00:00 to 2009-01-01 Data before the zero point SELECT * FROM table WHERE column_time BETWEEN 1218196800 AND 1230739199 -- DATE format, query 2008-08-08 to 2009-01-01 before the zero data SELECT * FROM table WHERE column_time BETWEEN '2008-08-08' AND '2009-01-01' -- DATETIME format, query 2008-08-08 20:00:00 to 2009-01-01 data before the zero point SELECT * FROM table WHERE column_time BETWEEN '2008-08-08 20:00:00' AND '2008-12 -31 23:59:59 '
But for the query to the current time data, it is recommended to use >= operator:
-- DATETIME format, query 2008-08-08 20:00:00 to the current time of the data SELECT * FROM table WHERE column_time >= '2008-08-08 20:00:00'
MySQL BETWEEN data comparison
BETWEEN also has a data comparison function, the syntax is as follows:
Expr BETWEEN min AND max
BETWEEN returns 1 if the value of the expr expression is greater than or equal to min and less than or equal to max, and 0 otherwise. Using this function, you can determine whether an expression or value is in a range:
-- Returns 0 SELECT 1 BETWEEN 2 AND 3 -- Returns 1 SELECT 'b' BETWEEN 'a' AND 'c' -- Determine the date range SELECT 20080808 BETWEEN 20080101 AND 20090101