MySQL – FOUND_ROWS() Function for Total Number of Rows Affected

In SQL Server to get the total number of rows affected by the last statement, you can use the system variable @@ROWCOUNT. Similarly, in MySQL you can find it using FOUND_ROWS () function.

Let us create this dataset


CREATE TABLE TEST(ID INT, NAME VARCHAR(30));
INSERT INTO TEST(ID,NAME)
SELECT 1,'NAME1' UNION ALL
SELECT 2,'NAME2' UNION ALL
SELECT 3,'NAME3' UNION ALL
SELECT 4,'NAME4' UNION ALL
SELECT 5,'NAME5';

SELECT * FROM TEST;
SELECT FOUND_ROWS();

MySQL - FOUND_ROWS() Function for Total Number of Rows Affected mysqlrowcount
The above will return two resultsets. The second result set will be 5 (no of rows for the SELECT statement).

You can also use SQL_CALC_FOUND_ROWS along with FOUND_ROWS () to get the total number of rows in a table. When SQL_CALC_FOUND_ROWS is used FOUND_ROWS() will omit the LIMIT clause.

SELECT * FROM TEST LIMIT 2;

The above will return 2 rows only.

SELECT SQL_CALC_FOUND_ROWS * FROM TEST LIMIT 2;
SELECT FOUND_ROWS();

MySQL - FOUND_ROWS() Function for Total Number of Rows Affected mysqlrowcount1
When you execute the above, it returns two result sets. The first result set will return two rows. The second result set will return 5 (total number of rows without LIMIT clause).

I think this is really interesting function and we can use it in daily application. Do leave a comment if you are using an application where you need to know how many rows were affected with the previous comment.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Backup Randomly Failing with Error 112 (There is not enough space on the disk.)
Next Post
SQL SERVER – Identifying Page Types

Related Posts

1 Comment. Leave new

Leave a Reply

Menu