CAST
Converts a value to the specified data type.
MySQL 8.1
CAST(value AS type)Parameters
- value
- Value to convert
- typekeyword
- Target data type, some types take a size in parentheses
- Values:BINARYCHARDATEDATETIMEDECIMALDOUBLEFLOATJSONNCHARREALSIGNEDTIMEUNSIGNEDYEAR
Return value
A value of the given type; a failed conversion gives 0 or NULL with a warning, not an error
Examples
DECIMAL with and without scale
MySQL 8.1
SELECT CAST(12005.6 AS DECIMAL) AS rounded,
CAST(12005.6 AS DECIMAL(10, 2)) AS with_scale;Average with two decimals
MySQL 8.1
SELECT family_member,
CAST(AVG(unit_price) AS DECIMAL(10, 2)) AS avg_price
FROM Payments
GROUP BY family_member;Strings to an integer
MySQL 8.1
SELECT CAST('12abc' AS SIGNED) AS number_prefix,
CAST('abc' AS SIGNED) AS no_number;Details
The list of target types in MySQL is shorter than the list of column types: an integer is written as SIGNED or UNSIGNED, and CAST(1 AS INT) is a syntax error. A string is CHAR, not VARCHAR.