MySQL type conversion function The use of convert vs. cast, and the difference between SQL Server

First, the CONVERT function

Character Set conversions: convert (XXX USING gb2312) type conversions are the same as SQL Server, but differ on type parameters: CAST (xxx as type), convert (XXX, type), type must be of the following type:

Available type binary with binary prefix effect: binary character type, parameter: CHAR () Date: Date Time: Time datetime type: DATETIME float Points: DECIMAL integer: Signed unsigned integer: UNSIGNED use of 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, Parameter: CHAR () Date: Date Time: Time datetime type: DATETIME floating point number: DECIMAL integer : Signed unsigned integer: UNSIGNED Here are a few examples:

Example One

Copy Code code example:mysql> SELECT CONVERT (' signed '); +----------------------+ | CONVERT (' All ', signed) |                   +----------------------+ | 23 | +----------------------+ 1 row in set Example II

Copy Code code example:mysql> SELECT CAST (' 125e342.83 ' as signed); +------------------------------+ | CAST (' 125e342.83 ' as signed) |                          +------------------------------+ | 125 | +------------------------------+ 1 row in set example three

Copy Code code example:mysql> SELECT CAST (' 3.35 ' as signed); +------------------------+ | CAST (' 3.35 ' as signed) |                      +------------------------+ | 3 | +------------------------+ 1 row in set like the example above, convert varchar 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 the pure date and the pure time when the date stores the hexadecimal store representation.

Copy Code code example: 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))--Result: 0x000000000000012c

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

MySQL type conversion function The use of convert vs. cast, and the difference between SQL Server