Топ-100
 
RDBMS Type: MySQL
Topic

Syntax:
ROW_COUNT()

ROW_COUNT() returns a value as follows:

o DDL statements: 0. This applies to statements such as CREATE TABLE or
DROP TABLE.

o DML statements other than SELECT: The number of affected rows. This
applies to statements such as UPDATE, INSERT, or DELETE (as before),
but now also to statements such as ALTER TABLE and LOAD DATA.

o SELECT: -1 if the statement returns a result set, or the number of
rows "affected" if it does not. For example, for SELECT * FROM t1,
ROW_COUNT() returns -1. For SELECT * FROM t1 INTO OUTFILE
'file_name', ROW_COUNT() returns the number of rows written to the
file.

o SIGNAL statements: 0.

For UPDATE statements, the affected-rows value by default is the number
of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to
mysql_real_connect()
(https://dev.mysql.com/doc/c-api/8.0/en/mysql-real-connect.html) when
connecting to mysqld, the affected-rows value is the number of rows
"found"; that is, matched by the WHERE clause.

For REPLACE statements, the affected-rows value is 2 if the new row
replaced an old row, because in this case, one row was inserted after
the duplicate was deleted.

For INSERT ... ON DUPLICATE KEY UPDATE statements, the affected-rows
value per row is 1 if the row is inserted as a new row, 2 if an
existing row is updated, and 0 if an existing row is set to its current
values. If you specify the CLIENT_FOUND_ROWS flag, the affected-rows
value is 1 (not 0) if an existing row is set to its current values.

The ROW_COUNT() value is similar to the value from the
mysql_affected_rows()
(https://dev.mysql.com/doc/c-api/8.0/en/mysql-affected-rows.html) C API
function and the row count that the mysql client displays following
statement execution.

URL: https://dev.mysql.com/doc/refman/8.0/en/information-functions.html

Example

mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t WHERE i IN(1,2);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)