移植sql server 的存储过程到mysql中,遇到了sql server中的:
IF @@ROWCOUNT < 1
对应到mysql中可以使用 FOUND_ROWS() 函数来替换。
1. found_rows() 函数
文档地址: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.
前面的带有limit的select语句如果加上了 SQL_CALC_FOUND_ROWS,那么接下来执行的 SELECT FOUND_ROWS(); 将返回前面语句不带limit语句返回的行数。
此种情况下,select found_rows() 和 select count(*) 有一个很小的区别:如果userId允许为null,那么select found_rows() 的结果可能要比select count(*) 要小一些。因为前者等价于:select count(userId) from Users; 而该语句不会计算userId 为null的行在内。而count(*)会计算在内。
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 incl