Shulou(Shulou.com)05/31 Report--

This article shows you what type conversion functions are commonly used in MySQL, which are concise and easy to understand, which can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1. Concat function.

Connection strings are commonly used: the concat function. For example, like query for sql query condition, AND c.name like concat (# {param.name},'%')

To convert Int to varchar, you often use concat functions, such as concat (8) 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 a string of type varchar with cast (str as unsigned) str for Int.

For example, the commonly used percentage conversion:

Select cast ((1bat 3) * 100as 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 integers: UNSIGNED

Binary, same effect with binary prefix: BINARY

Character type, with parameter: CHAR ()

Date: DATE

Time: TIME

Date and time type: DATETIME

Floating point numbers: DECIMAL

Mysql > SELECT BINARY 'a' = 'Agar;-> 0

3. IF function

If in mysql is a function, not 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 string, depending on the context in which it is used:

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

If expr2 or expr3 is explicitly NULL, the return type of the function IF () is of a type that is not a NULL column. (this is when you choose to add something new in 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 compare:

Mysql > SELECT IF (0.1 0mysql > SELECT IF);-> 1

In the first case above, IF (0. 1) returns 0 because 0. 1 is converted to an integer value, returning the test results of IF (0). This may not be what you expect. In the second case, compare and test 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: the expression returns the value

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

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

Expression (expr2) or expression (expr3) returns an integer

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

The above are the commonly used type conversion functions in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.