CODEWITHSUNDEEP
sqlmysqlstored-procedures
abs
abs

Reputation: 650

Does Mysql have an equivalent to @@ROWCOUNT like in mssql?

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

Upvotes: 48

Views: 104402

Answers (5)

Blake
Blake

Reputation: 1

Count the number of rows in a sub-query in the where clause. Then test if the total number of rows is greater than zero.

SELECT customerNumber, customerName, creditLimit FROM customers where (SELECT count(*) as tot FROM customers) > 0;

Upvotes: -1

Rolf
Rolf

Reputation: 5743

There is another way:

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

Get the row count

SELECT COUNT(*) FROM `results`;

Get your subset

SELECT * FROM `results` LIMIT 5,10;

The temporary table exists only in the current session. I would still clean-up afterwards

DROP TEMPORARY TABLE `results`;

Upvotes: 1

AndiDog
AndiDog

Reputation: 70198

For SELECTs you can use the FOUND_ROWS construct (documented 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 clause, it returns the number of rows there would've been without the LIMIT).

For UPDATE/DELETE/INSERT, it's the ROW_COUNT construct

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

which will return the number of affected rows.

Upvotes: 77

Yada
Yada

Reputation: 31225

The simplest way would be to use a variable:

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

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

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

Upvotes: 1

Lazarus
Lazarus

Reputation: 43094

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name

    -> WHERE id > 100 LIMIT 10;

mysql> SELECT FOUND_ROWS();

Read more about this here

Upvotes: 6

Related Questions