MySQL SQL預處理(Prepared)的語法實例與註意事項

Posted on by

一、SQL 語句的執行處理

1、即時 SQL

一條 SQL 在 DB 接收到最終執行完畢返回，大致的過程如下：

　　1. 詞法和語義解析；

　　2. 優化 SQL 語句，制定執行計劃；

　　3. 執行並返回結果；

如上，一條 SQL 直接是走流程處理，一次編譯，單次運行，此類普通語句被稱作 Immediate Statements （即時 SQL）。

2、預處理 SQL

但是，絕大多數情況下，某需求某一條 SQL 語句可能會被反復調用執行，或者每次執行的時候隻有個別的值不同（比如 select 的 where 子句值不同，update 的 set 子句值不同，insert 的 values 值不同）。如果每次都需要經過上面的詞法語義解析、語句優化、制定執行計劃等，則效率就明顯不行瞭。

所謂預編譯語句就是將此類 SQL 語句中的值用占位符替代，可以視為將 SQL 語句模板化或者說參數化，一般稱這類語句叫Prepared Statements。

預編譯語句的優勢在於歸納為：一次編譯、多次運行，省去瞭解析優化等過程；此外預編譯語句能防止 SQL 註入。

註意：

雖然可能是通過預處理 SQL 的方式一定程度的提高瞭效率，但是對於優化而言，最優的執行計劃不是光靠 SQL 語句的模板化來實現的，往往還是需要通過具體值來預估出成本代價。

二、Prepared SQL Statement Syntax

MySQL 官方將 prepare、execute、deallocate 統稱為 PREPARE STATEMENT。翻譯也就習慣的稱其為預處理語句。

MySQL 預處理語句的支持版本較早，所以我們目前普遍使用的 MySQL 版本都是支持這一語法的。

語法：

# 定義預處理語句
PREPARE stmt_name FROM preparable_stmt;
# 執行預處理語句
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
# 刪除(釋放)定義
{DEALLOCATE | DROP} PREPARE stmt_name;

 1、利用字符串定義預處理 SQL (直角三角形計算)

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @a = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = 4;                                                   
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt1;                                     
Query OK, 0 rows affected (0.00 sec)

 2、利用變量定義預處理 SQL (直角三角形計算)

mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt2 FROM @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @c = 6;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @d = 8;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt2 USING @c, @d;
+------------+
| hypotenuse |
+------------+
|         10 |
+------------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt2;
Query OK, 0 rows affected (0.00 sec)

 3、解決無法傳參問題

我們知道，對於 LIMIT 子句中的值，必須是常量，不得使用變量，也就是說不能使用：SELECT * FROM TABLE LIMIT @skip, @numrows; 如此，就可以是用 PREPARE 語句解決此問題。

mysql> SET @skip = 100; SET @numrows = 3;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t1 LIMIT @skip, @numrows;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@skip, @numrows' at line 1

mysql> PREPARE stmt3 FROM "SELECT * FROM t1 LIMIT ?, ?";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt3 USING @skip, @numrows;
+-----+--------+
| a   | filler |
+-----+--------+
| 100 | filler |
| 101 | filler |
| 102 | filler |
+-----+--------+
3 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt3;
Query OK, 0 rows affected (0.00 sec)

如此一來，結合2中介紹的利用變量定義預處理 SQL 也就基本解決瞭傳參時語法報錯問題瞭，類似的：用變量傳參做表名時，MySQL 會把變量名當做表名，這樣既不是本意，也會是語法錯誤，在 SQL Server 的解決辦法是利用字符串拼接穿插變量進行傳參，再將整條 SQL 語句作為變量，最後是用 sp_executesql 調用該拼接 SQL 執行，而 Prepared SQL Statement 可謂異曲同工之妙。

mysql> SET @table = 't2';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @s = CONCAT('SELECT * FROM ', @table);
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt4 FROM @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt4;
+------+-------+-------+
| id   | score | grade |
+------+-------+-------+
|    1 |    99 | A     |
|    2 |    81 | B     |
|    3 |    55 | D     |
|    4 |    69 | C     |
+------+-------+-------+
4 rows in set (0.00 sec)

mysql> DROP PREPARE stmt4;
Query OK, 0 rows affected (0.00 sec)

三、預處理 SQL 使用註意點

1、stmt_name 作為 preparable_stmt 的接收者，唯一標識，不區分大小寫。

2、preparable_stmt 語句中的 ? 是個占位符，所代表的是一個字符串，不需要將 ? 用引號包含起來。

3、定義一個已存在的 stmt_name ，原有的將被立即釋放，類似於變量的重新賦值。

4、PREPARE stmt_name 的作用域是session級

可以通過 max_prepared_stmt_count 變量來控制全局最大的存儲的預處理語句。

mysql> show variables like 'max_prepared%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| max_prepared_stmt_count | 16382 |
+-------------------------+-------+
1 row in set (0.00 sec)

預處理編譯 SQL 是占用資源的，所以在使用後註意及時使用 DEALLOCATE PREPARE 釋放資源，這是一個好習慣。

四、Prepared Statements優點

1.安全

Prepared Statements通過sql邏輯與數據的分離來增加安全，sql邏輯與數據的分離能防止普通類型的sql註入攻擊（SQL injection attack）。

2.性能

Prepared Statements隻語法分析一次，你初始話Prepared Statements時，mysql將檢查語法並準備語句的運行，當你執行query 多次時，這樣就不會在有額外的負擔瞭，如果，當運行query 很多次的時候（如：insert）這種預處理有很大的性能提高

他使用binary protocol協議，這樣更能提高效率。

總結

到此這篇關於MySQL SQL預處理(Prepared)的文章就介紹到這瞭,更多相關MySQL SQL預處理(Prepared)內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：