mysql并發寫入，mysql foundrows 并發_MySQL 中的 FOUND_ROWS() 函數

標簽：

移植sql server 的存儲過程到mysql中，遇到了sql server中的：

IF @@ROWCOUNT < 1

對應到mysql中可以使用 FOUND_ROWS() 函數來替換。

1. found_rows() 函數的文檔：

mysql并發寫入？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();

MySQL having，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語句返回的行數。

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創建表？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,也沒有帶 limit):

如果前面的select語句沒有帶 SQL_CALC_FOUND_ROWS，也沒有帶 limit ，那么后面的?SELECT FOUND_ROWS(); 返回的結果就是前面的select返回的行數；

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

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

mysql_query。這里第一個select found_rows() 返回105，因為他是從偏移100的地方，再掃描5行，所以返回105；但是第二個掃描的結果為空，select found_rows()返回了0！而不是105，因為 where userId=999999的結果為空，所以后面的 limit 100,5根本就沒有執行。所以select found_rows()返回了0。

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

上面sql中 user_Pwd=xx 的值都是一樣的。可以看到這種情況下的found_rows() 是對的select語句的中間結果，再 limit 時，此時的limit的掃描到的最大的行數。和原始表中的數據的行數，是沒有關系的。他是對select的中間結果的limit，然后才得到最后的結果集，再返回。

3)SQL_CALC_FOUND_ROWS and FOUND_ROWS() 適合使用的場景

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() 可以使用在分頁的場合。

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

DELIMITER //

DROP PROCEDURE IF EXISTS loginandreg //

CREATE PROCEDUREloginandreg(

OUT userIdBIGINT,IN user_Pwd VARCHAR(32),IN user_MobileCode VARCHAR(16),IN user_RegIP VARCHAR(16)

)BEGIN

MySQL select、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 INTOuserId;END IF;ELSE

INSERT INTOUsers(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() INTOuserId;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

mysql。SELECT -1 INTOuserId;END IF;

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

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

標簽：