In MariaDB, 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 the same as the row count that the mysql client displays and 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 (including TRUNCATE) and for other statements which don’t return any result set (such as USE, DO, SIGNAL or DEALLOCATE PREPARE), the ROW_COUNT() function returns 0.

Let’s create a table:

CREATE OR REPLACE TABLE guest (
  guest_id INT NOT NULL AUTO_INCREMENT,
  guest_name VARCHAR(255) NOT NULL,
  PRIMARY KEY (guest_id)
);

Result:

Query OK, 0 rows affected (0.046 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 and for ALTER TABLE, the ROW_COUNT() function returns the number of affected rows.

Below are some examples.

Insert Data

Now let’s insert some rows:

INSERT INTO guest (guest_name) VALUES ('Homer');
INSERT INTO guest (guest_name) VALUES ('Bart');
INSERT INTO guest (guest_name) VALUES ('Marge');

Result:

MariaDB [Zap]> INSERT INTO guest (guest_name) VALUES ('Homer');
Query OK, 1 row affected (0.037 sec)

MariaDB [Zap]> INSERT INTO guest (guest_name) VALUES ('Bart');
Query OK, 1 row affected (0.001 sec)

MariaDB [Zap]> INSERT INTO guest (guest_name) VALUES ('Marge');
Query OK, 1 row affected (0.002 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).

Update Data

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

UPDATE guest
SET guest_name = 'Homer';

Result:

Query OK, 2 rows affected (0.008 sec)
Rows matched: 3  Changed: 2  Warnings: 0

My mariadb client tells me that although three rows matched the criteria, just two 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() |
+-------------+
|           2 |
+-------------+

As expected, it returns 2, 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.

Example:

SELECT * FROM guest;

Result:

+----------+------------+
| guest_id | guest_name |
+----------+------------+
|        1 | Homer      |
|        2 | Homer      |
|        3 | Homer      |
+----------+------------+
3 rows in set (0.000 sec)

Now call ROW_COUNT() again:

SELECT ROW_COUNT();

Result:

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

Foreign Keys & Triggers

Note that 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.

More Information

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