Explanation of Type Conversion between CAST and CONVERT Functions in MySQL Database
- 2021-11-29 16:44:44
- OfStack
MySQL
CAST()
And
CONVERT()
Function can be used to get 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);
They are CAST (xxx AS type) and CONVERT (xxx type).
There are limits to the types that can be converted. This type can be one of the following values:
Binary, with binary prefix effect: BINARY
For example, when using like fuzzy search for a field of date type, the statement should be
Create_Time like binary CONCAT(‘%',#{createTime},'%')
Here are a few examples:
Example 1
mysql> SELECT CONVERT( ' 23',SIGNED);
+ --- -+
| CONVERT( ' 23',SIGNED) |
+ --- -+
| 23 |
+ --- -+
1 row in set
This example is to convert varchar type to 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 Example 1 above, convert varchar to int with cast (a as signed), where a is a string of varchar type.
Example 4
In SQL Server, the following code demonstrates that the 106-ary storage of the date store represents the result when only a simple date and a simple time are included in the datetime variable.
DECLARE @dt datetime
-- A simple date
SET @dt='1900-1-2'
SELECT CAST(@dt as binary(8))
-- Results : 0x0000000100000000
-- Simple time
SET @dt='00:00:01'
SELECT CAST(@dt as binary(8))
-- Results : 0x000000000000012C
The type conversion of MySQL is similar to that of SQL Server1, except that the type parameters are slightly different: CAST (xxx AS type) and CONVERT (xxx, type).
Summarize