ROW_COUNT
Returns the number of rows inserted, updated, or deleted by the preceding statement.
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(); **** +-------------+ | 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 | +--------+