Type casting and conversion in MySQL - Step-by-Step Execution

Concept Flow - Type casting and conversion

Start with original value

↓

Apply CAST or CONVERT function

↓

Value changes type

↓

Use converted value in query

↓

Return result with new type

This flow shows how a value is taken, converted to a new type using CAST or CONVERT, then used in the query with the new type.

Execution Sample

MySQL

SELECT CAST('123' AS UNSIGNED) AS num;
SELECT CONVERT('2024-06-01', DATE) AS date_val;

These queries convert a string to an unsigned number and a string to a date type.

Execution Table

Step	Query	Value Before	Conversion Function	Value After	Result Type
1	SELECT CAST('123' AS UNSIGNED) AS num;	'123'	CAST('123' AS UNSIGNED)	123	UNSIGNED INTEGER
2	SELECT CONVERT('2024-06-01', DATE) AS date_val;	'2024-06-01'	CONVERT('2024-06-01', DATE)	2024-06-01	DATE
3	End of queries	-	-	-	-

💡 All conversions complete, queries return values in new types.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
value	'123'	123	123	123
value	'2024-06-01'	'2024-06-01'	'2024-06-01'	'2024-06-01'

Key Moments - 2 Insights

Why does CAST('123' AS UNSIGNED) return 123 as a number, not a string?

CAST changes the data type from string to number, so the output is numeric, as shown in execution_table row 1.

What happens if the string cannot be converted to the target type?

MySQL returns 0 or NULL depending on context; this is not shown here but is important to remember.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result type after casting '123'?

A UNSIGNED INTEGER

B STRING

C DATE

D FLOAT

💡 Hint

Check the 'Result Type' column in execution_table row 1.

At which step does the value change from string to date?

A Step 1

B Step 2

C Step 3

D No change

💡 Hint

Look at the 'Value After' and 'Result Type' columns in execution_table row 2.

If the string 'abc' is cast to UNSIGNED, what would likely happen?

A Returns NULL

B Returns 'abc'

C Returns 0

D Returns error

💡 Hint

Recall key_moments about invalid conversions returning 0 or NULL.

Concept Snapshot

Type casting changes a value's data type.
Use CAST(value AS type) or CONVERT(value, type).
Common types: UNSIGNED, SIGNED, CHAR, DATE.
Invalid casts may return 0 or NULL.
Useful for ensuring correct data types in queries.

Full Transcript

This lesson shows how MySQL converts data types using CAST and CONVERT functions. We start with a string value, apply a conversion function, and get a new value with a different type. For example, casting '123' as UNSIGNED turns it into the number 123. Converting '2024-06-01' to DATE changes the string into a date type. The execution table tracks each step, showing the original value, the conversion applied, and the resulting value and type. Beginners often wonder why the output changes type; it's because CAST explicitly changes the data type. If a string cannot be converted properly, MySQL returns 0 or NULL instead of an error. The quiz questions help reinforce understanding by asking about result types and behavior on invalid input. Remember, type casting is essential to control data formats in your queries.