Summary of MySQL Common Type Conversion Functions (Recommended)

1. Concat function.

Connection strings are commonly used: concat function. Such as like query for sql query criteria, AND c. name like concat (# {param. name}, '%')

Converting Int to varchar often uses the concat function, such as concat (8, '0') to get the string '80'

2. Cast function; CONVERT function.

Usage: CAST (expr AS type), CONVERT (expr, type), CONVERT (expr USING transcoding_name).

SELECT CONVERT('abc' USING utf8);

Convert varchar to Int Use cast (str as unsigned) str as a string of type varchar.

For example, the commonly used percentage conversion:

select cast((1/3)*100 as UNSIGNED) as percent from dual;

result: 33

MySQL type conversion function parameters: CAST (xxx AS type), CONVERT (xxx type)
This type can be one of the following values:
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

Integer: SIGNED
Unsigned integer: UNSIGNED
Binary, with binary prefix effect: BINARY
Character type with parameter: CHAR ()
Date: DATE
Time: TIME
Date time type: DATETIME
Floating point number: DECIMAL

mysql> SELECT BINARY 'a' = 'A';

    -> 0

3. IF function

In mysql, if is a function rather than a command

IF(expr1,expr2,expr3)
If expr1 is true (expr1) < > 0 and expr1 < > NULL), then IF () returns expr2, otherwise expr3. IF () returns a number or string, depending on the context in which it is used:

mysql> SELECT IF(1>2,2,3);
  -> 3
mysql> SELECT IF(1<2,'yes','no');
  -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
  -> 'no'

If expr2 or expr3 is explicitly NULL, the return value of function IF () is of a type other than the NULL column. (This is newly added in the selection of MySQL 4.0. 3). expr1 is evaluated as an integer value, which means that if you are testing a floating-point or string value, you must perform a comparison operation:

mysql> SELECT IF(0.1,1,0);
 -> 0
mysql> SELECT IF(0.1<>0,1,0);
 -> 1

In the first case above, IF (0.1) returns 0 because 0.1 is converted to an integer value, returning the test result of IF (0). This may not be what you expected. In the second case, the comparison tests whether the original floating-point number is a non-zero value. The result of the comparison is used as an integer. The default IF () return value type (which is important when the result is stored in a temporary table) is determined in MySQL 3.23 as follows: Expression return value

The expression (expr2) or the expression (expr3) returns a string string

The expression (expr2) or the expression (expr3) returns a floating-point value floating-point

The expression (expr2) or the expression (expr3) returns an integer

If the expression (expr2) and the expression (expr3) are both strings and both strings ignore letter case, the return value also ignores letter case (starting with MySQL 3.23. 51).