MySQL Type Conversion: CAST and CONVERT

I hadn’t paid much attention to MySQL type conversions until it started causing some strange bugs in one of our reporting modules. Initially, everything seemed fine—queries ran correctly, and data was returned correctly most of the time—but then inconsistent totals began appearing in the financial data, and some filters would randomly fail based on user input.

It turned out the problem wasn’t in the business logic at all, but in the type conversions.

Many values ​​imported from APIs or CSVs, even those that looked like numbers or dates, were actually strings. MySQL silently performs type conversions in the background, which sounds convenient, but it’s not. After spending several nights debugging mismatched comparisons and incorrect calculations, I started using explicit type conversions in almost every critical place.

1. Type Conversion in MySQL

Type conversion is essentially the process of converting one data type to another. This sounds simple, but it’s a situation that frequently arises in real-world projects.

In our case, it usually happened when:

• Numbers are passed in as strings from an external system,
• Dates are exported as plain text,
• Or the front-end parameters do not match the actual database column types.

For example, we once had an order filter where order_id was an INT in MySQL, but the frontend always sent it as a string. Most of the time MySQL handled it automatically, but under some conditions indexes stopped being used efficiently. That was annoying to track down.

MySQL gives us two main ways to explicitly convert types:

CAST(expression AS data_type)

and

CONVERT(expression, data_type)

They have a lot of overlap in function, but there are also some practical differences.

2. CAST(): Explicitly Converting Data Types

I probably use CAST() more often because the syntax feels cleaner and it’s standard SQL, so moving queries between databases is less painful.

Syntax

CAST(expression AS data_type)

• expression: value or column being converted
• data_type: target type like CHAR, DATE, SIGNED, DECIMAL

Example 1: Converting String to Integer

SELECT CAST('123' AS SIGNED) AS converted_number;

We previously imported product quantities from a CSV file. All data was imported as text:

'100'
'250'
'30'

Looked harmless until calculations started behaving oddly in aggregate queries.

Using:

CAST(... AS SIGNED)

forced MySQL to treat the value as an actual integer instead of text.

Result:

123

After the explicit conversion, the arithmetic results returned to normal. Previously, debugging totals was indeed frustrating because this problem only occurred in certain specific cases.

Example 2: Converting Number to String

SELECT CAST(2026 AS CHAR) AS converted_text;

This tool was a great help to me in generating export labels and dynamic report messages.

The query converts numeric 2026 into:

'2026'

which is now treated as text.

I originally tried concatenating values directly without conversion and got inconsistent formatting depending on the query. Explicitly casting to CHAR made the output stable across reports.

Example 3: Converting String to Date

SELECT CAST('2026-04-27' AS DATE) AS converted_date;

This became important during a migration project.

We had old records where dates were stored as VARCHAR values. Not ideal, but legacy systems rarely care about ideal.

Without conversion, date comparisons were unreliable:

WHERE created_at > '2026-04-01'

Sometimes its behavior is more like a string comparison than a date comparison.

Converting it to the correct DATE value immediately resolved a series of filtering issues.

Also made date arithmetic work correctly:

• adding days,
• calculating intervals,
• sorting chronologically.

3. CONVERT(): Another Way to Change Data Types

CONVERT() does almost the same thing as CAST(), though I mostly use it when character set conversion is involved.

Syntax

CONVERT(expression, data_type)

Example 1: Converting String to Integer

SELECT CONVERT('456', SIGNED) AS converted_number;

Same idea as CAST().

Converts:

'456'

into a numeric type.

To be honest, in my daily work I usually choose the syntax that best suits my queries. In general applications, the performance difference is negligible.

Example 2: Converting Number to String

SELECT CONVERT(2026, CHAR) AS converted_text;

Converts:

2026

to:

'2026'

We used this a lot when building CSV exports directly from SQL queries.

Example 3: Using CONVERT for Character Set Conversion

SELECT CONVERT('Hello World' USING utf8) AS utf8_text;

This one became extremely useful in a multilingual project.

We had data coming from different systems using inconsistent encodings. Some pages displayed normal text, others showed corrupted characters like:

é
漢字

Classic encoding nightmare.

Using:

CONVERT(... USING utf8)

helped normalize text output before exporting or displaying data.

Not glamorous work, but definitely one of those things that saves hours later.

4. Key Differences Between CAST and CONVERT

In practice, unless there’s a specific need to handle character sets, I usually use CAST() by default.

I’ve learned this the hard way: relying on implicit type conversions is very risky. A query might work fine today, but it could fail later if the schema changes, the collation changes, or the dataset grows larger.

Explicit type conversions clearly express intent, especially when someone else needs to maintain the query six months from now.

5. Practical Use Cases of Type Conversion

A. Calculating with String Numbers

SELECT CAST('100' AS DECIMAL) + CAST('50.5' AS DECIMAL) AS total;

This is directly related to a billing issue we encountered.

Values ​​imported from an external API appear to be numbers, but are actually strings. Calculation results sometimes exhibit strange formatting or rounding errors.

After converting explicitly:

100 + 50.5 = 150.5

everything became consistent.

I remember checking this in logs at nearly 1 AM because totals in invoices were off by decimals in only certain currencies.

B. Formatting Dates as Strings

SELECT CONCAT('Today is ', CAST(CURDATE() AS CHAR)) AS message;

Simple example, but surprisingly common.

We used this pattern when generating scheduled email reports directly from SQL.

Output:

Today is 2026-04-27

Without conversion, formatting sometimes varied depending on connectors and drivers.

C. Ensuring Accurate Comparisons

SELECT *
FROM orders
WHERE order_id = CAST('101' AS SIGNED);

This fixed one of the more subtle performance problems I ran into.

The frontend passed IDs as strings:

{
  "order_id": "101"
}

while the database column was INT.

The query still worked, but under heavy load the execution plan occasionally changed and index usage became inconsistent.

Explicit casting stabilized the query behavior and improved lookup performance noticeably. Not dramatic — maybe from ~120ms down to ~20ms on average — but enough to matter in APIs handling thousands of requests.

A few habits I’ve developed after dealing with enough weird conversion bugs:

• Use explicit conversions when data types are ambiguous.
• When writing portable SQL, prioritize using the CAST() function.
• Use the CONVERT() function for character set handling and encoding cleanup.
• Don’t blindly trust imported data, especially CSV files and data imported from external APIs.
• Always test comparisons and calculations using real production data.

One issue I encountered early on surprised me: MySQL’s tolerance for implicit conversions is extremely high. This sounds convenient, but it also means bugs can remain hidden for a long time, only revealing themselves under extreme circumstances.
And these extreme circumstances are often the most difficult bugs to explain in production incidents.