Type casting and conversion in MySQL

Introduction

Type casting changes data from one type to another so you can use it correctly in your queries.

When you want to add a number stored as text to a real number.

When you need to compare a string with a number.

When you want to format a date stored as text into a date type.

When you want to convert a number to text to display it with other strings.

When you want to ensure calculations use the right data type.

Syntax

MySQL

CAST(expression AS target_type)
CONVERT(expression, target_type)

CAST and CONVERT do the same thing but have slightly different syntax.

target_type can be types like CHAR, SIGNED, UNSIGNED, DATE, DATETIME, DECIMAL, etc.

Examples

This converts the string '123' to the number 123.

MySQL

SELECT CAST('123' AS SIGNED);

This converts the string to a DATE type.

MySQL

SELECT CONVERT('2024-06-01', DATE);

This converts the number 123 to a string '123'.

MySQL

SELECT CAST(123 AS CHAR);

This converts the string '45.67' to a decimal number with 5 digits total and 2 after the decimal point.

MySQL

SELECT CONVERT('45.67', DECIMAL(5,2));

Sample Program

This query shows different type conversions: string to number, number to string, string to date, and string to decimal.

MySQL

SELECT
  CAST('100' AS SIGNED) AS number_value,
  CAST(200 AS CHAR) AS string_value,
  CONVERT('2024-06-15', DATE) AS date_value,
  CONVERT('123.45', DECIMAL(6,2)) AS decimal_value;

Important Notes

If conversion fails, MySQL may return 0 or NULL depending on the context.

Use CAST or CONVERT to avoid errors when mixing data types in calculations or comparisons.

Summary

Type casting changes data from one type to another.

Use CAST or CONVERT functions in MySQL for type conversion.

This helps avoid errors and makes data work correctly in queries.