Unexpected MySQL operator to get more data functionality

If you have recently made a select or update query, you may have used one or more MySQL comparison operators to limit the query's output. Comparisons are an integral part of most select queries, and MySQL provides many functions for this function; According to the most recent statistics, it has more than 20 such operators and functions, from the famous = and like to the more difficult to understand not in and strcmp ().

This article focuses on some of the less common MySQL data comparison functions and operators, discussing how they can be used in the application to compare strings, numbers, and date/time or user-supplied values in a table field.

BETWEEN

The between operator is a useful way to test whether a numeric or date value exists within a range. This operator accepts two parameters-both the maximum and the minimum-and tests whether the supplied value is within the range of the two values. If within this range, the operator returns a Boolean value-true; otherwise, it returns a pseudo value. Here's an example:

The following are the referenced contents: Mysql> SELECT 2350 BETWEEN and 10000; +----------------------------+ | 2350 BETWEEN 10000 | +----------------------------+ | 1 | +----------------------------+ 1 row in Set (0.18 sec)

The following two examples use the date value:

The following are the referenced contents: 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 for a set of numbers or time values. You'll know the effect of these two operators as soon as you see the name--here is an example of using the greatest operator for a set of dates:

The

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:

The following is the referenced content: 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 for testing whether a particular value exists in a predetermined list of options. It can be used on numbers, characters, and time values. It accepts two parameters-a list of values and options to be tested. Here is an example of using a string:

The following are the referenced contents: 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 a number:

The following are the referenced contents: 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 for distinguishing between null and non-null values in a list. A list of supplied values that returns the first Non-null value in the list, or returns a null if no non-null value exists. This is especially handy when you have a column that contains both null and actual content, and you want to quickly "skip" to the first non-null record. Here's an example:

The following are the referenced contents: mysql> SELECT COALESCE (null, ' bat ', A, null); +---------------------------------+ | COALESCE (null, ' bat ', A, NULL) | +---------------------------------+ | Bat | +---------------------------------+ 1 row in Set (0.02 sec)

INTERVAL

The interval operator provides another control over the number and its range. It accepts a list of numbers (N0, N1, N2 ...). , compares the N0 to all remaining digits and returns the index of the position of the smallest number greater than the N0. Digital N1, N2 ... Must be in ascending order.

Take a look at the following example, which compares the number 24 with the numbers in the list (5, 10, 15, 20, 25, 30):

The

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:25 (index position 4) is smaller, but larger than 20 (index position 3), so its interval is 4.

STRCMP

The STRCMP () function is one of the easiest ways to compare strings in MySQL. This function accepts two parameters-the string to be compared. If the two string is the same, it returns 0, and if the first is greater than the second, it returns 1, and if the first is less than the second, it returns-1. The results of the comparison depend entirely on the character set that is currently in use. Here are a few examples:

The following are the referenced contents: 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

For more complex string comparisons, regexp functions are often more powerful than abusive like statements. This function compares an expression to a regular expression pattern, and if it finds a match, it returns a Boolean value-True. Here is an example of this:

The following are the referenced contents: Mysql> SELECT ' The Matrix ' REGEXP ' [u-z]$ '; +------------------------------+ | ' The Matrix ' REGEXP ' [u-z]$ ' | +------------------------------+ | 1 | +------------------------------+ 1 row in Set (0.00 sec)

DATEDIFF

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

The following are the referenced contents: 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 between today's past or any future day. Here's an example:

The following are the referenced contents: Mysql> SELECT DATEDIFF (now (), 20060203); +---------------------------+ | DATEDIFF (now (), 20060203) | +---------------------------+ | 176 | +---------------------------+ 1 row in Set (0.00 sec)

The above is the content of this article. Keep in mind that you can find more information about these functions in the MySQL manual.