Summary of MySQL commonly used type conversion functions (recommended)

1. Concat function.

Commonly used connection string: concat function. For example, the like query of sql query condition, AND c.name like concat(#{param.name},'%')

The concat function is often used to convert Int to varchar, for example, concat(8,'0') gets 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 using cast(str as unsigned) str is a string of varchar type.

For example, 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)
The 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 the binary prefix: BINARY
Character type, can take parameters: CHAR()
Date: DATE
Time: TIME
Date and time type: DATETIME
Floating point numbers: 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 it returns expr3. IF() returns a number or a 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 type of the IF() function is the type of the non-NULL column. (This option is new in MySQL 4.0.3). expr1 is evaluated as an integer value, which means that if a floating point or string value is tested, a comparison must be performed:

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 result of the test of IF(0). This might not be what you expect. 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 as follows in MySQL 3.23:

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

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

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

If both expr2 and expr3 are strings, and both strings are case-insensitive, the return value is also case-insensitive (as of MySQL 3.23.51).

The above is a summary of the commonly used MySQL type conversion functions that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!