MySQL中HANDLER FOR NOT FOUND的工作原理及用法咨询

Hey there! No worries about being new to MySQL—let's break down HANDLER FOR NOT FOUND nice and clearly, since it's a super useful tool for handling missing data in stored programs.

What is HANDLER FOR NOT FOUND? #

It's a error handling mechanism specifically for MySQL stored programs (like stored procedures, functions, and triggers). Its sole job is to catch situations where a data operation doesn't return any rows—think:

• A SELECT ... INTO statement that tries to fetch a row into variables but finds nothing
• A cursor that's reached the end of its result set (no more rows to FETCH)

How does it work? #

First, some context: MySQL uses DECLARE HANDLER to define custom error responses in stored code. HANDLER FOR NOT FOUND targets two specific error conditions:

• Error code 1329 (labeled NO_DATA)
• SQLSTATE code '02000' (the standard SQL code for "no data found")

Here's the core flow:

1. You declare the handler inside a BEGIN...END block, after declaring any variables or cursors (MySQL requires this order).
2. When your code triggers one of those "no data" errors, execution jumps immediately to the code defined in the handler.
3. After running the handler code, what happens next depends on the handler type:
   • CONTINUE HANDLER: Execution resumes at the statement right after the one that triggered the error.
   • EXIT HANDLER: Execution exits the current BEGIN...END block immediately (any remaining code in the block won't run).

Practical Usage Examples #

Let's walk through common scenarios where this handler saves the day.

1. Handling empty SELECT ... INTO results #

Suppose you have a stored procedure that fetches a user's name by ID. If the ID doesn't exist, you want to return a clear message instead of letting the procedure throw an error.

DELIMITER //
CREATE PROCEDURE GetUserName(IN user_id INT, OUT user_name VARCHAR(50))
BEGIN
    -- Define the NOT FOUND handler to set a fallback value
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET user_name = 'User not found';
    
    -- Try to fetch the user's name
    SELECT name INTO user_name FROM users WHERE id = user_id;
END //
DELIMITER ;

If the SELECT finds no matching user, the handler triggers, sets user_name to the fallback text, and the procedure finishes cleanly.

2. Stopping cursor loops when no more rows exist #

Cursors are great for iterating over result sets, but they don't stop automatically when they reach the end. The NOT FOUND handler lets you signal the end of the loop.

DELIMITER //
CREATE PROCEDURE ListAllUsers()
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE user_id INT;
    DECLARE user_name VARCHAR(50);
    
    -- Declare a cursor to fetch user data
    DECLARE user_cursor CURSOR FOR SELECT id, name FROM users;
    
    -- Handler: set 'done' to TRUE when no more rows are found
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN user_cursor;
    
    -- Loop through the cursor results
    read_loop: LOOP
        FETCH user_cursor INTO user_id, user_name;
        
        -- Exit the loop if we've reached the end
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Process each row (here, we just print the user info)
        SELECT CONCAT('User ID: ', user_id, ', Name: ', user_name) AS user_info;
    END LOOP;
    
    CLOSE user_cursor;
END //
DELIMITER ;

When the cursor runs out of rows, FETCH triggers the NOT FOUND error, done gets set to TRUE, and the loop exits gracefully.

3. Choosing between CONTINUE and EXIT handlers #

The handler type changes behavior significantly. Let's compare:

CONTINUE Example

DELIMITER //
CREATE PROCEDURE DemoContinueHandler()
BEGIN
    DECLARE result VARCHAR(50);
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET result = 'No data found';
    
    SELECT non_existent_column INTO result FROM non_existent_table;
    -- This line will run even after the handler triggers
    SELECT 'Handler finished, continuing execution' AS status;
END //
DELIMITER ;

EXIT Example

DELIMITER //
CREATE PROCEDURE DemoExitHandler()
BEGIN
    DECLARE result VARCHAR(50);
    DECLARE EXIT HANDLER FOR NOT FOUND
        SET result = 'No data found';
    
    SELECT non_existent_column INTO result FROM non_existent_table;
    -- This line will NEVER run because the EXIT handler exits the block
    SELECT 'This won''t print' AS status;
END //
DELIMITER ;

内容的提问来源于stack exchange，提问作者Svetoslav Nedelchev