intro
Let’s explore everything you need to know about the MySQL CAST function for seamless conversion between different data types.
In computer science, casting refers to the process of converting a value from one data type to another. As you can imagine, that operation is also common in databases. That is why most DBMS technologies—including MySQL—come with dedicated data casting and conversion operators. Enter the MySQL CAST function!
In this article, you will learn what the CAST MySQL function is, how it works, and how to use it via multiple examples covering different conversion scenarios.
Let’s dive in!
What Is CAST in MySQL?
CAST is a MySQL function that takes an expression of any type and produces a result value of the specified type. In other words, it converts a value from one data type to another. Thus, the MySQL CAST function is used to change the data type of an expression or column in a query.
Keep in mind that the CAST MySQL function is an implementation of the standard SQL CAST. On top of that, MySQL also provides an equivalent CONVERT function, which offers the same functionality as CAST.
CAST MySQL Function: Syntax and Supported Types
Here is the syntax of CAST in MySQL:
1
CAST(expr AS type)
Where:
Note that if expr is NULL, CAST will always return NULL.
The allowed values for type are:
For a complete reference, consult the official documentation.
In InnoDB, the syntax of the CAST MySQL function is:
1
CAST(expr AS type [ARRAY])
The optional ARRAY keyword is used to create a multi-valued index on a JSON array in CREATE INDEX, CREATE TABLE, and ALTER TABLE statements. Note that ARRAY is only applicable in these scenarios. When specified, the data type specified after the AS keyword can be any type supported by the CAST function, except for BINARY, JSON, and YEAR.
MySQL CAST In Action
Now that you know how to use the MySQL CAST function, you are ready to see it in action in some examples.
Since determining the data type of an SQL expression is not always straightforward, we will use DbVisualizer. This powerful database client allows you to easily identify the data type of a cell by simply clicking on it:
The value 818 could be either a string or a number. Thanks to the information displayed by DbVisualizer in the lower-left corner, you can confirm that it is a number. This is just one of the many powerful features offered by DbVisualizer.
Let’s explore some CAST MySQL examples!
Float to Int
You have the decimal value 62.73 and want to convert it to an integer. Here is how you can do it in a MySQL cast as int example:
1
SELECT CAST(62.73 AS SIGNED);
The result will be the integer number 63 :
Note that MySQL automatically rounds the number to the nearest integer.
Int to Float
When performing arithmetic calculations, one may have to convert an integer to a decimal to avoid truncation. Cast an integer to a decimal in MySQL as below:
1
SELECT CAST(8 AS DECIMAL);
The result will be the decimal representation of 8 :
As shown in DbVisualizer, this confirms that 8 is a decimal number.
String to Int
Suppose we have a number in a string and want to convert it to an integer for use in arithmetic operations. You can achieve that as in the MySQL cast string to int example below:
1
SELECT CAST ("123" AS UNSIGNED);
The result will be the number 123 :
Int to String
There are scenarios where you want to convert integers to strings. That is useful as MySQL offers a lot of functions and operators for dealing with string values. You can achieve this goal by using the following query clause:
1
SELECT CAST(42 AS CHAR)
The result will be the string "42" because we are considering 42 to be a character (string) value:
String to Date
Another common scenario is to convert a string to a date. You can do that with the CAST MySQL function, as in the example below:
1
SELECT CAST("2024-05-18" AS DATE);
The result will be the date 2025-05-18 :
If you write an invalid date string, the result will be NULL :
Timestamp to Date
Assume you have a timestamp and want to convert it to its corresponding date. Below is how you can do it:
1
SELECT CAST("2025-10-18 14:30:00" AS DATE);
The result is the date 2025-10-18:
Et voilà! You are now a master of CAST in MySQL.
Conclusion
In this guide, you saw what the CAST MySQL function is and how it works. You now know that CAST is a popular MySQL data type conversion function. Thanks to the examples shown here, you have also learned how and when to use it.
Figuring out the type of an SQL value can often be challenging, but with a robust database client like DbVisualizer, it becomes a breeze!
By simply clicking on a cell, you can instantly see the data type of the value it contains. This is just one of the many features offered by this powerful tool, which also includes advanced query optimization and automatic ERD-like schema generation. Try DbVisualizer for free today!
FAQ
Is the CAST MySQL function part of the ISO/ANSI standard?
Yes, CAST is part of the SQL standard defined by ISO/ANSI. The function is widely supported across various databases, ensuring compatibility and consistency in SQL syntax. The purpose of the function is to allow the conversion of a value from one data type to another.
Is it possible to MySQL cast as integer?
Yes, it is possible to MySQL cast as integer thanks to the CAST function. In particular, you can convert any data type to an integer by specifying SIGNED for signed integers or UNSIGNED for unsigned integers, respectively.
What is a cast as integer MySQL example?
Here is cast as integer MySQL:
1
SELECT CAST(82.91 AS SIGNED);
This will result in 83, as MySQL automatically rounds the value to the nearest integer.
What is a MySQL cast int to string example?
Below is a MySQL cast int to string example:
1
SELECT CAST(187 AS CHAR);
This will convert the integer 187 into the string "187".
What is the difference between CAST and CONVERT in MySQL?
Syntax is the difference. Both CAST and CONVERT in MySQL are used for data type conversion, but they differ slightly in syntax. CAST uses the syntax CAST(value AS type), while CONVERT uses CONVERT(value, type). Note that CONVERT does not support the creation of multi-value indexes as you can do with CAST in InnoDB by using the optional ARRAY keyword.
