Usage of mysql cast and convert functions

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);

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

The types that can be converted are limited. This type can be one of the following values:

Binary, with the effect of binary prefix: BINARY

Character type, can take parameters: CHAR()

Date: DATE

Time: TIME

Date time type: DATETIME

Floating point: 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

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

Like the above example, change 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 result of date storage when the datetime variable contains only a simple date and a simple time.

DECLARE @dt datetime
  
--单纯的日期
SET @dt='1900-1-2'
SELECT CAST(@dt as binary(8))
--结果: 0x0000000100000000
  
--单纯的时间
SET @dt='00:00:01'
SELECT CAST(@dt as binary(8))
--结果: 0x000000000000012C

MySQL's type conversion is the same as that of SQL Server, but the type parameters are a little different: CAST(xxx AS type), CONVERT(xxx, type).