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:

CAST (value as type); 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:

• 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

mysql> SELECT CONVERT (' signed '); +----------------------+| CONVERT (' signed ', |+----------------------+|                   |+----------------------+1 Row in Set

Example Two

mysql> SELECT CAST (' 125e342.83 ' as signed) +------------------------------+| CAST (' 125e342.83 ' as signed) |+------------------------------+|                          |+------------------------------+1 Row in Set

Example Three

mysql> SELECT CAST (' 3.35 ' as signed) +------------------------+| CAST (' 3.35 ' as signed) |+------------------------+|                      3 |+------------------------+1 row in Set

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.

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))-Results: 0x000000000000012c

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

This article turns from http://www.nowamagic.net/librarys/veda/detail/2044

The use of the MySQL cast and CONVERT functions