MySQL comparison functions and operators

Comparison functions and operators in MySQL:

1, [not] between ...  And ... Check whether a value is within a range of values

Description: Expr between min and Max

Returns 1 if expr is greater than or equal to whether the expression is less than or equal to the maximum and minimum, otherwise 0 is returned. If all parameters are of the same type, this is equivalent to an expression (min<=expr and Expr<=max). Otherwise, the appropriate type conversion is performed.

Mysql> SELECT 2 between 1  and 3,2 between 3  and 1;  - 1,0MySQL> SELECT 1 between 2  and 3;  - 0MySQL> SELECT 'b' between 'a'  and 'C';  - 1MySQL> SELECT 2 between 2  and '3';  - 1MySQL> SELECT 2 between 2  and 'x-3';  - 0

2. COALESCE (value1,...): Returns the first non-null argument

Description: Returns the first non-null value in the list, or null if there is no non-null value.

　　

MySQL>SELECTcoalesce(NULL,1);          - 1 MySQL > SELECT COALESCE (null,null,null);          - NULL

3, Greatest (Value1,value2,...)

Description: Returns the largest parameter in the parameter list

Mysql> SELECTGreatest (2,0);  - 2MySQL> SELECTGreatest (34.0,3.0,5.0,767.0);  - 767.0MySQL> SELECTGreatest ('B','A','C');  - 'C'

4, [not] in ()--check if the value is in the data set

Description: Expr in (value,...); Returns true if expr equals any value in the values list, otherwise false;

If the value list is full of constants, MySQL sorts according to the type of expr and then makes binary lookups, which is very fast.

Mysql> SELECT 2 inch(0,3,5,7);  - 0MySQL> SELECT 'WEFWF' inch('Wee','WEFWF','Weg');  - 1

Try not to mix references and unquoted values in a list, because the rules that compare reference values, such as strings, and unquoted values, such as numbers, are different. Therefore, mixed types can result in inconsistent results. For example, don't write an expression like this:

the wrong wording:SELECTVal1 fromTbl1WHEREVal1inch(1,2,'a');
The correct wording:SELECTVal1 fromTbl1WHEREVal1inch('1','2','a');

5. ISNULL (expr)

Description: If expr is null, returns 1, otherwise 0 is returned;

MySQL>SELECTISNULL(1+1);          - 0 MySQL > SELECT ISNULL (1/0);          - 1

5.1, is null: Test value is null

MySQL>SELECT1isnull0isnullnull  isNULL;          - 0 0 1

5.2.<=>

Description: The operator is a safe null comparison symbol that performs equality comparisons like the = operator, but returns 1 if the two operand is null instead of NULL, if an operand is null and returns 0 instead of NULL.

　　

Mysql> SELECT 1 <=> 1,NULL <=> NULL,1 <=> NULL;  - 1,1,0MySQL> SELECT 1 = 1,NULL = NULL,1 = NULL;  - 1,NULL,NULL

5.3, is Boolean value

Description: The test value is a Boolean value, and the Boolean value can be True,false or unknow

MySQL>SELECT1is0areNULL is UNKNOWN ;          - 1 1 1

6, INTERVAL (N,N1,N2, ...)


Description: N < N1 returns 0,n < N2 returns 1, one analogy. If n is null returns-1. All parameters are treated as integers, requiring parameters to meet N1<n2<...<nn, which allows for quick search of this version

Mysql> SELECTINTERVAL ( at,1, the, -, -, -, $);  - 3MySQL> SELECTINTERVAL (Ten,1,Ten, -, +);  - 2MySQL> SELECTINTERVAL ( A, at, -, -, $);  - 0

7, LEAST (Value1,value2,...)

Description: Returns the minimum value in the Value list.

If all the arguments are null, the result is also null and no comparison is required

If all the arguments are integers, an integer is returned and the integer is compared

If all arguments are real numbers, a real number is returned, and the real number is compared

If the argument is composed of a number and a string, the argument is compared as a number

If all parameters are non-binary strings, it is not a binary character

In other cases, all parameters are compared as binary binary

Mysql> SELECTLEAST (2,0);  - 0MySQL> SELECTLEAST (34.0,3.0,5.0,767.0);  - 3.0MySQL> SELECTLEAST ('B','A','C');  - 'A'MySQL> SelectLeastTen,'a')         - 0

MySQL comparison functions and operators