ROW_COUNT()¶

ROW_COUNT() returns the affected-row status of the most recently executed SQL statement: DML statements report affected rows, result-set and failed statements report -1, and DDL uses statement-specific counts.

Description¶

ROW_COUNT() reports the affected-row status of the previous statement in the current session:

For DML statements such as INSERT, UPDATE, DELETE, and REPLACE, it returns the number of affected rows.
After a result-set statement such as SELECT, or after a failed statement, it returns -1. It does not report the number of rows in a result set.
Most DDL statements return 0, but some statements return a statement-specific affected-row count. For example, CREATE TABLE ... AS SELECT returns the number of inserted rows. DROP DATABASE returns the number of removed catalog relations/objects, including tables, views, and sequences; an index on a table does not add another count.

Additional affected-row rules include:

Statement	Reported status
`REPLACE` that inserts a new row	`1`
`REPLACE` that replaces a conflicting row	`2` (delete plus insert)
Ordinary `UPDATE`	Counts matched rows, including rows assigned their existing values. This differs from MySQL’s default changed-row count; MySQL reports matched rows only when the client enables `CLIENT_FOUND_ROWS`.
`INSERT ... ON DUPLICATE KEY UPDATE`	`1` for insert, `2` for an update that changes a row, and `0` for a no-op update
`CALL`	Propagates the final nonnegative affected-row count produced by the procedure. If the procedure’s final statement returns a result set, the subsequent `ROW_COUNT()` returns `0`.
Prepared `SELECT ROW_COUNT()`	Reads the status at `EXECUTE` time, not at `PREPARE` time

The value reflects the immediately preceding statement, so a second consecutive SELECT ROW_COUNT() reports -1 because its preceding statement is the previous SELECT.

Syntax¶

ROW_COUNT()

Return Value¶

Returns an integer affected-row status. Returns -1 after a result-set statement or a failed statement. DDL normally returns 0, except for statements that produce a statement-specific count, including rows inserted by CREATE TABLE ... AS SELECT and catalog relations removed by DROP DATABASE.

Arguments¶

ROW_COUNT() takes no arguments. It is called as an empty function and reads the affected-row count from the immediately preceding statement in the current session.

Examples¶

DROP DATABASE IF EXISTS row_count_demo;
CREATE DATABASE row_count_demo;
USE row_count_demo;

CREATE TABLE t (id INT PRIMARY KEY, v INT);
INSERT INTO t VALUES (1, 10), (2, 20);
SELECT ROW_COUNT();
UPDATE t SET v = v + 1 WHERE id IN (1, 2);
SELECT ROW_COUNT();
DELETE FROM t WHERE id = 2;
SELECT ROW_COUNT();
SELECT v FROM t WHERE id = 1;
SELECT ROW_COUNT();

DROP DATABASE row_count_demo;

The four SELECT ROW_COUNT() calls in this example return 2, 2, 1, and -1, respectively. Calling ROW_COUNT() again immediately after the final call also returns -1, because the preceding statement produced a result set.