Unexpected MySQL operators can get more data functions _ MySQL

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 famous and LIKE to more

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 MySQL 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, 20000529, 20000604);+----------------------------------------+| GREATEST(20000601, 20000529, 20000604) |+----------------------------------------+| 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, 1000);+------------------------------------+| LEAST(100, 200, 50, -6, -73, 1000) |+------------------------------------+| -73 |+------------------------------------+1 row in set (0.03 sec)

IN

IN is a useful operator used to test whether a specific value exists IN the pre-specified option list. It can be used in numbers, characters, and time values. It accepts two parameters-the list of values and options to be tested. The following is an example of using a string:

mysql> SELECT 'c' IN ('a', 'b', 'c', 'd');+-----------------------------+| 'c' IN ('a', 'b', 'c', 'd') |+-----------------------------+| 1 |+-----------------------------+1 row in set (0.02 sec)

The following example uses numbers:

mysql> SELECT 1 IN (3,4,5);+--------------+| 1 IN (3,4,5) |+--------------+| 0 |+--------------+1 row in set (0.00 sec)

COALESCE

The COALESCE operator is useful in distinguishing between NULL and non-NULL values in the list. Using the provided value list, it will return the first non-NULL value in the list, or, if there is no non-NULL value, it will return a NULL. This is especially convenient when you have a column that contains both NULL and actual content, and you want to quickly "jump" to the first non-NULL record. The following is an example:

mysql> SELECT COALESCE(NULL, 'bat', 12, NULL);+---------------------------------+| COALESCE(NULL, 'bat', 12, NULL) |+---------------------------------+| bat |+---------------------------------+1 row in set (0.02 sec)

INTERVAL

The INTERVAL operator provides another control over numbers and their ranges. It accepts a list of numbers (N0, N1, N2. ..), compares N0 with all the remaining numbers, and returns the index of the location of the smallest number greater than N0. Numbers N1, N2. .. must be in ascending order.

Let's take a look at the example below. it compares the number 24 with the number (5, 10, 15, 20, 25, 30) in the list:

mysql> SELECT INTERVAL (24, 5, 10, 15, 20, 25, 30);+--------------------------------------+| INTERVAL (24, 5, 10, 15, 20, 25, 30) |+--------------------------------------+| 4 |+--------------------------------------+1 row in set (0.07 sec)

In this example, 24 is smaller than 25 (index location 4), but larger than 20 (index location 3), so its INTERVAL is 4.

STRCMP

STRCMP () is one of the simplest ways to compare strings in MySQL. This function accepts two parameters-the string to be compared. If the two strings are the same, it returns 0; if the first string is greater than the second string, it returns 1; if the first string is smaller than the second string, it returns-1. The comparison result is completely dependent on the character set currently in use. The following are examples:

mysql> SELECT STRCMP('hell', 'hell');+------------------------+| STRCMP('hell', 'hell') |+------------------------+| 0 |+------------------------+1 row in set (0.00 sec)mysql> SELECT STRCMP('bell', 'hell');+------------------------+| STRCMP('bell', 'hell') |+------------------------+| -1 |+------------------------+1 row in set (0.00 sec)

REGEXP

To perform more complex string comparisons, REGEXP functions are often more powerful than LIKE statements. This function compares an expression with a regular expression pattern. if a match is found, it returns a boolean value-true. The following is an example:

mysql> SELECT 'The Matrix' REGEXP '[u-z]

DATEDIFF

As its name indicates, the DATEDIFF () function is a tool to quickly obtain the number of days between two dates. This is useful when calculating the number of days when an event occurs, or when a person's birthday is given, calculating his/her age.

mysql> SELECT DATEDIFF(20060101, 20051201);+------------------------------+| DATEDIFF(20060101, 20051201) |+------------------------------+| 31 |+------------------------------+1 row in set (0.00 sec)

DATEDIFF () is usually used with the NOW () function-for example, to calculate the number of days from today to the past or any day in the future. The following is an example:

mysql> SELECT DATEDIFF(NOW(), 20060203);+---------------------------+| DATEDIFF(NOW(), 20060203) |+---------------------------+| 176 |+---------------------------+1 row in set (0.00 sec)

The above is the content of this article. Remember that you can find it in the MySQL manual