Mysql是否具有与mssql中的@ rowcount相同的功能?

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

如何获得MySQL中的行计数值，就像mssql中的@ rowcount那样?

4 个解决方案

#1

For SELECTs you can use the FOUND_ROWS construct (documented here):

对于select，您可以使用FOUND_ROWS构造(这里有文档说明):

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).

它将返回最后一个SELECT查询中的行数(或者如果第一个查询有一个LIMIT子句，它将返回没有LIMIT的行数)。

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

对于更新/删除/插入，它是ROW_COUNT结构

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

which will return the number of affected rows.

它将返回受影响的行数。

#2

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name

    -> WHERE id > 100 LIMIT 10;

mysql> SELECT FOUND_ROWS();

#3

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.

或者您可以使用FOUND_ROWS()构造，将SQL_CALC_FOUND_ROWS放在SELECT语句中。

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

#4

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`;

#1