Summary of MySQL common type conversion functions

MySQL function, general type conversion function summary

1, Concat function.

Connection strings are commonly used: concat functions. Like queries such as SQL query criteria, and C.name as 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 with cast (str as unsigned) str to a varchar type string.

For example, the usual percentage conversions:

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 parameters: CHAR ()
Date: Date
Time:
DateTime Date/Time type
Floating point number: DECIMAL

Binary STR is the abbreviated form of cast (str as BINARY):

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

0

3. If function

If in MySQL is a function and not a command

IF (EXPR1,EXPR2,EXPR3)
If Expr1 is true (expr1 <> 0 and Expr1 <> NULL), then if () returns EXPR2, otherwise returns 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, then the return value type of the function if () is a type of non-NULL column. (This is a new addition to MySQL 4.0.3). EXPR1 is calculated as an integer value, which means that if you are testing a floating-point or string value, you must do a comparison:
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, and the test result of if (0) is returned. This may not be what you expect. In the second case, the comparison tests whether the original floating-point number is a non-0 value. The results of the comparison are used as integers. The default if () return value type (which is very important when the result is stored in a temporary table) is determined in MySQL 3.23 as follows: expression return value
Expression (EXPR2) or expression (EXPR3) return value is a string string
Expression (EXPR2) or expression (EXPR3) return value is floating-point value float
Expression (EXPR2) or expression (EXPR3) return value is integer integral type
If both the expression (EXPR2) and the expression (EXPR3) are strings and two strings are ignored, the return value is also ignored (starting with MySQL 3.23.51).

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Summary of MySQL common type conversion functions