ROW_COUNT

ROW_COUNT()

int

The following shows how to use ROW_COUNT() in a query after running an INSERT:

DROP TABLE IF EXISTS account;
CREATE TABLE account(id int, amount decimal(10,2));
INSERT account VALUES(1, 10.00), (2, 30.00);

SELECT ROW_COUNT();
****
+-------------+
| ROW_COUNT() |
+-------------+
|           2 |
+-------------+

This example shows how an application can raise an error if a ROW_COUNT() result is different than expected after a DELETE:

DELIMITER //
CREATE OR REPLACE PROCEDURE delete_account(_id int) RETURNS int AS
DECLARE _row_count int = -1;
BEGIN
  DELETE FROM account WHERE account.id = _id;
  _row_count = row_count();
  IF _row_count = 0 THEN
    RAISE user_exception(CONCAT("No rows deleted for account ", _id));
  END IF;
  RETURN _row_count;
END //
DELIMITER ;

Example use:

ECHO delete_account(3);
ERROR 2242 (HY000): Unhandled exception
Type: ER_USER_RAISE
Message: No rows deleted for account 3
Callstack:
  #0 Line 7 in `db1`.`delete_account`

ECHO delete_account(1);
+--------+
| RESULT |
+--------+
|      1 |
+--------+