Use special MySQL operators to obtain more data comparison functions

Use special MySQL operators to obtain more data comparison functions

If you have recently performed SELECT or UPDATE queries, you may have used one or more MySQL comparison operators to limit the query output results. Comparison is an integral part of most SELECT queries, and MySQL provides many functions for this function. According to the last statistics, it has more than 20 such operators and functions, from the famous = and LIKE to the more difficult not in and STRCMP ().

This article focuses on some less common data comparison functions and operators, discusses how they can be used to compare strings, numbers, dates, times, or user-supplied values in table fields in an application.

BETWEEN

The BETWEEN operator is a useful method to test whether a value or a date value exists in a certain range. This operator accepts two parameters, maximum and minimum, and tests whether the provided values are within the range of these two values. If it is within this range, the operator returns a Boolean Value -- true; otherwise, it returns a pseudo value. The following is an example:

Mysql> SELECT 2350 BETWEEN 100 AND 10000;

+ ---------------------------- +

| 2350 BETWEEN 100 AND 10000 |

+ ---------------------------- +

| 1 |

+ ---------------------------- +

1 row in set (0.18 sec)

The following two examples use the date value:

Mysql> SELECT 20060405 BETWEEN 20060101 AND 20070101;

+ ---------------------------------------- +

| 20060405 BETWEEN 20060101 AND 20070101 |

+ ---------------------------------------- +

| 1 |

+ ---------------------------------------- +

1 row in set (0.00 sec)

Mysql> SELECT 20060405 BETWEEN 20060101 AND 20060401;

+ ---------------------------------------- +

| 20060405 BETWEEN 20060101 AND 20060401 |

+ ---------------------------------------- +

| 0 |

+ ---------------------------------------- +

1 row in set (0.00 sec)

GREATEST and LEAST

The GREATEST and LEAST operators provide a convenient way to determine the maximum and minimum values of a group of numbers or time values. When you look at the names, you will know the functions of these two operators-The following is an example of using the GREATEST operator for a group of dates:

Mysql> select greatest (20000601,200 20000604 );

+ ---------------------------------------- +

| GREATEST (20000601,200 20000604,) |

+ ---------------------------------------- +

| 1, 20000604 |

+ ---------------------------------------- +

1 row in set (0.00 sec)

The following is an example of using the LEAST operator for a set of values:

Mysql> select least (100,200, 50,-6,-73,100 0 );

+ ------------------------------------ +

| LEAST (100,200, 50,-6,-73,100 0) |

+ ------------------------------------ +

|-73 |

+ ------------------------------------ +

1 row in set (0.03 sec)