MariaDB ROW_COUNT() Function

In MariaDB, ROW_COUNT() is a built-in function that returns the number of rows affected by the last update, insert or delete statement.

MariaDB ROW_COUNT() Syntax

Here is the syntax of the MariaDB ROW_COUNT() function:

ROW_COUNT()

Parameters

The MariaDB ROW_COUNT() function do not require any parameters.

Return value

The MariaDB ROW_COUNT() function returns the number of rows affected by the last SQL statement executed. The execution logic of the ROW_COUNT() function is as follows:

  • If the previous statement was a DDL statement, the ROW_COUNT() function will return 0. For example CREATE TABLE, DROP TABLE wait.

  • If the previous statement was a UPDATE, INSERT, DELETE, ALTER TABLE or LOAD DATA statement, the ROW_COUNT() function returns the number of rows affected.

  • If the previous statement was a SELECT statement, the ROW_COUNT() function returns -1.

  • If the previous statement was a SELECT statement that does not return a result set, the ROW_COUNT() function returns the number of rows affected. For example: SELECT * FROM t1 INTO OUTFILE 'file_name'.

MariaDB ROW_COUNT() Example

The following example shows how to use the ROW_COUNT() function.

First, let’s create a table named test_row_count:

CREATE TABLE test_row_count(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

Let’s call the ROW_COUNT() function:

SELECT ROW_COUNT();

Output:

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

ROW_COUNT() returned 0 because the previous statement was a CREATE statement .

Let’s insert 2 rows for testing:

INSERT INTO test_row_count (id) VALUES (NULL), (NULL);

Output:

Query OK, 2 rows affected (0.014 sec)
Records: 2  Duplicates: 0  Warnings: 0

Here, it 2 rows affected tells us that 2 rows were affected, the two newly added rows. Let’s use the ROW_COUNT() function to see how many rows are affected:

SELECT ROW_COUNT();

Output:

+-------------+
| ROW_COUNT() |
+-------------+
|           2 |
+-------------+

Here, the 2 returned by the ROW_COUNT() function is consistent with 2 rows affected that was returned by the above INSERT statement.

Let’s query the data in the table:

SELECT * FROM test_row_count;

Output:

+----+
| id |
+----+
|  1 |
|  2 |
+----+

Let’s use the ROW_COUNT() function to see how many rows are affected:

SELECT ROW_COUNT();

Output:

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

Here, the ROW_COUNT() function returns -1 for SELECT statements.

Conclusion

In MariaDB, ROW_COUNT() is a built-in function that returns the number of rows affected by the last update, insert or delete statement.