MySQL CAST vs CONVERT

MySQL CAST and MySQL CONVERT both help you change data type in MySQL. They are very similar in functionality with a few differences. Here’s the comparison MySQL CAST vs CONVERT to help you decide which one to use.

Table of Contents

MySQL CAST vs CONVERT

Here’s a step by step comparison MySQL CAST vs CONVERT.

Functionality

MySQL CAST allows you to cast data from one data type to another data type. Here’s our detailed article about MySQL CAST.

MySQL CONVERT also allows you to convert data from one data type to another data type. It also allows you to convert character set of data into another character set. MySQL CAST cannot be used to change character set. Here’s our detailed article about MySQL CONVERT.

You can use MySQL CAST as well as MySQL CONVERT to convert data type of literals as well as columns.

Syntax

Here’s the syntax of MySQL CAST function

CAST(data as data_type)

MySQL CAST requires two inputs – the data to be typecasted and the data type (decimal, char, etc) to which you want to convert this data. You can cast data into BINARY, CHAR, DATE, DATETIME, TIME, DECIMAL, SIGNED, UNSIGNED data types.

You can provide data as a literal value as shown below

CAST(1 as char)

You can data as a column name (e.g id)

CAST(id as char)

Bonus Read : MySQL Rollback Query

Here’s the syntax of MySQL CONVERT function

CONVERT(input_value, data_type)

In the above function, you need to provide input_value as literal or column name, and data_type to which you want to convert this data. Like MySQL CAST, you can cast data into BINARY, CHAR, DATE, DATETIME, TIME, DECIMAL, SIGNED, UNSIGNED data types.

For example, here’s how to convert literal value to another data type

CONVERT(1, char)

and here’s how to convert a column (e.g id) to another data type

CONVERT(id, char)

Here’s MySQL CONVERT syntax to convert character sets

CONVERT(input_value USING character_set)

In the above function, you need to provide input_value as literal string or column name, and character_set to which you want to convert this data.

Here’s an example of how to cast as latin1

convert('test string' using 'latin1')

Bonus Read : How to Use MySQL Rollup

Supported data types

Both MySQL CAST and MySQL CONVERT support data conversion only to BINARY, CHAR, DATE, DATETIME, TIME,DECIMAL, SIGNED, UNSIGNED data types. Other data types are not supported.