13.5 被準備SQL語句的語法

05-18

13.5 被準備SQL語句的語法

來自專欄 mysql 官方文檔翻譯

參考官方文檔：

https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

MySQL 5.7提供了對伺服器端預處理語句的支持。此支持利用高效的客戶端/伺服器二進位協議。使用準備好的語句和佔位符來獲取參數值具有以下好處：

每次執行時解析語句的開銷都較小。通常情況下，資料庫應用程序處理大量幾乎相同的語句，只對WHERE查詢和刪除，SET更新和VALUES等子句中的文字或變數值進行更改。
防止SQL注入攻擊。參數值可以包含未轉義的SQL引號和分隔符字元。

--手動軟解析

在SQL 腳本中的準備的語句

預備語句的備選SQL介面可用。這個介面不如通過預先準備好的語句API使用二進位協議那樣高效，但不需要編程，因為它可直接在SQL級別使用：

當沒有可用的編程介面時，您可以使用它。
您可以從任何可以將SQL語句發送到要執行的伺服器的程序中使用它，例如mysql客戶端程序。
即使客戶端使用舊版本的客戶端庫，只要連接到運行MySQL 4.1或更高版本的伺服器即可使用它。

準備好的語句的SQL語法旨在用於以下情況：

在編寫代碼之前，測試預準備語句在應用程序中的工作方式
當您無法訪問支持它們的編程API時使用預準備語句。
使用預準備語句互動式解決應用程序問題。
創建一個測試用例，用準備好的語句重現問題，以便您可以提交錯誤報告。

PREPARE，EXECUTE和DEALLOCATE PREPARE語句

預準備語句的SQL語法基於三個SQL語句：

PREPARE準備一份執行語句
EXECUTE執行一個準備好的語句
DEALLOCATE PREPARE發布一個準備好的語句

下面的例子展示了準備計算三角形斜邊的兩種等價方法，給出了雙方的長度。

第一個例子展示了如何通過使用字元串文字來提供語句的文本來創建一個準備好的語句：

mysql> PREPARE stmt1 FROMSELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse;

mysql> SET @a = 3;

mysql> SET @b = 4;

mysql> EXECUTE stmt1 USING @a, @b;

+------------+

| hypotenuse |

+------------+

| 5 |

+------------+

mysql> DEALLOCATE PREPARE stmt1;

第二個例子是類似的，但將語句的文本作為用戶變數提供：

mysql> SET @s =SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse;

mysql> PREPARE stmt2 FROM @s;

mysql> SET @a = 6;

mysql> SET @b = 8;

mysql> EXECUTE stmt2 USING @a, @b;

+------------+

| hypotenuse |

+------------+

| 10 |

+------------+

mysql> DEALLOCATE PREPARE stmt2;

下面是一個附加示例，演示如何選擇要在運行時執行查詢的表，方法是將表的名稱存儲為用戶變數：

mysql> USE test;

mysql> CREATE TABLE t1 (a INT NOT NULL);

mysql> INSERT INTO t1 VALUES (4), (8), (11), (32), (80);

mysql> SET @table = t1;

mysql> SET @s = CONCAT(SELECT * FROM , @table);

mysql> PREPARE stmt3 FROM @s;

mysql> EXECUTE stmt3;

+----+

| a |

+----+

| 4 |

| 8 |

| 11 |

| 32 |

| 80 |

+----+

mysql> DEALLOCATE PREPARE stmt3;

準備好的聲明特定於創建它的會話。如果您在不取消分配先前準備好的語句的情況下終止會話，則伺服器會自動解除分配它。

準備好的聲明也是全球會議。如果您在已存儲的常式中創建預準備語句，則在存儲的常式結束時不會釋放它。

為了防止同時創建的準備語句過多，請設置max_prepared_stmt_count系統變數。要防止使用預準備語句，請將該值設置為0。

準備好的語句中允許的SQL語法

從MySQL 5.7.2開始，為了遵守聲明診斷語句不可預備的SQL標準，MySQL不支持以下為預處理語句：

SHOW WARNINGS, SHOW COUNT(*) WARNINGS
SHOW ERRORS, SHOW COUNT(*) ERRORS
包含對warning_count或error_count系統變數的任何引用的語句。

其他語句在MySQL 5.7中不受支持。

一般來說，SQL準備語句中不允許的語句在存儲的程序中也是不允許的。

對準備好的語句引用的表或視圖的元數據更改會被檢測到，並在下次執行時導致語句自動重新編譯。

使用預處理語句時，佔位符可用於LIMIT子句的參數。

在與PREPARE和EXECUTE一起使用的準備好的CALL語句中，對於OUT和INOUT參數的佔位符支持可以從MySQL 5.7開始。有關早期版本的示例和解決方法，請參見第13.2.1節「CALL語法」。無論版本如何，佔位符均可用於IN參數。

預處理語句的SQL語法不能以嵌套方式使用。也就是說，傳遞給PREPARE的語句本身不能是PREPARE，EXECUTE或DEALLOCATE PREPARE語句。

預處理語句的SQL語法與使用預處理語句API調用截然不同。例如，不能使用mysql_stmt_prepare（）C API函數來準備PREPARE，EXECUTE或DEALLOCATE PREPARE語句。

預編譯語句的SQL語法可以在存儲過程中使用，但不能在存儲函數或觸發器中使用。但是，游標不能用於使用PREPARE和EXECUTE準備並執行的動態語句。游標創建時檢查游標的語句，因此語句不能是動態的。

預準備語句的SQL語法不支持多語句（即，由單個字元串中的多個語句分隔;字元）。

要編寫使用CALL SQL語句執行包含預處理語句的存儲過程的C程序，必須啟用CLIENT_MULTI_RESULTS標誌。這是因為除了過程中執行的語句可能返回的任何結果集外，每個CALL都會返回一個結果來指示調用狀態。

在調用mysql_real_connect（）時，可以啟用CLIENT_MULTI_RESULTS，或者顯式地通過傳遞CLIENT_MULTI_RESULTS標誌本身，或者通過傳遞CLIENT_MULTI_STATEMENTS（它也啟用CLIENT_MULTI_RESULTS）來隱式調用。

13.5.1 PREPARE 語法

PREPARE stmt_name FROM preparable_stmt

PREPARE語句準備一條SQL語句並為其指定一個名稱stmt_name，稍後通過該名稱來引用該語句。準備好的語句使用EXECUTE執行和DEALLOCATE PREPARE釋放。

語句名稱不區分大小寫。 preparable_stmt是一個字元串文字或包含用戶變數的SQL語句文本。文本必須代表單一語句，而不是多個語句。在語句中，？字元可以用作參數標記來指示稍後在執行時將數據值綁定到查詢的位置。這個？字元不應包含在引號內，即使您打算將它們綁定到字元串值。參數標記只能用於出現數據值的地方，而不能用於SQL關鍵字，標識符等等。

如果具有給定名稱的準備好的語句已經存在，則在準備新語句之前隱式地釋放它。這意味著如果新語句包含錯誤並且無法準備，並會返回錯誤，並且不存在具有給定名稱的語句。

準備好的語句的範圍是創建它的會話，其中包含以下幾個含義：

在一個會話中創建的預備語句不適用於其他會話。
當會話結束時，無論是正常還是異常，其已準備好的語句都不再存在。如果啟用自動重新連接，則不會通知客戶端連接丟失。出於這個原因，客戶可能希望禁用自動重新連接。
在存儲程序中創建的預備語句在程序執行完成後繼續存在，並且可以稍後在程序之外執行。
在存儲的程序上下文中準備的語句不能引用存儲過程或函數參數或局部變數，因為它們在程序結束時超出範圍，並且將在程序之後稍後執行的語句不可用。作為一種解決方法，請參閱用戶定義的變數，該變數也具有會話範圍;

13.5.2 EXECUTE 語法

EXECUTE stmt_name [USING @var_name [, @var_name] ...]

在使用PREPARE準備語句之後，使用引用準備好的語句名稱的EXECUTE語句來執行它。如果準備的語句包含任何參數標記，則必須提供一個USING子句，其中列出了包含要綁定到參數的值的用戶變數。參數值只能由用戶變數提供，並且USING子句必須與語句中參數標記的數目完全相同。

您可以多次執行給定的預處理語句，將不同的變數傳遞給它，或者在每次執行之前將變數設置為不同的值。

13.5.3 DEALLOCATE 語法

{DEALLOCATE | DROP} PREPARE stmt_name

要取消分配使用PREPARE生成的預備語句，請使用引用準備好的語句名稱的DEALLOCATE PREPARE語句。試圖在釋放它之後執行預備語句會導致錯誤。如果創建的準備語句過多，而且未通過DEALLOCATE PREPARE語句或會話結束釋放，則可能會遇到由max_prepared_stmt_count系統變數強制執行的上限。