MySQL BETWEEN Usage

MySQL BETWEEN Syntax

The BETWEEN operator is used in a WHERE expression to select a range of data between two values. BETWEEN is used together with AND, the syntax is as follows:

WHERE column BETWEEN value1 AND value2
WHERE column NOT BETWEEN value1 AND value2

Usually value1 should be less than value2. When BETWEEN is preceded by the NOT operator, it means the opposite of BETWEEN, that is, selecting a value outside this range.

BETWEEN Example

Select user data with uid between 2 and 5:

SELECT * FROM user WHERE uid BETWEEN 2 AND 5

Return query results as follows:

In addition to numeric types, BETWEEN also supports string ranges. The following selects all users whose username is between a and j (and includes the single letter k/K):

SELECT * FROM user WHERE username BETWEEN 'a' AND 'k'

Character ranges are also supported Chinese characters, but generally speaking they don’t make much sense.

MySQL BETWEEN Boundary

Although almost all databases support the BETWEEN...AND operator, different databases handle BETWEEN...AND differently. In MySQL, BETWEEN contains value1 and value2 boundary values, such as the above example of selecting user data with uid between 2 and 5.

Some databases do not contain value1 and value2 boundary values ​​(similar to > and <), and some databases contain value1 but not value2 (similar to >= and <). So when using BETWEEN ... AND, please check how your database handles BETWEEN boundary values.

MySQL BETWEEN time and date

BETWEEN AND is often used to retrieve content within a time or date range. Here are some common BETWEEN time and date examples:

// int 时间戳格式，查询 2008-08-08 20:00:00 到 2009-01-01 零点之前的数据
SELECT * FROM table WHERE column_time BETWEEN 1218196800 AND 1230739199

// DATE 格式，查询 2008-08-08 到 2009-01-01 零点之前的数据
SELECT * FROM table WHERE column_time BETWEEN '2008-08-08' AND '2009-01-01'

// DATETIME 格式，查询 2008-08-08 20:00:00 到 2009-01-01 零点之前的数据
SELECT * FROM table WHERE column_time BETWEEN '2008-08-08 20:00:00' AND '2008-12-31 23:59:59'

But for queries For data at the current time, it is recommended to use the >= operator:

// DATETIME 格式，查询 2008-08-08 20:00:00 到当前时刻的数据
SELECT * FROM table WHERE column_time >= '2008-08-08 20:00:00'

It can be seen that the same requirements and different field types may be written in different ways. In terms of efficiency, the int timestamp format is the most efficient.

Although the above examples of BETWEEN are all SELECT queries, BETWEEN can also be used in SQL that applies WHERE expressions such as UPDATE and DELETE.

MySQL BETWEEN data comparison

BETWEEN also has a data comparison function, the syntax is as follows:

expr BETWEEN min AND max

When the value of the expr expression is greater than or equal to min and less than or equal to max, The return value of BETWEEN is 1, otherwise it returns 0. Using this function, you can determine whether an expression or value is in a certain range:

// 返回 0
SELECT 1 BETWEEN 2 AND 3
// 返回 1
SELECT 'b' BETWEEN 'a' AND 'c'
// 判断日期范围
SELECT 20080808 BETWEEN 20080101 AND 20090101

BETWEEN has similar similarities to operators such as =, > in some cases. function, but BETWEEN has a higher computing level and is even more efficient. Of course, BETWEEN is not flexible enough due to boundary value issues, so different situations require specific treatment of which operator to use.

The above is the content of MySQL BETWEEN usage. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!