Explanation of CAST and CONVERT functions for type conversion in MySQL database

MySQL's CAST() and CONVERT() functions can be used to obtain a value of one type and produce a value of another type.

The specific syntax of the two is as follows:

CAST(value as type); 
CONVERT(value, type);

That is, CAST(xxx AS type), CONVERT(xxx, type).

There are restrictions on the types that can be converted. The type can be one of the following values:

Binary, with the effect of binary prefix: BINARY

For example, when using like to fuzzy search Create_Time like binary CONCAT('%',#{createTime},'%')

• Character type, with parameters: CHAR()
• Date: DATE
• Time: TIME
• Date and time type: DATETIME
• Floating point numbers: DECIMAL
• Integer: SIGNED
• Unsigned integer: UNSIGNED

Here are a few examples:

Example 1

mysql> SELECT CONVERT('23',SIGNED); 
+———————-+ 
| CONVERT('23',SIGNED) | 
+———————-+ 
| 23 | 
+———————-+ 
1 row in set

This example converts a varchar type to an int type.

Example 2

mysql> SELECT CAST('125e342.83' AS signed);
+------------------------------+
| CAST('125e342.83' AS signed) |
+------------------------------+
| 125 |
+------------------------------+
1 row in set

Example 3

mysql> SELECT CAST('3.35' AS signed);
+------------------------+
| CAST('3.35' AS signed) |
+------------------------+
| 3 |
+------------------------+
1 row in set

As in the example above, to convert varchar to int, use cast(a as signed), where a is a string of type varchar.

Example 4

In SQL Server, the following code demonstrates the hexadecimal storage representation of the date when the datetime variable contains only a pure date and a pure time.

DECLARE @dt datetime
--Simple date SET @dt='1900-1-2'
SELECT CAST(@dt as binary(8))
--Result: 0x0000000100000000
--Simple time SET @dt='00:00:01'
SELECT CAST(@dt as binary(8))
--Result: 0x000000000000012C

MySQL type conversion is the same as SQL Server, except that the type parameter is slightly different: CAST(xxx AS type), CONVERT(xxx, type).

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links