IF @@ROWCOUNT < 1

對(duì)應(yīng)到mysql中可以使用 FOUND_ROWS() 函數(shù)來(lái)替換。

1. found_rows() 函數(shù)

文檔地址：http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_found-rows

1）found_rows() 的第一種使用情況(帶有SQL_CALC_FOUND_ROWS,也帶有 limit)：

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name

-> WHERE id > 100 LIMIT 10;

mysql> SELECT FOUND_ROWS();

The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.

前面的帶有l(wèi)imit的select語(yǔ)句如果加上了 SQL_CALC_FOUND_ROWS，那么接下來(lái)執(zhí)行的 SELECT FOUND_ROWS(); 將返回前面語(yǔ)句不帶limit語(yǔ)句返回的行數(shù)。

此種情況下，select found_rows() 和 select count(*) 有一個(gè)很小的區(qū)別：如果userId允許為null，那么select found_rows() 的結(jié)果可能要比select count(*) 要小一些。因?yàn)榍罢叩葍r(jià)于：select count(userId) from Users; 而該語(yǔ)句不會(huì)計(jì)算userId 為null的行在內(nèi)。而count(*)會(huì)計(jì)算在內(nèi)。

2）found_rows() 的第二種/第三中使用情況(不帶有SQL_CALC_FOUND_ROWS)：

In the absence of the SQL_CALC_FOUND_ROWS option in the most recent successful SELECT statement, FOUND_ROWS() returns the number of rows in the result set returned by that statement. If the statement includes a LIMIT clause, FOUND_ROWS() returns the number of rows up to the limit. For example, FOUND_ROWS() returns 10 or 60, respectively, if the statement includes LIMIT 10 or LIMIT 50, 10.

The row count available through FOUND_ROWS() is transient and not intended to be available past the statement following the SELECT SQL_CALC_FOUND_ROWS statement. If you need to refer to the value later, save it:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM ... ;

mysql> SET @rows = FOUND_ROWS();

If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client.

1> 第二種使用情況(不帶有SQL_CALC_FOUND_ROWS,也沒(méi)有帶 limit ):

如果前面的select語(yǔ)句沒(méi)有帶 SQL_CALC_FOUND_ROWS，也沒(méi)有帶 limit ，那么后面的 SELECT FOUND_ROWS(); 返回的結(jié)果就是前面的select返回的行數(shù)；

2> 第三中使用情況(不帶有SQL_CALC_FOUND_ROWS,但是有帶 limit ):

如果前面的select語(yǔ)句沒(méi)有帶 SQL_CALC_FOUND_ROWS，但是帶有 limit，那么后面的 SELECT FOUND_ROWS(); 返回的結(jié)果就是limit語(yǔ)句到達(dá)的最大的行數(shù)，比如：select * from xxx limit 10; 到達(dá)的最大的行數(shù)為10，所以 found_rows() 返回10；比如 select * from xxx limit 50,10; 它要從第50行開(kāi)始，再掃描10行，所以到達(dá)的最大的行數(shù)為60，所以found_rows() 返回60。

這里第一個(gè)select found_rows() 返回105，因?yàn)樗菑钠?00的地方，再掃描5行，所以返回105；但是第二個(gè)掃描的結(jié)果為空，select found_rows()返回了0！而不是105，因?yàn)?where userId=999999的結(jié)果為空，所以后面的 limit 100,5根本就沒(méi)有執(zhí)行。所以select found_rows()返回了0。

再看一個(gè)例子，更深入的理解其中情況下的 found_rows():

上面sql中 user_Pwd=xx 的值都是一樣的?？梢钥吹竭@種情況下的found_rows() 是對(duì)的select語(yǔ)句的中間結(jié)果，再 limit 時(shí)，此時(shí)的limit的掃描到的最大的行數(shù)。和原始表中的數(shù)據(jù)的行數(shù)，是沒(méi)有關(guān)系的。他是對(duì)select的中間結(jié)果的limit，然后才得到最后的結(jié)果集，再返回。

3）SQL_CALC_FOUND_ROWS and FOUND_ROWS() 適合使用的場(chǎng)景

SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS() enables you to determine how many other pages are needed for the rest of the result.

1> SQL_CALC_FOUND_ROW + limit + found_rows() 可以使用在分頁(yè)的場(chǎng)合。

2> 不帶SQL_CALC_FOUND_ROW 的 found_rows() 可以使用在存儲(chǔ)過(guò)程中判斷前面的select是否為空：

DELIMITER //
DROP PROCEDURE IF EXISTS loginandreg //

CREATE PROCEDURE loginandreg(
 OUT userId BIGINT,
 IN user_Pwd VARCHAR(32),
 IN user_MobileCode VARCHAR(16),
 IN user_RegIP VARCHAR(16)
)
BEGIN
IF EXISTS(SELECT * FROM Users u WHERE u.user_MobileCode=user_MobileCode) THEN
 SELECT u.userId INTO userId FROM Users u WHERE u.user_MobileCode=user_MobileCode AND u.user_Pwd=user_Pwd; 
 IF FOUND_ROWS() < 1 THEN
 SELECT -1 INTO userId;
 END IF;
ELSE 
 INSERT INTO Users(user_Pwd,user_MobileCode,user_Visibility,user_Level,user_RegTime,user_RegIP,user_Collecter,user_Collected)
 VALUES (user_Pwd,user_MobileCode,6,6,NOW(),user_RegIP,0,0);
 SELECT LAST_INSERT_ID() INTO userId;
END IF;

END //
DELIMITER ;

上面存儲(chǔ)過(guò)程中的：

SELECT u.userId INTO userId FROM Users u WHERE u.user_MobileCode=user_MobileCode AND u.user_Pwd=user_Pwd;

IF FOUND_ROWS() < 1 THEN

SELECT -1 INTO userId;

END IF;

就是一個(gè)很好的使用的例子。

這種存儲(chǔ)過(guò)程的場(chǎng)景中就可以使用 mysql 的 FOUND_ROWS() 替換 sql server 存儲(chǔ)過(guò)程中的 IF @@ROWCOUNT < 1 語(yǔ)句。

--------------------------------------------------------------------------------------------------------------------------

2. row-count() 函數(shù)

文檔地址：http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_row-count

一句話，row_count() 函數(shù)一般用于返回被 update, insert, delete 實(shí)際修改的行數(shù)。

In MySQL 5.6, ROW_COUNT() returns a value as follows:

DDL statements: 0. This applies to statements such as CREATE TABLE or DROP TABLE.

DML statements other than SELECT: The number of affected rows. This applies to statements such as UPDATE, INSERT, or DELETE (as before), but now also to statements such as ALTER TABLE and LOAD DATA INFILE.

SELECT: -1 if the statement returns a result set, or the number of rows “affected” if it does not. For example, for SELECT * FROM t1, ROW_COUNT() returns -1. For SELECT * FROM t1 INTO OUTFILE 'file_name', ROW_COUNT() returns the number of rows written to the file.

SIGNAL statements: 0.

For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is the number of rows “found”; that is, matched by the WHERE clause.

也就是說(shuō)對(duì)于update語(yǔ)句，row_count() 默認(rèn)返回的是實(shí)際被修改的行數(shù)；但是通過(guò)參數(shù)設(shè)置，也可以返回找到的行數(shù)(或者說(shuō)匹配的行數(shù)，受影響的行數(shù))，這樣設(shè)置就能兼容于Oracle ps/sql中 sql%rowcount 和 sql server 中的 @@RowCount。

但是 row_count() 的結(jié)果和 mysql 的JDBC driver的默認(rèn)行為卻是不一致的，mysql jdbc中的 Connection.getUpdateCount() 函數(shù)返回的是被找到的行數(shù)，而不是實(shí)際被修改的行數(shù)，如果要返回被實(shí)際修改的行，要使用存儲(chǔ)過(guò)程，相關(guān)鏈接說(shuō)明：

http://stackoverflow.com/questions/17544782/how-to-tell-number-of-rows-changed-from-jdbc-execution

http://mybatis-user.963551.n3.nabble.com/Return-number-of-changed-rows-td3888464.html#a3903155

http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html (這里包含了所有mysql jdbc 鏈接可設(shè)置的參數(shù))

useAffectedRows

Don't set the CLIENT_FOUND_ROWS flag when connecting to the server (not JDBC-compliant, will break most applications that rely on "found" rows vs. "affected rows" for DML statements), but does cause "correct" update counts from "INSERT ... ON DUPLICATE KEY UPDATE" statements to be returned by the server.

Default: false

Since version: 5.1.7

關(guān)于對(duì)mysql復(fù)制的影響：

Important

FOUND_ROWS() is not replicated reliably using statement-based replication. This function is automatically replicated using row-based replication.

Important

ROW_COUNT() is not replicated reliably using statement-based replication. This function is automatically replicated using row-based replication.

注意：found_rows() 和 row_count() 在基于 語(yǔ)句的復(fù)制 環(huán)境中是不可靠的，它們自動(dòng)使用 基于行的復(fù)制行為。

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com