MySQL中的游標(biāo)和綁定變量是什么

這篇文章主要介紹“MySQL中的游標(biāo)和綁定變量是什么”，在日常操作中，相信很多人在MySQL中的游標(biāo)和綁定變量是什么問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL中的游標(biāo)和綁定變量是什么”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

一、MySQL游標(biāo)簡介

MySQL在服務(wù)器端提供只讀的、單向的游標(biāo)，而且只能在存儲過程或者更底層的客戶端API中使用。

因為MySQL游標(biāo)中指向的對象都是存儲在臨時表中而不是實際查詢到的數(shù)據(jù)，所以MySQL游標(biāo)總是只讀的。它可以逐行指向查詢結(jié)果，然后讓程序做進一步的處理。在一個存儲過程中，可以有多個游標(biāo)，也可以在循環(huán)中“嵌套”地使用游標(biāo)。

MySQL的游標(biāo)設(shè)計也為粗心的人“準備”了陷阱。因為是使用臨時表實現(xiàn)的，所以它在效率上給開發(fā)人員一個錯覺。需要記住的最重要的一點是：當(dāng)你打開一個游標(biāo)的時候需要執(zhí)行整個查詢。

考慮下面的存儲過程：

CREATE PROCEDURE bad_cursor()
BEGIN
DECLARE film_id INT;
DECLARE f CURSOR FOR SELECT film_id FROM sakila.film;
OPEN f;
FETCH f INTO film_id;
CLOSE f;
END

從這個例子中可以看到，不用處理完所有的數(shù)據(jù)就可以立刻關(guān)閉游標(biāo)。使用Oracle或者SQL Server的用戶不會認為這個存儲過程有什么問題，但是在MySQL中，這會帶來很多的不必要的額外操作。使用SHOW STATUS來診斷這個存儲過程，可以看到它需要做1000個索引頁的讀取，做1000個寫入。這是因為在表sakila.film中有1000條記錄，而所有這些讀和寫都發(fā)生在第五行的打開游標(biāo)動作。

這個案例告訴我們，如果在關(guān)閉游標(biāo)的時候你只是掃描一個大結(jié)果集的一小部分，那么存儲過程可能不僅沒有減少開銷，相反帶來了大量的額外開銷。這時，你需要考慮使用LIMIT來限制返回的結(jié)果集。

游標(biāo)也會讓MySQL執(zhí)行一些額外的I/O操作，而這些操作的效率可能非常低。因為臨時內(nèi)存表不支持BLOB和TEXT類型，如果游標(biāo)返回的結(jié)果包含這樣的列的話，MySQL就必須創(chuàng)建臨時磁盤表來存放，這樣性能可能會很糟。即使沒有這樣的列，當(dāng)臨時表大于tmp_table_size的時候，MyQL也還是會在磁盤上創(chuàng)建臨時表。

MySQL不支持客戶端的游標(biāo)，不過客戶端API可以通過緩存全部查詢結(jié)果的方式模擬客戶端的游標(biāo)。這和直接將結(jié)果放在一個內(nèi)存數(shù)組中來維護并沒有什么不同。

二、綁定變量

從MySQL 4.1版本開始，就支持服務(wù)器端的綁定變量（prepared statement），這大大提高了客戶端和服務(wù)器端數(shù)據(jù)傳輸?shù)男?。你若使用一個支持新協(xié)議的客戶端，如MySQL CAPI，就可以使用綁定變量功能了。另外，Java和.NET的也都可以使用各自的客戶端Connector/J和Connector/NET來使用綁定變量。

最后，還有一個SQL接口用于支持綁定變量，后面我們將討論這個（這里容易引起困擾）。

當(dāng)創(chuàng)建一個綁定變量SQL時，客戶端向服務(wù)器發(fā)送了一個SQL語句的原型。服務(wù)器端收到這個SQL語句框架后，解析并存儲這個SQL語句的部分執(zhí)行計劃，返回給客戶端一個SQL語句處理句柄。以后每次執(zhí)行這類查詢，客戶端都指定使用這個句柄。

綁定變量的SQL，使用問號標(biāo)記可以接收參數(shù)的位置，當(dāng)真正需要執(zhí)行具體查詢的時候，則使用具體值代替這些問號。例如，下面是一個綁定變量的SQL語句：

INSERT INTO tbl(col1, col2, col3) VALUES (?, ?, ?);

可以通過向服務(wù)器端發(fā)送各個問號的取值和這個SQL的句柄來執(zhí)行一個具體的查詢。反復(fù)使用這樣的方式執(zhí)行具體的查詢，這正是綁定變量的優(yōu)勢所在。具體如何發(fā)送取值參數(shù)和SQL句柄，則和各個客戶端的編程語言有關(guān)。使用Java和.NET的MySQL連接器就是一種辦法。很多使用MySQL C語言鏈接庫的客戶端可以提供類似的接口，需要根據(jù)使用的編程語言的文檔來了解如何使用綁定變量。

因為如下的原因，MySQL在使用綁定變量的時候可以更高效地執(zhí)行大量的重復(fù)語句：

1.在服務(wù)器端只需要解析一次SQL語句。

2.在服務(wù)器端某些優(yōu)化器的工作只需要執(zhí)行一次，因為它會緩存一部分的執(zhí)行計劃。

• 以二進制的方式只發(fā)送參數(shù)和句柄，比起每次都發(fā)送ASCII碼文本效率更高，一個二進制的日期字段只需要三個字節(jié)，但如果是ASCII碼則需要十個字節(jié)。不過最大的節(jié)省還是來自于BLOB和TEXT字段，綁定變量的形式可以分塊傳輸，而無須一次性傳輸。二進制協(xié)議在客戶端也可能節(jié)省很多內(nèi)存，減少了網(wǎng)絡(luò)開銷，另外，還節(jié)省了將數(shù)據(jù)從存儲原始格式轉(zhuǎn)換成文本格式的開銷。

4.僅僅是參數(shù)——而不是整個查詢語句——需要發(fā)送到服務(wù)器端，所以網(wǎng)絡(luò)開銷會更小。

5.MySQL在存儲參數(shù)的時候，直接將其存放到緩存中，不再需要在內(nèi)存中多次復(fù)制。

綁定變量相對也更安全。無須在應(yīng)用程序中處理轉(zhuǎn)義，一則更簡單了，二則也大大減少了SQL注入和攻擊的風(fēng)險。（任何時候都不要信任用戶輸入，即使是使用綁定變量的時候。）

可以只在使用綁定變量的時候才使用二進制傳輸協(xié)議。如果使用普通的mysql_query()接口則不會使用二進制傳輸協(xié)議。還有一些客戶端讓你使用綁定變量，先發(fā)送帶參數(shù)的綁定SQL，然后發(fā)送變量值，但是實際上，這些客戶端只是模擬了綁定變量的接口，最后還是會直接用具體值代替參數(shù)后，再使用mysql_query()發(fā)送整個查詢語句。

2.1 綁定變量的優(yōu)化

對使用綁定變量的SQL，MySQL能夠緩存其部分執(zhí)行計劃，如果某些執(zhí)行計劃需要根據(jù)傳入的參數(shù)來計算時，MySQL就無法緩存這部分的執(zhí)行計劃。根據(jù)優(yōu)化器什么時候工作，可以將優(yōu)化分為三類。

在本書編寫的時候，下面的三點是適用的。

1.在準備階段

• 服務(wù)器解析SQL語句，移除不可能的條件，并且重寫子查詢。

2.在第一次執(zhí)行的時候

• 如果可能的話，服務(wù)器先簡化嵌套循環(huán)的關(guān)聯(lián)，并將外關(guān)聯(lián)轉(zhuǎn)化成內(nèi)關(guān)聯(lián)。

3.在每次SQL語句執(zhí)行時

• 服務(wù)器做如下事情：

1)過濾分區(qū)。

2)如果可能的話，盡量移除COUNT()、MIN()和MAX()。

3)移除常數(shù)表達式。

4)檢測常量表。

5)做必要的等值傳播。

6)分析和優(yōu)化ref、range和索引優(yōu)化等訪問數(shù)據(jù)的方法。

7)優(yōu)化關(guān)聯(lián)順序。

2.2 SQL接口的綁定變量

MySQL支持了SQL接口的綁定變量。不使用二進制傳輸協(xié)議也可以直接以SQL的方式使用綁定變量。下面案例展示了如何使用SQL接口的綁定變量： 

當(dāng)服務(wù)器收到這些SQL語句后，先會像一般客戶端的鏈接庫一樣將其翻譯成對應(yīng)的操作。

這意味著你無須使用二進制協(xié)議也可以使用綁定變量。

正如你看到的，比起直接編寫的SQL語句，這里的語法看起來有一些怪怪的。

那么，這種寫法實現(xiàn)的綁定變量到底有什么優(yōu)勢呢？

最主要的用途就是在存儲過程中使用。在MySQL 5.0版本中，就可以在存儲過程中使用綁定變量，其語法和前面介紹的SQL接口的綁定變量類似。這意味，可以在存儲過程中構(gòu)建并執(zhí)行“動態(tài)”的SQL語句，這里的

“動態(tài)”是指可以通過靈活地拼接字符串等參數(shù)構(gòu)建SQL語句。例如，下面的示例存儲過程中可以針對某個數(shù)據(jù)庫執(zhí)行OPTIMIZE TABLE的操作：

DROP PROCEDURE IF EXISTS optimize_tables;
DELIMITER //
CREATE PROCEDURE optimize_tables(db_name VARCHAR(64))
BEGIN
DECLARE t VARCHAR(64);
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = db_name AND TABLE_TYPE = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
tables_loop: LOOP
FETCH c INTO t;
IF done THEN
LEAVE tables_loop;
END IF;
SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name, ".", t);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE c;
END//
DELIMITER ;

可以這樣調(diào)用這個存儲過程：

mysql> CALL optimize_tables('sakila')

另一種實現(xiàn)存儲過程中循環(huán)的辦法是：

REPEAT
FETCH c INTO t;
IF NOT done THEN
SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name, ".", t);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL done END REPEAT;

這兩種循環(huán)結(jié)構(gòu)最重要的區(qū)別在于：REPEAT會為每個循環(huán)檢查兩次循環(huán)條件。在這個例子中，因為循環(huán)條件檢查的是一個整數(shù)判斷，并不會有什么性能問題，如果循環(huán)的判斷條件非常復(fù)雜的話，則需要注意這兩者的區(qū)別。

像這樣使用SQL接口的綁定變量拼接表名和庫名是很常見的，這樣的好處是無須使用任何參數(shù)就能完成SQL語句。而庫名和表名都是關(guān)鍵字，在二進制協(xié)議的綁定變量中是不能將這兩部分參數(shù)化的。另一個經(jīng)常需要動態(tài)設(shè)置的就是LIMIT子句，因為二進制協(xié)議中也無法將這個值參數(shù)化。

另外，編寫存儲過程時，SQL接口的綁定變量通?？梢院艽蟪潭鹊貛椭覀冋{(diào)試綁定變量，如果不是在存儲過程中，SQL接口的綁定變量就不是那么有用了。因為SQL接口的綁定變量，它既沒有使用二進制傳輸協(xié)議，也沒有能夠節(jié)省帶寬，相反還總是需要增加至少一次額外網(wǎng)絡(luò)傳輸才能完成一次查詢。所有只有在某些特殊的場景下SQL接口的綁定變量才有用，比如當(dāng)SQL語句非常非常長，并且需要多次執(zhí)行的時候。

2.3 綁定變量的限制

關(guān)于綁定變量的一些限制和注意事項如下：

1.綁定變量是會話級別的，所以連接之間不能共用綁定變量句柄。同樣地，一旦連接斷開，則原來的句柄也不能再使用了。（連接池和持久化連接可以在一定程度上緩解這個問題。）

2.在MySQL 5.1版本之前，綁定變量的SQL是不能使用查詢緩存的。

3.并不是所有的時候使用綁定變量都能獲得更好的性能。如果只是執(zhí)行一次SQL，那么使用綁定變量方式無疑比直接執(zhí)行多了一次額外的準備階段消耗，而且還需要一次額外的網(wǎng)絡(luò)開銷。（要正確地使用綁定變量，還需要在使用完成后，釋放相關(guān)的資源。）

4.當(dāng)前版本下，還不能在存儲函數(shù)中使用綁定變量（但是存儲過程中可以使用）。

5.如果總是忘記釋放綁定變量資源，則在服務(wù)器端很容易發(fā)生資源“泄漏”。綁定變量 SQL總數(shù)的限制是一個全局限制，所以某一個地方的錯誤可能會對所有其他的線程都產(chǎn)生影響。

6.有些操作，如BEGIN，無法在綁定變量中完成。

不過使用綁定變量最大的障礙可能是：

它是如何實現(xiàn)以及原理是怎樣的，這兩點很容易讓人困惑。有時，很難解釋如下三種綁定變量類型之間的區(qū)別是什么：

1.客戶端模擬的綁定變量

• 客戶端的驅(qū)動程序接收一個帶參數(shù)的SQL，再將指定的值帶入其中，最后將完整的查詢發(fā)送到服務(wù)器端。

2.服務(wù)器端的綁定變量

• 客戶端使用特殊的二進制協(xié)議將帶參數(shù)的字符串發(fā)送到服務(wù)器端，然后使用二進制協(xié)議將具體的參數(shù)值發(fā)送給服務(wù)器端并執(zhí)行。

3.SQL接口的綁定變量

• 客戶端先發(fā)送一個帶參數(shù)的字符串到服務(wù)器端，這類似于使用PREPARE的SQL語句，然后發(fā)送設(shè)置參數(shù)的SQL，最后使用EXECUTE來執(zhí)行SQL。所有這些都使用普通的文本傳輸協(xié)議。 

到此，關(guān)于“MySQL中的游標(biāo)和綁定變量是什么”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>