MYSQL expression operator and data type conversion _ MySQL

MYSQL expression operators and data type conversion tips: expression operators, in MySQL expressions, if the type of a data value does not match the type required by the context, mySQL automatically converts data values based on the operations to be performed.

1. expression operators
Table 1 arithmetic operator

Operator syntax
+ A + B Addition
-A-B subtraction
--A: negative.
* A * B multiplication
/A/B division
% A % B for remainder

Table 2. logical operators

Operator syntax
AND or & a AND B or a & B logic AND, if the two operands are both true, then true
OR | a OR B OR a | B logic OR, as long as one operand is true, it is true
XOR a XOR B logic exclusive or, if there is only one operand is true, it is true
Not or! NOT a OR! A logic is not. if the operand is false, it is true.

Table 3. bitwise operators

Operator syntax
& A & B is bitwise and. if the operand is the same as 1, the bitwise is 1.
| A | B is bitwise OR. if one of the operands is 1, this bitwise is 1.
^ A ^ B returns an exclusive or. if the same bit of the operand is 1 and 0, this bit is 1.
<A <B shifts the bits of a to the left of B
> A> B shifts the bits in a to the right of B.

Table 4. Comparison operators

Operator syntax
= A = B. If the two operands are equal, the return value is true.
<=> A <=> B: true if the two operands are equal. it can be used for NULL value comparison.
! = Or <>! = B or a <> B. If the number of two operations is not the same, it is true.
<A <B if a is less than B, true
<= A <= B if a is less than or equal to B, it is true.
> A> B: true if a is greater than B
> = A> B: true if a is greater than or equal to B
IN a IN (b1, b2,...) If a is equal to one of b1, b2,..., it is true.
BETWEEN a BETWEEN B AND c if a is BETWEEN B AND c (including B AND c), it is true.
Not between a not between B AND c if a is not between B AND c (including B AND c), true
LIKE a LIKE B SQL Mode match. if a matches B, it is true.
Not like a not like B SQL Mode match. if a does NOT match B, it is true.
REGEXP a REGEXP B regular expression match. if a matches B, it is true.
Not regexp a not regexp B regular expression match. if a does NOT match B, it is true.
Is null a is null if a is null, it IS true
Is not null a is not null if a is not null, true

In the LIKE pattern match, "%" matches any character, and "_" matches one character. Match case-insensitive characters.

Table 5. operator priority (sorted from high to low)

Operator
BINARY, COLLATE
NOT ,!
^
XOR
-(Unary evaluate negative operator ),~ (One-dollar anti-operator)
*,/, %
+ ,-
<,>
&
|
<, <=, =, <=> ,! =, <>, >=,>, IN, IS, LIKE, REGEXP, RLIKE
BETWEEN, CASE, WHEN, THEN, ELSE
AND ,&&
OR, |
: =

2. type conversion
In MySQL expressions, if the type of a data value does not match the type required by the context, MySQL automatically converts the data value type based on the operation to be performed. For example:

1 + '2' is converted to 1 + 2 = 3
1 + 'ABC' is converted to 1 + 0 = 1 because abc cannot be converted to any value, the default value is 0.
MySQL automatically converts strings and values to date and time values according to the expression context requirements.

MySQL also converts out-of-range or invalid values, but the conversion result is incorrect. In this case, MySQL will prompt a warning message, which can be captured for processing.