mysql operator

1. Operator precedence

The following list shows the order of operator precedence from low to high. Operators listed on the same line have the same precedence.

:=

||, OR, =, <, <>, !=, IS, LIKE, REGEXP, IN

|

&

<<, >>

-, +

*, /, DIV, %, MOD

^

- (unary minus sign), ~ (unary bit inversion)

!

BINARY, COLLATE

Note: If HIGH_NOT_PRECEDENCE SQL mode is activated, the priority of NOT is the same as the ! operation The symbols are the same.

2. Parentheses

( ... ) use parentheses to specify the order of operations of expressions, for example:

mysql> SELECT 1+2*3;
        -> 7
mysql> SELECT (1+2)*3;
        -> 9

3. Comparison functions and operators

The results of comparison operations are 1 (TRUE), 0 ( FALSE) or NULL. These operations can be used on numbers and strings. Strings are automatically converted to numbers, and numbers are automatically converted to strings, as needed.

Some functions in this chapter (such as LEAST() and GREATEST()) do not include 1 (TRUE), 0 (FALSE) and NULL. However, the resulting value is based on a comparison operation that operates according to the following rules:

MySQL performs numerical comparisons according to the following rules:

If one or both parameters are NULL, unless NULL-safe <=> etc. symbol, the result of the comparison operation is NULL.

If the two parameters in the same comparison operation are both strings, the comparison will be based on the strings.

If both parameters are integers, the comparison will be based on integers.

Hexadecimal values ​​are processed as binary strings when they do not need to be compared as numbers.

If one of the parameters is a TIMESTAMP or DATETIME column, and the other parameters are constants, the constants will be converted to timestamp before comparison. The purpose of this is to make ODBC proceed more smoothly. Note that this does not apply to parameters in IN()! For more reliability, complete datetime/date/time strings are usually used when making comparisons.

In other cases, arguments are compared as floating point numbers.

By default, string comparisons are case-insensitive and use the existing character set (cp1252 Latin1 by default, also suitable for English).

For comparison, you can use the CAST() function to convert a value to another type. Use CONVERT() to convert a string value to a different character set.

The following example illustrates the process of converting strings to numbers in comparison operations:

mysql> SELECT 1 > '6x';

-> 0

mysql> SELECT 7 > '6x';

-> 1

mysql> SELECT 0 > 'x6';

-> 0

mysql> SELECT 0 = 'x6';

-> 1

Note that when listing a string, When comparing a number, MySQL cannot use the index on the column for fast lookup. If str_col is an indexed string column, the index cannot perform a search function in the following statement:

SELECT * FROM tbl_name WHERE str_col=1;

The reason is that many different strings can be converted to Value 1: '1', ' 1', '1a', ...

=

is equal to:

mysql> SELECT 1 = 0;

-> 0

mysql> SELECT '0' = 0; SELECT '0.0' ELECT '.01' = 0.01;

-> 1

<=>

NULL-safe equal. This operator performs the same comparison operation as the = operator, but when both opcodes are NULL, the resulting value is 1 rather than NULL, and when an opcode is NULL, the resulting value is 0 rather than NULL.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;

-> 1, 1, 0

mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;

-> 1, NULL, NULL

<> !=

not equal to:

mysql> SELECT '.01' <> '0.01';

-> ; 1

mysql> SELECT .01 <> '0.01';

-> 0

mysql> SELECT 'zapp' <> 'zappp';

-> 1

<=

Less than or equal to:

mysql> SELECT 0.1 <= 2;

-> 1

<

Less than:

mysql> SELECT 2 < 2;

-> 0

> =

is greater than or equal to:

mysql> SELECT 2 >= 2;

-> 1

>

is greater than:

mysql> SELECT 2 > 2;

-> 0

IS boolean_value IS NOT boolean_value

Test a value based on a Boolean value. Here, the Boolean value can be TRUE, FALSE or UNKNOWN.

mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;

-> 1, 1, 1

mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;

- > 1, 1, 0

IS NULL IS NOT NULL

Tests whether a value is NULL.

mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;

-> 0, 0, 1

mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;

- > 1, 1, 0

In order to work smoothly with ODBC programs, MySQL supports the following additional features when using IS NULL:

After a value is generated, run a statement in the following format immediately to find the latest AUTO_INCREMENT The value of the value: eO SELECT * From TBL_NAME WHERE Auto_Col is Null

When the operation cannot run when SQL_AUTO_IS_NULL = 0.

For DATE and DATETIME columns named NOT NULL, you can find the specific date '0000-00-00' by using the following statement:

o SELECT * FROM tbl_name WHERE date_column IS NULL

Some ODBC is required to run this step application software, because ODBC itself does not support a '0000-00-00' time value.

expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, then the return value of BETWEEN is 1, or 0. If all parameters are of the same type, the above relationship is equivalent to the expression (min <= expr AND expr <= max). Other types of conversions are performed according to the rules described at the beginning of this chapter and are applicable to any of the three parameters.

mysql> SELECT 1 BETWEEN 2 AND 3;

-> 0

mysql> EN 2 AND ' 3';

-> 1

mysql> SELECT 2 BETWEEN 2 AND 'x-3';

-> 0

expr NOT BETWEEN min AND max

This is equivalent to NOT(expr BETWEEN min AND max ).

· COALESCE(value,...)

The return value is the first non-NULL value in the list. If there is no non-NULL value, the return value is NULL.

mysql> SELECT COALESCE(NULL,1);

-> 1

mysql> )

When there are 2 or more parameters, the return value is the largest (maximum value) parameter. The rules for comparing parameters are the same as for LEAST().

mysql> SELECT GREATEST(2,0);

-> 2

mysql> ; SELECT GREATEST('B' ,'A','C');

-> 'C'

When no argument is NULL, the return value of GREATEST() is NULL.

· expr IN (value,...)

If expr is any value in the IN list, the return value is 1, otherwise the return value is 0. If all values ​​are constants, their calculation and classification are based on the type of expr . At this time, binary search is used to search for information. If the IN value list consists entirely of constants, it means that IN is very fast. If expr is a case-sensitive string expression, string comparisons are also performed in a case-sensitive manner.

mysql> SELECT 2 IN (0,3,5,'wefwf');

-> 0

mysql> SELECT 'wefwf' IN (0,3,5,'wefwf');

-> ; 1

IN The number of values ​​listed in the list is only limited by the max_allowed_packet value.

In order to be consistent with the SQL standard, when the expression on the left is NULL, or when no match is found in the table, or when an expression in the table is NULL, the return value of IN is NULL. The

IN() construct can also be used to write certain types of subqueries.

· expr NOT IN (value,...)

This is the same as NOT (expr IN (value,...)).

ISNULL(expr)

If expr is NULL, then the return value of ISNULL() is 1, otherwise the return value is 0.

mysql> SELECT ISNULL(1+1);

-> 0

mysql> SELECT ISNULL(1/0); The

ISNULL() function has some of the same characteristics as the IS NULL comparison operator. See the note on IS NULL .

· INTERVAL(N,N1,N2,N3,...)

If N < N1, the return value is 0; if N < N2 and so on, the return value is 1; if N is NULL, The return value is -1. All parameters are treated as integers. For the correct operation of this function, N1 < N2 < N3 < ...< Nn must be satisfied. The reason for this is the use of binary search (extremely fast).

mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);

-> 3

mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);

- > ; 2

mysql> SELECT INTERVAL(22, 23, 30, 44, 200);

-> 0

· LEAST(value1,value2,...)

When there are two or more parameters In this case, the return value is the minimum (minimum value) argument. Use the following rules to compare independent variables:

If the return value is used in an INTEGER context, or all parameters are integer values, then they are compared as integer values.

If the return value is used in a REAL context, or all parameters are real-valued, then it is compared as a real-valued value.

If any parameter is a case-sensitive string, the parameters will be compared according to the case-sensitive string.

In other cases, arguments are compared as case-sensitive strings.

If any independent variable is NULL, the return value of LEAST() is NULL.

mysql> SELECT LEAST(2,0);

-> 0

mysql> SELECT LEAST(34.0,3.0,5.0,767.0);

-> 3.0

mysql> SELECT LEAST('B' ,'A','C');

-> 'A'

Note that the above conversion rules will produce some strange results in some edge cases:

mysql> SELECT CAST(LEAST(3600, 9223372036854775808.0 ) as SIGNED);

-> -9223372036854775808

This happens because MySQL reads 9223372036854775808.0 in an integer context. Integer representation is not conducive to preserving numeric values, so it includes a signed integer.

4. Logical operators

In SQL, the evaluation results of all logical operators are TRUE, FALSE or NULL (UNKNOWN). In MySQL, they are represented as 1 (TRUE), 0 (FALSE), and NULL. Most of them are common to different database SQLs, however some servers may return any non-zero value for TRUE.

NOT !

logical NOT. When the operand is 0, the returned value is 1; when the operand is non-zero, the returned value is 0, and when the operand is NOT NULL, the returned value is NULL.

mysql> SELECT NOT 10;

-> 0

mysql> SELECT NOT 0;

-> 1

mysql> SELECT NOT NULL;

->

mysql> SELECT ! (1 +1);

-> 0

mysql> SELECT ! 1+1;

-> 1

The last example produces a result of 1 because the expression is evaluated as (!1)+ 1 is the same.

AND &&

Logical AND. When all operands are non-zero and not NULL, the calculated result is 1. When one or more operands are 0, the result is 0. In other cases, the return value is NULL.

mysql> SELECT 1 && 1;

-> 1

mysql> SELECT 1 && 0;

-> 0

mysql> SELECT 1 && NULL;

-&g t; NULL

mysql> SELECT 0 && NULL;

-> 0

mysql> SELECT NULL && 0;

-> 0

OR ||

Logical OR. When both operands are non-NULL, if either operand is non-zero, the result is 1, otherwise the result is 0. When one operand is NULL, if the other operand is non-zero, the result is 1, otherwise the result is NULL. If both operands are NULL, the result is NULL.

mysql> SELECT 1 || 1;

-> 1

mysql> SELECT 1 || 0;

-> 1

mysql> SELECT 0 || t; 0

mysql> SELECT 0 || NULL;

-> NULL

mysql> SELECT 1 || NULL;

-> 1

XOR

Logical XOR. When any operand is NULL, the return value is NULL. For non-NULL operands, if an odd operand has a non-zero value, the calculated result is 1, otherwise it is 0.

mysql> SELECT 1 XOR 1;

-> 0

mysql> SELECT 1 t; NULL

mysql> SELECT 1 XOR 1 XOR 1;

-> 1

a The calculation for