Outdated Version
You are viewing an older version of this section. View current production version.
ROW_COUNT
Returns the number of rows affected by the last INSERT, UPDATE, or DELETE
command. It can be used from client applications, stored procedures, and the
command line.
Syntax
ROW_COUNT()
Arguments
- None.
Return Type
int
Examples
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();
Results:
+-------------+
| 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 |
+--------+
