MySQL中的遊標和綁定變數是什麼-mysql教程-PHP中文網

一、MySQL遊標簡介

MySQL在伺服器端提供唯讀的、單向的遊標，而且只能在預存程序或更底層的客戶端API中使用。

MySQL遊標是唯讀的，因為它指向的物件儲存在臨時表中而非實際查詢到的資料。它可以逐行指向查詢結果，然後讓程式做進一步的處理。在一個預存程序中，遊標可以被多次使用並且可以被「嵌套」在循環結構中。

MySQL的遊標設計也為粗心的人「準備」了陷阱。因為是使用臨時表實現的，所以它在效率上給開發人員一個錯覺。開啟遊標時需要執行整個查詢是最重要的要注意的事項。

考慮下面的預存程序：

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

登入後複製

這個例子表明，遊標在處理未完成的資料時可以立即關閉。使用Oracle或SQL Server的使用者不會認為這個預存程序有什麼問題，但在MySQL中，這會帶來很多不必要的額外的操作。使用SHOW STATUS來診斷這個預存過程，可以看到它需要做1000個索引頁的讀取，做1000個寫入。在開啟遊標動作的第五行發生了1000次讀寫操作，原因在於表sakila.film中有1000筆記錄。

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

使用遊標可能會導致MySQL執行一些額外的低效率I/O操作。因為臨時記憶體表不支援BLOB和TEXT類型，如果遊標傳回的結果包含這樣的列的話，MySQL就必須建立臨時磁碟表來存放，這樣效能可能會很糟。即便沒有該列，當臨時表超過tmp_table_size大小時，MySQL仍會在磁碟上建立臨時表。

雖然MySQL不支援客戶端遊標，但可以透過客戶端API快取所有查詢結果來模擬遊標。這和直接將結果放在一個記憶體數組中來維護並沒有什麼不同。

二、綁定變數

從MySQL 4.1版本開始，就支援伺服器端的綁定變數（prepared statement），這大大提高了客戶端和伺服器端資料傳輸的效率。如果你使用支援新協定的客戶端，例如MySQL CAPI，就可以使用綁定變數功能。另外，Java和.NET的也都可以使用各自的客戶端Connector/J和Connector/NET來使用綁定變數。

最後，還有一個SQL介面用來支援綁定變量，後面我們將討論這個（這裡容易引起困擾）。

客戶端會向伺服器傳送一份 SQL 語句的模板，以建立一個綁定變數的 SQL。伺服器端收到這個SQL語句框架後，解析並儲存這個SQL語句的部分執行計劃，傳回給客戶端一個SQL語句處理句柄。以後每次執行這類查詢，客戶端都會指定使用這個句柄。

綁定變數的SQL，使用問號標記可以接收參數的位置，當真正需要執行特定查詢的時候，則使用具體值來取代這些問號。例如，以下是一個綁定變數的SQL語句：

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

登入後複製

傳送該SQL句柄和各個問號參數取值到伺服器端，就能執行特定的查詢。重複使用這樣的方式執行具體的查詢，這正是綁定變數的優勢所在。具體如何傳送取值參數和SQL句柄，則和各個客戶端的程式語言有關。使用Java和.NET的MySQL連接器就是一種方法。很多使用MySQL C語言連結庫的客戶端可以提供類似的接口，需要根據使用的程式語言的文檔來了解如何使用綁定變數。

因為如下的原因，MySQL在使用綁定變數的時候可以更有效率地執行大量的重複語句：

1.在伺服器端只需要解析一次SQL語句。

2.在伺服器端某些優化器的工作只需要執行一次，因為它會快取一部分的執行計劃。

以二進位的方式只傳送參數和句柄，比起每次都發送ASCII碼文字效率更高，一個二進位的日期欄位只需要三個字節，但如果是ASCII碼則需要十個位元組。透過使用綁定變數的形式，BLOB和TEXT欄位可以分塊傳輸，從而達到最大的節省。這樣就不需要一次傳輸了。二進位協定在客戶端也可能節省很多內存，減少了網路開銷，另外，還節省了將資料從儲存原始格式轉換成文字格式的開銷。

4.只是參數——而不是整個查詢語句——需要傳送到伺服器端，所以網路開銷會更小。

5.MySQL在儲存參數的時候，直接將其存放到快取中，不再需要在記憶體中多次複製。

綁定變數相對也更安全。在應用程式中不需要處理轉義，這樣可以變得更簡單，同時也能大幅降低SQL注入和攻擊的風險。（任何時候都不要信任使用者輸入，即使是使用綁定變數的時候。）

可以只在使用绑定变量的时候才使用二进制传输协议。如果使用常规的mysql_query()接口，则无法使用二进制传输协议。还有一些客户端让你使用绑定变量，先发送带参数的绑定SQL，然后发送变量值，但是实际上，这些客户端只是模拟了绑定变量的接口，最后还是会直接用具体值代替参数后，再使用mysql_query()发送整个查询语句。

2.1 绑定变量的优化

对使用绑定变量的SQL，MySQL能够缓存其部分执行计划，如果某些执行计划需要根据传入的参数来计算时，MySQL就无法缓存这部分的执行计划。根据优化器什么时候工作，可以将优化分为三类。

在本书编写的时候，下面的三点是适用的。

1.在准备阶段

服务器解析SQL语句，移除不可能的条件，并且重写子查询。

2.在第一次执行的时候

如果可能的话，服务器先简化嵌套循环的关联，并将外关联转化成内关联。

3.在每次SQL语句执行时

服务器做如下事情：

1)过滤分区。

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

3)移除常数表达式。

4)检测常量表。

5)做必要的等值传播。

6)分析和优化ref、range和索引优化等访问数据的方法。

7)优化关联顺序。

2.2 SQL接口的绑定变量

MySQL支持了SQL接口的绑定变量。不使用二进制传输协议也可以直接以SQL的方式使用绑定变量。下面案例展示了如何使用SQL接口的绑定变量：

MySQL中的遊標和綁定變數是什麼

当服务器收到这些SQL语句后，先会像一般客户端的链接库一样将其翻译成对应的操作。

这意味着你无须使用二进制协议也可以使用绑定变量。

正如你看到的，比起直接编写的SQL语句，这里的语法看起来有一些怪怪的。

那么，这种写法实现的绑定变量到底有什么优势呢？

最主要的用途就是在存储过程中使用。在MySQL 5.0版本中，就可以在存储过程中使用绑定变量，其语法和前面介绍的SQL接口的绑定变量类似。意思是在存储过程中可以创建和运行基于动态SQL语句的代码

“动态”是指可以通过灵活地拼接字符串等参数构建SQL语句。举个例子，下面这个存储过程可以在特定的数据库中执行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 = &#39;BASE TABLE&#39;;
DECLARE CONTINUE HANDLER FOR SQLSTATE &#39;02000&#39; 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 ;

登入後複製

可以这样调用这个存储过程：

mysql> CALL optimize_tables(&#39;sakila&#39;)

登入後複製

另一种实现存储过程中循环的办法是：

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;

登入後複製

REPEAT和其他循环结构最大的不同是，它在每次循环中都会检查两次循环条件。在这个例子中，因为循环条件检查的是一个整数判断，并不会有什么性能问题，如果循环的判断条件非常复杂的话，则需要注意这两者的区别。

像这样使用SQL接口的绑定变量拼接表名和库名是很常见的，这样的好处是无须使用任何参数就能完成SQL语句。由于库名和表名都是关键字，因此在绑定变量的二进制协议中无法将这两个参数化。LIMIT子句是另一个经常需要动态设置的，因为在二进制协议中无法将其参数化。

另外，编写存储过程时，SQL接口的绑定变量通常可以很大程度地帮助我们调试绑定变量，如果不是在存储过程中，SQL接口的绑定变量就不是那么有用了。因为SQL接口的绑定变量，它既没有使用二进制传输协议，也没有能够节省带宽，相反还总是需要增加至少一次额外网络传输才能完成一次查询。所有只有在某些特殊的场景下SQL接口的绑定变量才有用，比如当SQL语句非常非常长，并且需要多次执行的时候。