Variables and control flow in MySQL - Step-by-Step Execution

Concept Flow - Variables and control flow

Start

↓

Declare Variables

↓

Assign Initial Values

↓

Evaluate Condition

|Yes↓

Execute Statements

↓

Update Variables

↓

Loop Back to Condition

No↓

End

This flow shows how variables are declared, assigned, checked in conditions, updated, and how control loops until the condition is false.

Execution Sample

MySQL

SET @counter = 1;
WHILE @counter <= 3 DO
  SELECT @counter;
  SET @counter = @counter + 1;
END WHILE;

This code uses a variable to count from 1 to 3, selecting the value each time.

Execution Table

Step	Variable @counter	Condition (@counter <= 3)	Action	Output
1	1	1 <= 3 is TRUE	SELECT 1; SET @counter = 2	1
2	2	2 <= 3 is TRUE	SELECT 2; SET @counter = 3	2
3	3	3 <= 3 is TRUE	SELECT 3; SET @counter = 4	3
4	4	4 <= 3 is FALSE	Exit loop	No output

💡 Loop ends because @counter is 4 and condition 4 <= 3 is FALSE

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
@counter	undefined	1	2	3	4

Key Moments - 2 Insights

Why does the loop stop when @counter is 4 even though we set it to 4 inside the loop?

Because the condition is checked before each loop iteration (see step 4 in execution_table). When @counter becomes 4, the condition @counter <= 3 is FALSE, so the loop exits before running the body again.

What happens if we forget to update @counter inside the loop?

The condition will always be TRUE (e.g., @counter stays 1), causing an infinite loop. The execution_table shows how @counter changes each step to avoid this.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of @counter at Step 3?

A 2

B 3

C 4

D 1

💡 Hint

Check the 'Variable @counter' column at Step 3 in the execution_table.

At which step does the loop condition become false?

A Step 4

B Step 3

C Step 2

D Step 1

💡 Hint

Look at the 'Condition' column in the execution_table to find when it is FALSE.

If we change the initial value of @counter to 4, what will happen?

A The loop will run once

B The loop will run three times

C The loop will not run at all

D The loop will run infinitely

💡 Hint

Refer to the condition check in the execution_table and consider if 4 <= 3 is TRUE or FALSE at the start.

Concept Snapshot

Variables and control flow in MySQL:
- Declare variables with SET @var = value;
- Use WHILE loops to repeat while a condition is TRUE
- Condition checked before each loop iteration
- Update variables inside loop to avoid infinite loops
- Loop ends when condition becomes FALSE

Full Transcript

This lesson shows how to use variables and control flow in MySQL. We start by declaring a variable @counter and setting it to 1. Then we use a WHILE loop that runs as long as @counter is less than or equal to 3. Inside the loop, we select the current value of @counter and then increase it by 1. The loop repeats until @counter becomes 4, at which point the condition is false and the loop stops. We track the variable changes and see how the condition controls the loop. This helps avoid infinite loops by updating variables properly.