The use of the MySQL cast and CONVERT functions

MySQL's cast () and CONVERT () functions can be used to get a value of one type and produce a value of another type. The specific syntax for both is as follows:

View Source print?

1	CAST(value astype);

2	CONVERT(value, type);

is cast (xxx as type), CONVERT (XXX, type).

There is a limit to the types that can be converted. This type can be one of the following values: Huimin County House Zhe Clothing

• Binary, with binary prefix effect: binary
• Character type, with parameters: CHAR ()
• Date: Date
• Time:
• DateTime Date/Time type
• Floating point number: DECIMAL
• Integer: Signed
• unsigned integer: UNSIGNED

Here are a few examples:

Example One

View Source print?

1	mysql> SELECTCONVERT(‘23‘,SIGNED);

2	+----------------------+

3	| CONVERT(‘23‘,SIGNED) |

4	+----------------------+

5	|                   23 |

6	+----------------------+

7	1 row inset

Example Two

View Source print?

1	mysql> SELECTCAST(‘125e342.83‘ ASsigned);

2	+------------------------------+

3	| CAST(‘125e342.83‘ASsigned) |

4	+------------------------------+

5	|                          125 |

6	+------------------------------+

7	1 row inset

Example Three

View Source print?

1	mysql> SELECTCAST(‘3.35‘ ASsigned);

2	+------------------------+

3	| CAST(‘3.35‘ASsigned) |

4	+------------------------+

5	|                      3 |

6	+------------------------+

7	1 row inset

As in the example above, the varchar is converted to int with cast (a as signed), where a is a varchar-type string.

Example 4

In SQL Server, the following code demonstrates the result of a DateTime variable that contains only a single date and a simple time when the date stores the hexadecimal store representation.

View Source print?

01	DECLARE @dt datetime

02

03

--单纯的日期

04	SET @dt=‘1900-1-2‘

05	SELECT CAST(@dt asbinary(8))

06	--结果: 0x0000000100000000

07

08

--单纯的时间

09	SET @dt=‘00:00:01‘

10	SELECT CAST(@dt asbinary(8))

11	--结果: 0x000000000000012C

MySQL type conversion is like SQL Server, that is, the type parameter is a little different: CAST (xxx as type), convert (XXX, type).

The use of the MySQL cast and CONVERT functions