Cursors for row iteration in MySQL - Deep Dive

Overview - Cursors for row iteration

What is it?

A cursor in MySQL is a database tool that lets you go through rows one by one from a query result. Instead of handling all rows at once, you can process each row individually. This is useful when you need to perform operations on each row separately inside a stored procedure. Cursors help manage data step-by-step, like reading a list line by line.

Why it matters

Without cursors, you would have to process all rows at once or write complex queries that might not fit every situation. Cursors solve the problem of handling data row-by-row, which is important for tasks like updating records based on complex logic or interacting with external systems. Without them, some database operations would be inefficient or impossible to do cleanly.

Where it fits

Before learning cursors, you should understand basic SQL queries, SELECT statements, and stored procedures. After mastering cursors, you can explore advanced procedural SQL features like loops, conditionals, and error handling. Cursors fit into the journey of moving from simple data retrieval to complex row-by-row data processing inside the database.

Mental Model

Core Idea

A cursor is like a bookmark that lets you move through query results one row at a time to handle each row individually.

Think of it like...

Imagine reading a book with a bookmark. Instead of reading the whole book at once, you use the bookmark to keep track of your place and read one page at a time carefully.

┌───────────────┐
│ Query Result  │
│ ┌───────────┐ │
│ │ Row 1     │ │
│ │ Row 2     │ │
│ │ Row 3     │ │
│ │ ...       │ │
│ └───────────┘ │
└─────┬─────────┘
      │
      ▼
┌───────────────┐
│   Cursor      │
│ (points to a  │
│  current row) │
└───────────────┘
      │
      ▼
┌───────────────┐
│ Process Row 1 │
│ Process Row 2 │
│ Process Row 3 │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding basic SELECT queries

Concept: Learn how to retrieve data from a table using SELECT statements.

A SELECT query fetches rows from a table. For example, SELECT * FROM employees; returns all rows and columns from the employees table. This is the starting point before using cursors because cursors work on the result of such queries.

Result

A list of rows from the table is returned.

Knowing how to write SELECT queries is essential because cursors operate on the results these queries produce.

2

Foundation

Introduction to stored procedures

Concept: Stored procedures are blocks of SQL code stored in the database that can be executed repeatedly.

A stored procedure lets you group SQL statements and logic together. For example, CREATE PROCEDURE example() BEGIN SELECT * FROM employees; END; defines a procedure that can be called to run the SELECT query. Cursors are often used inside stored procedures to process rows one by one.

Result

You can run complex logic inside the database repeatedly and efficiently.

Understanding stored procedures is key because cursors are typically used inside them to handle row-by-row processing.

3

Intermediate

Declaring and opening a cursor

🤔Before reading on: Do you think a cursor automatically starts at the first row when declared, or do you need to open it explicitly? Commit to your answer.

Concept: You must declare a cursor for a SELECT query and then open it to start fetching rows.

In MySQL, you declare a cursor with DECLARE cursor_name CURSOR FOR SELECT_statement; Then you open it with OPEN cursor_name;. This prepares the cursor to start reading rows from the query result.

Result

The cursor is ready to fetch rows one by one.

Knowing that declaring and opening are separate steps helps avoid errors where the cursor is not ready to fetch data.

4

Intermediate

Fetching rows from a cursor

🤔Before reading on: When fetching from a cursor, do you think it returns all rows at once or one row at a time? Commit to your answer.

Concept: Fetching retrieves the next row from the cursor into variables for processing.

Use FETCH cursor_name INTO variable_list; to get the next row. Each FETCH moves the cursor forward by one row. You can then use the variables to work with the row's data inside your procedure.

Result

You get one row's data at a time to process.

Understanding that FETCH moves the cursor forward one row at a time is crucial for controlled row-by-row processing.

5

Intermediate

Handling end of data with NOT FOUND condition

🤔Before reading on: Do you think fetching past the last row causes an error or sets a special condition? Commit to your answer.

Concept: MySQL uses a condition handler to detect when no more rows are available from the cursor.

Declare a CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; inside the procedure. When FETCH reaches the end, done becomes TRUE, signaling to stop fetching. This prevents errors and controls the loop that processes rows.

Result

You can safely detect when all rows are processed.

Knowing how to detect the end of cursor data prevents infinite loops and runtime errors.

6

Advanced

Looping through rows with cursors

🤔Before reading on: Is it possible to process all rows with a simple loop using cursors? Commit to your answer.

Concept: Combine cursor FETCH and a loop to process each row until no more rows remain.

Use a WHILE or REPEAT loop that fetches rows and checks the done flag. For example: DECLARE done INT DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT ...; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO variables; IF done THEN LEAVE read_loop; END IF; -- process row here END LOOP; CLOSE cur;

Result

All rows are processed one by one inside the loop.

Understanding the loop structure with cursor control flow is key to effective row-by-row processing.

7

Expert

Performance and resource considerations with cursors

🤔Before reading on: Do you think cursors are always the fastest way to process rows? Commit to your answer.

Concept: Cursors consume resources and can be slower than set-based operations; use them only when necessary.

Cursors hold locks and memory while open, which can impact database performance. They are best for complex row-by-row logic that cannot be done with set operations. Always close cursors with CLOSE cursor_name; to free resources. Consider alternatives like set-based queries or application-side processing when possible.

Result

You avoid performance bottlenecks and resource leaks.

Knowing the tradeoffs of cursors helps you choose the right tool and write efficient database code.

Under the Hood

When you declare a cursor, MySQL prepares the query and reserves a pointer to the result set. Opening the cursor executes the query and positions the pointer before the first row. Each FETCH moves the pointer forward and copies the current row's data into variables. The NOT FOUND handler detects when the pointer moves past the last row. Closing the cursor releases resources and locks held during iteration.

Why designed this way?

Cursors were designed to allow procedural row-by-row processing inside the database, which SQL alone cannot do easily. The separation of declare, open, fetch, and close steps gives precise control over resource usage and flow. Alternatives like set-based queries are faster but less flexible for complex logic. This design balances flexibility with control.

┌───────────────┐
│ DECLARE CURSOR│
└──────┬────────┘
       │
┌──────▼───────┐
│ OPEN CURSOR  │
│ (executes    │
│  query)      │
└──────┬───────┘
       │
┌──────▼───────┐
│ FETCH ROW    │
│ (move pointer│
│  forward)    │
└──────┬───────┘
       │
┌──────▼───────┐
│ PROCESS ROW  │
└──────┬───────┘
       │
┌──────▼───────┐
│ CHECK NOT    │
│ FOUND HANDLER│
└──────┬───────┘
       │
┌──────▼───────┐
│ CLOSE CURSOR │
└──────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does a cursor automatically close after fetching all rows? Commit to yes or no.

Common Belief: Once a cursor fetches all rows, it closes automatically and frees resources.

Reality: Cursors remain open until explicitly closed with CLOSE statement, holding resources.

Why it matters: Not closing cursors can cause resource leaks and lock contention in the database.

Quick: Can cursors be used to update rows directly while iterating? Commit to yes or no.

Common Belief: You can update rows directly through the cursor as you fetch them.

Reality: MySQL cursors are read-only; to update rows, you must run separate UPDATE statements using fetched data.

Why it matters: Assuming cursors allow direct updates can lead to failed updates or incorrect logic.

Quick: Do cursors always perform better than set-based queries? Commit to yes or no.

Common Belief: Cursors are faster because they process rows one at a time.

Reality: Cursors are usually slower and more resource-intensive than set-based queries.

Why it matters: Using cursors unnecessarily can degrade database performance significantly.

Quick: Does fetching from a cursor return all rows at once? Commit to yes or no.

Common Belief: Fetching from a cursor returns all rows immediately.

Reality: Fetching returns one row at a time, advancing the cursor position.

Why it matters: Misunderstanding fetch behavior can cause logic errors and infinite loops.

Expert Zone

1

Cursors in MySQL are non-scrollable and read-only, meaning you cannot move backward or update rows through them.

2

The NOT FOUND handler must be declared before opening the cursor to properly detect the end of data.

3

Opening multiple cursors simultaneously can cause resource contention; managing cursor lifecycle carefully is critical in complex procedures.

When NOT to use

Avoid cursors when set-based SQL queries can solve the problem more efficiently. Use cursors only for complex procedural logic that cannot be expressed in a single query. For large data processing, consider exporting data and processing outside the database or using batch updates.

Production Patterns

In production, cursors are used inside stored procedures for tasks like auditing each row, sending notifications per record, or complex calculations that depend on previous rows. They are combined with condition handlers and loops to ensure robust error handling and clean resource management.

Connections

Iterators in programming

Cursors in databases and iterators in programming both provide a way to access elements one at a time.

Understanding iterators in programming languages helps grasp how cursors sequentially access rows, reinforcing the concept of controlled stepwise data processing.

Batch processing in data engineering

Cursors enable row-by-row processing similar to how batch jobs process data in chunks or steps.

Knowing batch processing concepts clarifies why cursors are useful for handling large datasets incrementally rather than all at once.

Reading a book with a bookmark

Both involve keeping track of a current position to process content sequentially.

This analogy helps understand the cursor's role as a pointer moving through data, but the real connection is to sequential access patterns in computing.

Common Pitfalls

#1 Not closing the cursor after use

Wrong approach:

DECLARE cur CURSOR FOR SELECT id FROM employees;
OPEN cur;
FETCH cur INTO emp_id;
-- process emp_id
-- missing CLOSE cur;

Correct approach:

DECLARE cur CURSOR FOR SELECT id FROM employees;
OPEN cur;
FETCH cur INTO emp_id;
-- process emp_id
CLOSE cur;

Root cause:

Forgetting to close the cursor leads to resource leaks because the cursor remains open and holds locks.

#2 Not handling the end of cursor data

Wrong approach:

DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT id FROM employees;
OPEN cur;
read_loop: LOOP
FETCH cur INTO emp_id;
-- no check for done
-- process emp_id
END LOOP;
CLOSE cur;

Correct approach:

DECLARE done INT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE cur CURSOR FOR SELECT id FROM employees;
OPEN cur;
read_loop: LOOP
FETCH cur INTO emp_id;
IF done THEN LEAVE read_loop; END IF;
-- process emp_id
END LOOP;
CLOSE cur;

Root cause:

Without a NOT FOUND handler, the loop never knows when to stop, causing infinite loops or errors.

#3 Trying to update rows directly through cursor variables

Wrong approach:

FETCH cur INTO emp_id;
SET emp_id = emp_id + 1; -- expecting this updates the table

Correct approach:

FETCH cur INTO emp_id;
UPDATE employees SET id = emp_id + 1 WHERE id = emp_id;

Root cause:

Cursor variables hold copies of data, not references; updates require explicit SQL statements.

Key Takeaways

Cursors let you process query results one row at a time inside stored procedures, enabling complex row-by-row logic.

You must declare, open, fetch from, and close cursors explicitly to manage resources and control flow.

A NOT FOUND handler is essential to detect when all rows have been processed and to avoid infinite loops.

Cursors are powerful but can be slow and resource-heavy; prefer set-based queries when possible.

Understanding cursor mechanics helps write efficient, safe, and maintainable database procedures.