Syntax
------

ROW_COUNT()

Description
-----------

ROW_COUNT() returns the number of rows updated, inserted or deleted by the
preceding statement. This is the same as the row count that the mariadb client
displays and the value from the mysql_affected_rows() C API function.

Generally:

* For statements which return a result set (such as SELECT, SHOW, DESC or
HELP), returns -1, even when the result set is empty. This is also true for
administrative statements, such as OPTIMIZE.
* For DML statements other than SELECT and for ALTER TABLE, returns the number
of affected rows.
* For DDL statements (including TRUNCATE) and for other statements which don't
return any result set (such as USE, DO, SIGNAL or DEALLOCATE PREPARE), returns
0.

For UPDATE, affected rows is by default the number of rows that were actually
changed. If the CLIENT_FOUND_ROWS flag to mysql_real_connect() is specified
when connecting to mysqld, affected rows is instead the number of rows matched
by the WHERE clause.

For REPLACE, deleted rows are also counted. So, if REPLACE deletes a row and
adds a new row, ROW_COUNT() returns 2.

For INSERT ... ON DUPLICATE KEY, updated rows are counted twice. So, if INSERT
adds a new rows and modifies another row, ROW_COUNT() returns 3.

ROW_COUNT() does not take into account rows that are not directly
deleted/updated by the last statement. This means that rows deleted by foreign
keys or triggers are not counted.

Warning: You can use ROW_COUNT() with prepared statements, but you need to
call it after EXECUTE, not after DEALLOCATE PREPARE, because the row count for
allocate prepare is always 0.

Warning: When used after a CALL statement, this function returns the number of
rows affected by the last statement in the procedure, not by the whole
procedure.

Warning: After INSERT DELAYED, ROW_COUNT() returns the number of the rows you
tried to insert, not the number of the successful writes.

This information can also be found in the diagnostics area.

Statements using the ROW_COUNT() function are not safe for replication.

Examples
--------

CREATE TABLE t (A INT);

INSERT INTO t VALUES(1),(2),(3);

SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           3 |
+-------------+

DELETE FROM t WHERE A IN(1,2);

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

Example with prepared statements:

SET @q = 'INSERT INTO t VALUES(1),(2),(3);';

PREPARE stmt FROM @q;

EXECUTE stmt;
Query OK, 3 rows affected (0.39 sec)
Records: 3  Duplicates: 0  Warnings: 0

SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           3 |
+-------------+

URL: https://mariadb.com/kb/en/row_count/