Variables and control flow in MySQL - Deep Dive

Overview - Variables and control flow

What is it?

Variables in MySQL are placeholders that store data temporarily during a session or procedure. Control flow refers to the way MySQL executes commands based on conditions or loops, allowing decisions and repeated actions. Together, they help make SQL scripts dynamic and flexible. This means you can write queries that change behavior depending on data or repeat tasks automatically.

Why it matters

Without variables and control flow, SQL queries would be static and repetitive, requiring manual changes for different situations. This would slow down work and increase errors. Variables and control flow let you automate complex tasks, handle different cases, and write smarter database programs. This saves time and makes databases more powerful and responsive.

Where it fits

Before learning variables and control flow, you should understand basic SQL queries like SELECT, INSERT, UPDATE, and DELETE. After mastering this topic, you can move on to stored procedures, triggers, and advanced scripting in MySQL. This topic is a bridge from simple queries to programming logic inside the database.

Mental Model

Core Idea

Variables hold temporary data, and control flow directs which SQL commands run based on conditions or loops.

Think of it like...

Think of variables as labeled boxes where you can store things temporarily, and control flow as traffic signals that decide which path to take depending on the situation.

┌─────────────┐      ┌───────────────┐      ┌───────────────┐
│  Variables  │─────▶│ Control Flow  │─────▶│  SQL Commands │
│ (storage)   │      │ (decisions)   │      │ (actions)     │
└─────────────┘      └───────────────┘      └───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding User-defined Variables

Concept: Introduce user-defined variables that store values during a session.

In MySQL, you can create user-defined variables by prefixing a name with '@'. For example, 'SET @count := 5;' stores the number 5 in the variable named count. These variables keep their value during your session and can be used in queries. They are useful for temporary storage without creating table columns.

Result

You can reuse '@count' in later queries to refer to the value 5.

Knowing user-defined variables lets you hold temporary data and reuse it across multiple queries without changing the database structure.

2

Foundation

Local Variables in Stored Programs

Concept: Learn about local variables inside stored procedures or functions.

Inside stored procedures, you declare variables with DECLARE, like 'DECLARE total INT DEFAULT 0;'. These variables exist only during the procedure execution and help store intermediate results or counters. They are different from user-defined variables because they are local and have a defined type.

Result

You can use 'total' inside the procedure to accumulate values or control logic.

Local variables provide structured, typed storage inside procedures, enabling complex logic and calculations.

3

Intermediate

Using IF Statements for Conditional Logic

🤔Before reading on: do you think IF statements can be used outside stored procedures in MySQL? Commit to your answer.

Concept: Introduce IF statements to run different SQL commands based on conditions.

IF statements let you check a condition and run code only if it's true. In stored procedures, you write: IF condition THEN statements; ELSE statements; END IF;. This helps make decisions, like checking if a value is positive or negative and acting accordingly.

Result

The procedure runs different commands depending on the condition's truth.

Understanding IF statements unlocks the ability to make decisions in SQL scripts, making them dynamic and responsive.

4

Intermediate

Loops: Repeating Actions with WHILE

🤔Before reading on: do you think loops can run forever in MySQL? Commit to your answer.

Concept: Learn how to repeat actions using WHILE loops in stored procedures.

WHILE loops run a block of code repeatedly while a condition is true. For example, WHILE counter < 5 DO statements; END WHILE;. This lets you perform repeated tasks like counting, summing, or processing rows one by one.

Result

The loop runs multiple times until the condition becomes false.

Knowing loops lets you automate repetitive tasks inside the database, reducing manual work and errors.

5

Intermediate

Using CASE for Multi-way Decisions

Concept: Introduce CASE expressions to choose between many options.

CASE lets you check multiple conditions and return different results. For example, CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END. This is useful for grading, categorizing, or complex decision trees.

Result

The query returns different outputs based on the value tested.

CASE expressions simplify complex conditional logic into readable, compact code.

6

Advanced

Combining Variables and Control Flow in Procedures

🤔Before reading on: do you think variables keep their values after a procedure ends? Commit to your answer.

Concept: Learn how variables and control flow work together inside stored procedures for powerful logic.

Inside a procedure, you can declare variables, use IF and WHILE to control flow, and update variables as you go. For example, you can loop through numbers, add them to a variable, and return the total. Variables reset each time the procedure runs, so they don't keep values between calls.

Result

Procedures can perform complex calculations and return results dynamically.

Understanding the lifecycle of variables and control flow inside procedures is key to writing reliable, reusable database code.

7

Expert

Handling Flow Control with SIGNAL and Handlers

🤔Before reading on: do you think MySQL can raise custom errors inside procedures? Commit to your answer.

Concept: Explore advanced control flow with SIGNAL to raise errors and handlers to catch them.

MySQL lets you use SIGNAL to raise custom errors, like SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error message';. You can also declare handlers to catch errors or warnings and respond, for example, to clean up or retry. This adds robustness to your procedures by managing unexpected situations.

Result

Procedures can handle errors gracefully and control execution flow on failures.

Knowing how to raise and handle errors inside SQL code is essential for building fault-tolerant database applications.

Under the Hood

MySQL stores variables in memory during a session or procedure execution. User-defined variables exist for the session and are stored in a session context. Local variables inside procedures are allocated on a stack frame for that procedure call. Control flow statements like IF and WHILE are parsed and executed by the MySQL server's SQL engine, which evaluates conditions and directs execution accordingly. SIGNAL and handlers interact with the server's error handling subsystem to manage exceptions.

Why designed this way?

MySQL separates user-defined and local variables to balance flexibility and safety. User variables allow quick, session-wide temporary storage without schema changes, while local variables provide typed, scoped storage for structured programming. Control flow constructs were added to support procedural programming inside the database, enabling complex logic close to data. SIGNAL and handlers were introduced to improve error management, making stored programs more robust and maintainable.

Session Start
   │
   ▼
┌───────────────┐
│ User Variables │ (session scope, stored in session memory)
└───────────────┘
        │
        ▼
┌─────────────────────────┐
│ Stored Procedure Call   │
│ ┌─────────────────────┐ │
│ │ Local Variables      │ │ (stack frame, procedure scope)
│ └─────────────────────┘ │
│ ┌─────────────────────┐ │
│ │ Control Flow Engine  │ │ (executes IF, WHILE, CASE)
│ └─────────────────────┘ │
└─────────────────────────┘
        │
        ▼
   SQL Execution
        │
        ▼
   Result or Error
        │
        ▼
   SIGNAL / Handler
        │
        ▼
   Error Management

Myth Busters - 4 Common Misconceptions

Quick: Do user-defined variables keep their values after you disconnect from MySQL? Commit to yes or no.

Common Belief: User-defined variables keep their values permanently until you change them.

Reality: User-defined variables exist only during your current session and are lost when you disconnect.

Why it matters: Assuming variables persist can cause bugs when scripts rely on old values that no longer exist.

Quick: Can you use IF statements directly in a normal SELECT query? Commit to yes or no.

Common Belief: IF statements can be used anywhere in SQL, including simple SELECT queries.

Reality: IF statements as control flow are only allowed inside stored programs; outside, you use IF() function or CASE expressions.

Why it matters: Trying to use IF statements in normal queries causes syntax errors and confusion.

Quick: Does a WHILE loop always run at least once? Commit to yes or no.

Common Belief: WHILE loops always execute their body at least once, like some loops in other languages.

Reality: WHILE loops check the condition before running; if false initially, the body never runs.

Why it matters: Misunderstanding this can lead to logic errors and infinite loops.

Quick: Can SIGNAL be used to handle errors automatically? Commit to yes or no.

Common Belief: SIGNAL automatically fixes errors when they occur.

Reality: SIGNAL raises errors; it does not handle or fix them. Handlers are needed to catch errors.

Why it matters: Confusing SIGNAL and handlers can cause unhandled errors and crashes.

Expert Zone

1

User-defined variables are loosely typed and can change type dynamically, which can cause subtle bugs if not carefully managed.

2

Local variables inside procedures have strict scope and lifetime, resetting on each call, which affects how state is maintained across calls.

3

Control flow statements in MySQL are limited compared to full programming languages; for example, there is no direct support for 'for' loops, requiring workarounds.

When NOT to use

Avoid using user-defined variables for critical application state because they are session-scoped and not transaction-safe. Instead, use table columns or temporary tables for persistent or shared data. For complex logic, consider application-level code or more advanced procedural languages like PL/pgSQL in PostgreSQL.

Production Patterns

In production, variables and control flow are commonly used in stored procedures for batch processing, data validation, and complex calculations. SIGNAL and handlers are used to enforce business rules and ensure data integrity by raising and managing errors gracefully. Loops and conditionals automate repetitive tasks like archiving old records or generating reports.

Connections

Programming Variables and Control Structures

Builds-on

Understanding variables and control flow in MySQL is easier if you know how variables and conditionals work in general programming languages like Python or JavaScript.

Finite State Machines (FSM)

Similar pattern

Control flow in SQL procedures resembles FSMs where the program moves between states (conditions) based on inputs, helping understand complex decision logic.

Workflow Automation

Builds-on

Variables and control flow in databases are foundational for automating workflows, similar to how business process automation tools use variables and conditions to control task sequences.

Common Pitfalls

#1 Using user-defined variables without initializing them.

Wrong approach:

SELECT @total + 1; -- without setting @total first

Correct approach:

SET @total := 0; SELECT @total + 1;

Root cause:

User-defined variables default to NULL if not initialized, causing unexpected NULL results in expressions.

#2 Trying to use IF statement outside stored procedures.

Wrong approach:

IF @count > 5 THEN SELECT 'High'; END IF;

Correct approach:

SELECT IF(@count > 5, 'High', 'Low');

Root cause:

IF statements are only valid inside stored programs; outside, use IF() function or CASE.

#3 Creating infinite loops by not updating loop variables.

Wrong approach:

WHILE counter < 5 DO SELECT counter; END WHILE;

Correct approach:

WHILE counter < 5 DO SET counter = counter + 1; END WHILE;

Root cause:

Loop condition never changes, causing infinite execution.

Key Takeaways

Variables in MySQL store temporary data either for the session or inside stored procedures, enabling dynamic SQL behavior.

Control flow statements like IF, WHILE, and CASE let you make decisions and repeat actions inside the database.

User-defined variables are session-scoped and loosely typed, while local variables in procedures are typed and scoped to the procedure call.

SIGNAL and handlers provide advanced error management, making stored programs more robust.

Understanding variables and control flow is essential to write flexible, automated, and maintainable database scripts.

Practice

1. What is the purpose of a variable in MySQL stored procedures?

easy

A. To create new tables dynamically

B. To permanently save data in the database

C. To store temporary data during procedure execution

D. To define user permissions

Solution

1. Step 1: Understand variable role in procedures

   Variables hold temporary data only while the procedure runs.

2. Step 2: Differentiate from permanent storage

   Permanent data is stored in tables, not variables.

3. Final Answer:

   To store temporary data during procedure execution -> Option C

4. Quick Check:

   Variables = Temporary data [OK]

Hint: Variables hold temporary data inside procedures [OK]

Common Mistakes:

• Confusing variables with table data storage
• Thinking variables create database objects
• Assuming variables control user access

2. Which of the following is the correct way to declare and set a variable in MySQL stored procedures?

easy

A. SET x INT; DECLARE x = 10;

B. DECLARE x INT; SET x = 10;

C. DECLARE x = 10 INT;

D. SET x = 10; DECLARE x INT;

Solution

1. Step 1: Recall syntax for variable declaration

   Use DECLARE variable_name data_type; to declare.

2. Step 2: Assign value using SET

   Use SET variable_name = value; after declaration.

3. Final Answer:

   DECLARE x INT; SET x = 10; -> Option B

4. Quick Check:

   DECLARE then SET = correct syntax [OK]

Hint: Declare first, then set value with SET [OK]

Common Mistakes:

• Using SET before DECLARE
• Combining declaration and assignment incorrectly
• Wrong order of keywords

3. Consider the following MySQL procedure snippet:

DECLARE counter INT DEFAULT 1;
WHILE counter <= 3 DO
  SET counter = counter + 1;
END WHILE;
SELECT counter;

What will be the output of the SELECT counter; statement?

medium

A. 3

B. Error due to missing BEGIN...END

C. 1

D. 4

Solution

1. Step 1: Trace the WHILE loop increments

   Counter starts at 1, increments by 1 each loop until counter > 3.

2. Step 2: Count loop iterations and final value

   Loop runs while counter=1,2,3; increments to 4 and stops.

3. Final Answer:

   4 -> Option D

4. Quick Check:

   Counter ends at 4 after loop [OK]

Hint: WHILE increments counter until condition false [OK]

Common Mistakes:

• Assuming counter stops at 3
• Forgetting counter increments before condition check
• Thinking syntax error without BEGIN...END in snippet

4. Identify the error in this MySQL stored procedure snippet:

DECLARE total INT;
SET total = 0
WHILE total < 5 DO
  SET total = total + 1;
END WHILE;

medium

A. Missing semicolon after SET total = 0

B. WHILE loop condition syntax is wrong

C. DECLARE must be inside BEGIN...END block

D. Variable total not declared

Solution

1. Step 1: Check statement endings

   SET total = 0 is missing a semicolon at the end.

2. Step 2: Validate syntax for DECLARE and WHILE

   DECLARE is valid before BEGIN, and WHILE syntax is correct.

3. Final Answer:

   Missing semicolon after SET total = 0 -> Option A

4. Quick Check:

   Every statement needs semicolon [OK]

Hint: Check semicolons after each statement [OK]

Common Mistakes:

• Ignoring missing semicolon errors
• Thinking DECLARE must be inside BEGIN...END always
• Misreading WHILE syntax

5. You want to create a MySQL stored procedure that counts how many numbers between 1 and 10 are even, using variables and control flow. Which of the following code snippets correctly implements this?

hard

A. DECLARE count_even INT DEFAULT 0; DECLARE i INT DEFAULT 1; WHILE i <= 10 DO IF i % 2 = 0 THEN SET count_even = count_even + 1; END IF; SET i = i + 1; END WHILE; SELECT count_even;

B. DECLARE count_even INT; SET count_even = 0; FOR i IN 1..10 DO IF i % 2 = 0 THEN SET count_even = count_even + 1; END IF; END FOR; SELECT count_even;

C. DECLARE count_even INT DEFAULT 0; SET i = 1; WHILE i < 10 DO IF i % 2 = 1 THEN SET count_even = count_even + 1; END IF; SET i = i + 1; END WHILE; SELECT count_even;

D. DECLARE count_even INT DEFAULT 0; DECLARE i INT DEFAULT 1; WHILE i <= 10 DO IF i / 2 = 0 THEN SET count_even = count_even + 1; END IF; SET i = i + 1; END WHILE; SELECT count_even;

Solution

1. Step 1: Check loop and variable initialization

   DECLARE count_even INT DEFAULT 0; DECLARE i INT DEFAULT 1; WHILE i <= 10 DO IF i % 2 = 0 THEN SET count_even = count_even + 1; END IF; SET i = i + 1; END WHILE; SELECT count_even; correctly declares variables and initializes them.

2. Step 2: Verify condition and increment logic

   DECLARE count_even INT DEFAULT 0; DECLARE i INT DEFAULT 1; WHILE i <= 10 DO IF i % 2 = 0 THEN SET count_even = count_even + 1; END IF; SET i = i + 1; END WHILE; SELECT count_even; uses WHILE loop from 1 to 10, checks even with modulo, increments count and i properly.

3. Step 3: Identify errors in other options

   DECLARE count_even INT; SET count_even = 0; FOR i IN 1..10 DO IF i % 2 = 0 THEN SET count_even = count_even + 1; END IF; END FOR; SELECT count_even; uses invalid FOR syntax; C counts odd numbers; D uses division instead of modulo.

4. Final Answer:

   Option A code correctly counts even numbers between 1 and 10 -> Option A

5. Quick Check:

   Use WHILE + modulo for even check [OK]

Hint: Use WHILE loop and modulo (%) to check even numbers [OK]

Common Mistakes:

• Using invalid FOR loop syntax in MySQL
• Confusing modulo (%) with division (/)
• Counting odd numbers instead of even