MySQL ROW_COUNT() Function

The MySQL ROW_COUNT() function returns the number of rows affected by the execution of the last SQL statement.

ROW_COUNT() Syntax

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

ROW_COUNT()

Parameters

The MySQL ROW_COUNT() function does not require any parameters.

Return value

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

  • The ROW_COUNT() function returns 0 if the previous statement was a DDL statement . For example CREATE TABLE, DROP TABLE etc.

  • 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 that returns a result set, 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'.

ROW_COUNT() Examples

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

First, let’s create a table test_row_count:

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

Let’s insert two rows for testing:

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

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

Here, 2 rows affected told us that 2 rows were affected, that is the two rows just inserted. Let’s use the ROW_COUNT() function to see the number of rows affected:

SELECT ROW_COUNT();

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

Here, the value 2 returned by the ROW_COUNT() function is the same as that returned by the INSERT statement above 2 rows affected.

Let’s query the data in the table:

SELECT * FROM test_row_count;

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

Let’s use the ROW_COUNT() function to see the number of rows affected:

SELECT ROW_COUNT();

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

Here, the ROW_COUNT() function returns -1.