How to find out which rows were affected after UPDATE statement inside MySQL procedure?

Question

How to find out affected rows after using UPDATE statements inside procedure in MySQL?(without using an API mysql_affected_rows()). Thanks!

DROP PROCEDURE IF EXISTS users_login;
DELIMITER //
CREATE PROCEDURE users_login(IN _id INT UNSIGNED)
    BEGIN

    DECLARE _error TINYINT DEFAULT FALSE;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _error = TRUE;

    UPDATE users SET login = NOW() WHERE id = _id;

    IF (_error = TRUE) THEN
        SHOW ERRORS;
    END IF;

    END//

DELIMITER ;

I just want to make some check inside of the procedure.

Answer 1

You don't need your procedure. It just produces unnecessary overhead.

To answer your question. If you want to know, you should do a SELECT first, to check which rows will be affected. If you're using InnoDB (it supports transactions) instead of MyISAM, you can even lock the rows before updating, so that concurrent sessions don't change rows between your select and your update statement. You should do this in a transaction, like this:

START TRANSACTION;
SELECT whatever FROM your_table WHERE foo = 'bar' FOR UPDATE;
UPDATE your_table SET whatever = 'new_value' WHERE foo = 'bar';
COMMIT;

When the transaction is finished with the commit;, the lock is released again.

If you don't want to do a SELECT first, you can use variables to store the primary keys of the affected rows.

(a bit hacky and not tested, but it should work)

SET @affected_ids := NULL;
UPDATE your_table 
SET whatever = 'new_value',
primary_key_column = IF(@affected_ids := CONCAT_WS(',', primary_key_column, @affected_ids), primary_key_column, primary_key_column)
WHERE foo = 'bar';

It works like this. The IF() function has the syntax

IF(<boolean expression>, <then>, <else>)

In the <boolean expression> part, we add the current rows primary key to the variable. This is always true. Nonetheless we update the primary key with the primary key, we specify it in both true and false part. The value doesn't change and MySQL is in fact smart enough to not even touch it.

After your UPDATE statement you simply do

SELECT @affected_ids;

and get all rows that were updated.