?

1> SQL_CALC_FOUND_ROW + limit + found_rows() 可以使用在分页的场合。

?

2> 不带SQL_CALC_FOUND_ROW 的 found_rows() 可以使用在存储过程中判断前面的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 ;

?

上面存储过程中的：

?

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;

就是一个很好的使用的例子。

?

这种存储过程的场景中就可以使用 mysql 的 FOUND_ROWS() 替换 sql server 存储过程中的 IF @@ROWCOUNT < 1 语句。

?

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

?

2. row-count() 函数

?

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

?

一句话，row_count() 函数一般用于返回被 update, insert, delete 实际修改的行数。

?

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.

?

也就是说对于update语句，row_count() 默认返回的是实际被修改的行数；但是通过参数设置，也可以返回找到的行数(或者说匹配的行数，受影响的行数)，这样设置就能兼容于 Oracle ?ps/sql中 sql%rowcount 和 sql server 中的 @@RowCount。

?

但是 row_count() 的结果和 mysql 的JDBC driver的默认行为却是不一致的，mysql jdbc中的 Connection.getUpdateCount() 函数返回的是被找到的行数，而不是实际被修改的行数，如果要返回被实际修改的行，要使用存储过程，相关链接说明：

?

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 链接可设置的参数)

?

?

?useAffectedRows

?