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},'%')

Character type with parameter: CHAR () Date: DATE Time: TIME Date time type: DATETIME Floating point number: 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 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


Related articles: