Summary: in this tutorial, we will show you how to use MySQL CAST function to convert a value of any type into a value with a specified type.

Introduction to MySQL CAST function

The syntax of the MySQL CAST() function is as follows:

CAST(expression AS TYPE)

The CAST() function converts a value of any type into a value that has a specified type. The target type can be any one of the following types: BINARY, CHAR, DATE, DATETIME, TIME,DECIMAL, SIGNED, UNSIGNED.

The CAST() function is often used to return a value with a specified type for comparison in the WHERE, JOIN, and HAVING clauses.

Let’s take a look at some examples of using the CAST() function.

MySQL CAST function examples

In the following example, MySQL converts a string into an integer implicitly before doing calculation:

SELECT (1 + '1')/2;

To explicitly convert a string into an integer, you use the CAST() function as the following statement:

SELECT (1 + CAST('1' AS UNSIGNED))/2;

The following statement explicitly convert an integer into a string and concatenate the string with another string:

SELECT CONCAT('MySQL CAST example #',CAST(2 AS CHAR));

Let’s take a look at the orders table in the sample database.

See the following query:

SELECT orderNumber,        requiredDate FROM orders WHERE requiredDate BETWEEN '2003-01-01' AND '2003-01-31';

The query selects orders whose required dates are in January 2003. The data type of the requireDate column is DATE therefore MySQL has to convert the literal strings: '2003-01-01' and '2003-01-31' into TIMESTAMP values before evaluating WHERE condition.

However, to be safe, you can use CAST() function to explicitly convert a string into a TIMESTAMP value as follows:

SELECT orderNumber,        requiredDate FROM orders WHERE requiredDate BETWEEN  CAST('2003-01-01' AS DATETIME)                         AND CAST('2003-01-31' AS DATETIME);

The following statement converts DOUBLE values into CHAR values and uses the results as the arguments of the 

In this tutorial, you have learned how to use the MySQL CAST() function to convert a value with any type into a value with a specified type.

原文链接：http://outofmemory.cn/mysql/function/mysql-cast