Does Mysql have the equivalent of @@ ROWCOUNT, as in mssql?

Question

How can I get count values ​​in MySQL as @@ROWCOUNT in mssql?

Answer 1

For SELECT, you can use the FOUND_ROWS construct ( described here ):

 SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever; SELECT FOUND_ROWS( ) ; 

which will return the number of rows in the last SELECT query (or if the first query has a LIMIT , it returns the number of rows that would be without LIMIT ).

For UPDATE / DELETE / INSERT , this is a ROW_COUNT construct

 INSERT INTO your_table VALUES (1,2,3); SELECT ROW_COUNT(); 

which will return the number of rows affected.

Answer 2

 mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name -> WHERE id > 100 LIMIT 10; mysql> SELECT FOUND_ROWS(); 

Read more about it here.

Answer 3

The easiest way is to use a variable:

 mysql> SELECT @rowcount:=COUNT(*) FROM my_table; mysql> SELECT @rowcount; 

Or you can use the FOUND_ROWS() construct after placing SQL_CALC_FOUND_ROWS in a SELECT statement.

 mysql> SELECT SQL_CALC_FOUND_ROWS * FROM my_table; mysql> SELECT FOUND_ROWS(); 

Answer 4

There is another way:

 CREATE TEMPORARY TABLE `results` AS ( *** Your query without LIMIT *** ); 

Get row count

 SELECT COUNT(*) FROM `results`; 

Get a subset

 SELECT * FROM `results` LIMIT 5,10; 

The temporary table exists only in the current session. I will still be cleansed after

 DROP TEMPORARY TABLE `results`;