In MySQL, ROW_COUNT() is a built-in function that returns the number of rows updated, inserted, or deleted by the preceding statement.

The value returned by ROW_COUNT() is often the same as the row count that the mysql client displays following statement execution, as well as the value from the mysql_affected_rows() C API function.

Syntax

The syntax goes like this:

ROW_COUNT()

No arguments are required, or accepted.

Examples

DDL Statements

For DDL statements, such as CREATE TABLE and DROP TABLE, the ROW_COUNT() function returns 0.

Let’s create a table:

CREATE TABLE Idiots (
  IdiotId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  IdiotName VARCHAR(255) NOT NULL
);

Result:

Query OK, 0 rows affected (0.02 sec)

And run ROW_COUNT():

SELECT ROW_COUNT();

Result:

+-------------+
| ROW_COUNT() |
+-------------+
|           0 |
+-------------+

As expected, 0 is returned, because zero rows were affected.

DML Statements

For DML statements other than SELECT, the ROW_COUNT() function returns the number of affected rows.

Below are some examples.

Insert Data

Let’s insert some rows into the table we created earlier:

INSERT INTO Idiots ( IdiotName ) VALUES ( 'Homer' );
INSERT INTO Idiots ( IdiotName ) VALUES ( 'Flanders' );
INSERT INTO Idiots ( IdiotName ) VALUES ( 'Peter Griffin' );

Result:

mysql> INSERT INTO Idiots ( IdiotName ) VALUES ( 'Homer' );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Idiots ( IdiotName ) VALUES ( 'Flanders' );
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO Idiots ( IdiotName ) VALUES ( 'Peter Griffin' );
Query OK, 1 row affected (0.00 sec)

Each statement resulted in one row being affected.

And let’s run ROW_COUNT() again:

SELECT ROW_COUNT();

Result:

+-------------+
| ROW_COUNT() |
+-------------+
|           1 |
+-------------+

This returns 1 because that’s how many rows were affected in the last statement. Even though we affected three rows, it took three statements to do that (each statement inserted just one row, and ROW_COUNT() only reports on the last statement).

Let’s insert three more rows, except this time we’ll insert them all within the same statement:

INSERT INTO Idiots ( IdiotName ) VALUES 
    ( 'Dumb' ),
    ( 'Dumber' ),
    ( 'Dumbest' );

Result:

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

Now let’s run ROW_COUNT() again:

SELECT ROW_COUNT();

Result:

+-------------+
| ROW_COUNT() |
+-------------+
|           3 |
+-------------+

This time all three are accounted for.

Update Data

Now let’s update the data in all six rows:

UPDATE Idiots
SET IdiotName = 'Homer';

Result:

Query OK, 5 rows affected (0.01 sec)
Rows matched: 6  Changed: 5  Warnings: 0

My mysql client tells me that although six rows matched the criteria, just five rows were changed. This is because the first row already contains Homer, which is also what we’re trying to update it to.

Let’s see what ROW_COUNT() returns:

SELECT ROW_COUNT();

Result:

+-------------+
| ROW_COUNT() |
+-------------+
|           5 |
+-------------+

As expected, it returns 5, because that’s how many rows were actually updated.

Result Set Statements

For statements that return a result set (such as SELECT, SHOW, DESC or HELP), the ROW_COUNT() function returns -1, even when the result set is empty. This is also true for administrative statements, such as OPTIMIZE TABLE.

Example:

SELECT * FROM Idiots;

Result:

+---------+-----------+
| IdiotId | IdiotName |
+---------+-----------+
|       1 | Homer     |
|       2 | Homer     |
|       3 | Homer     |
|       4 | Homer     |
|       5 | Homer     |
|       6 | Homer     |
+---------+-----------+

Now call ROW_COUNT() again:

SELECT ROW_COUNT();

Result:

+-------------+
| ROW_COUNT() |
+-------------+
|          -1 |
+-------------+

REPLACE Statements

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.

More Information

See the MySQL documentation for more details and a few other things to be mindful of when using this function.