MySQL?SQL預(yù)處理Prepared的語(yǔ)法實(shí)例與注意事項(xiàng)是怎樣的

本篇文章給大家分享的是有關(guān)MySQL SQL預(yù)處理Prepared的語(yǔ)法實(shí)例與注意事項(xiàng)是怎樣的，小編覺(jué)得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話(huà)不多說(shuō)，跟著小編一起來(lái)看看吧。

一、SQL 語(yǔ)句的執(zhí)行處理

1、即時(shí) SQL

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

　　1. 詞法和語(yǔ)義解析；

　　2. 優(yōu)化 SQL 語(yǔ)句，制定執(zhí)行計(jì)劃；

　　3. 執(zhí)行并返回結(jié)果；

如上，一條 SQL 直接是走流程處理，一次編譯，單次運(yùn)行，此類(lèi)普通語(yǔ)句被稱(chēng)作 Immediate Statements （即時(shí) SQL）。

2、預(yù)處理 SQL

但是，絕大多數(shù)情況下，某需求某一條 SQL 語(yǔ)句可能會(huì)被反復(fù)調(diào)用執(zhí)行，或者每次執(zhí)行的時(shí)候只有個(gè)別的值不同（比如 select 的 where 子句值不同，update 的 set 子句值不同，insert 的 values 值不同）。如果每次都需要經(jīng)過(guò)上面的詞法語(yǔ)義解析、語(yǔ)句優(yōu)化、制定執(zhí)行計(jì)劃等，則效率就明顯不行了。

所謂預(yù)編譯語(yǔ)句就是將此類(lèi) SQL 語(yǔ)句中的值用占位符替代，可以視為將 SQL 語(yǔ)句模板化或者說(shuō)參數(shù)化，一般稱(chēng)這類(lèi)語(yǔ)句叫Prepared Statements。

預(yù)編譯語(yǔ)句的優(yōu)勢(shì)在于歸納為：一次編譯、多次運(yùn)行，省去了解析優(yōu)化等過(guò)程；此外預(yù)編譯語(yǔ)句能防止 SQL 注入。

注意：

雖然可能是通過(guò)預(yù)處理 SQL 的方式一定程度的提高了效率，但是對(duì)于優(yōu)化而言，最優(yōu)的執(zhí)行計(jì)劃不是光靠 SQL 語(yǔ)句的模板化來(lái)實(shí)現(xiàn)的，往往還是需要通過(guò)具體值來(lái)預(yù)估出成本代價(jià)。

二、Prepared SQL Statement Syntax

MySQL 官方將 prepare、execute、deallocate 統(tǒng)稱(chēng)為 PREPARE STATEMENT。翻譯也就習(xí)慣的稱(chēng)其為預(yù)處理語(yǔ)句。

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

語(yǔ)法：

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

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

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、利用變量定義預(yù)處理 SQL (直角三角形計(jì)算)

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、解決無(wú)法傳參問(wèn)題

我們知道，對(duì)于 LIMIT 子句中的值，必須是常量，不得使用變量，也就是說(shuō)不能使用：SELECT * FROM TABLE LIMIT @skip, @numrows; 如此，就可以是用 PREPARE 語(yǔ)句解決此問(wèn)題。

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)

如此一來(lái)，結(jié)合2中介紹的利用變量定義預(yù)處理 SQL 也就基本解決了傳參時(shí)語(yǔ)法報(bào)錯(cuò)問(wèn)題了，類(lèi)似的：用變量傳參做表名時(shí)，MySQL 會(huì)把變量名當(dāng)做表名，這樣既不是本意，也會(huì)是語(yǔ)法錯(cuò)誤，在 SQL Server 的解決辦法是利用字符串拼接穿插變量進(jìn)行傳參，再將整條 SQL 語(yǔ)句作為變量，最后是用 sp_executesql 調(diào)用該拼接 SQL 執(zhí)行，而 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)

三、預(yù)處理 SQL 使用注意點(diǎn)

1、stmt_name 作為 preparable_stmt 的接收者，唯一標(biāo)識(shí)，不區(qū)分大小寫(xiě)。

2、preparable_stmt 語(yǔ)句中的 ? 是個(gè)占位符，所代表的是一個(gè)字符串，不需要將 ? 用引號(hào)包含起來(lái)。

3、定義一個(gè)已存在的 stmt_name ，原有的將被立即釋放，類(lèi)似于變量的重新賦值。

4、PREPARE stmt_name 的作用域是session級(jí)

可以通過(guò) max_prepared_stmt_count 變量來(lái)控制全局最大的存儲(chǔ)的預(yù)處理語(yǔ)句。

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

預(yù)處理編譯 SQL 是占用資源的，所以在使用后注意及時(shí)使用 DEALLOCATE PREPARE 釋放資源，這是一個(gè)好習(xí)慣。

四、Prepared Statements優(yōu)點(diǎn)

1.安全

Prepared Statements通過(guò)sql邏輯與數(shù)據(jù)的分離來(lái)增加安全，sql邏輯與數(shù)據(jù)的分離能防止普通類(lèi)型的sql注入攻擊（SQL injection attack）。

2.性能

Prepared Statements只語(yǔ)法分析一次，你初始話(huà)Prepared Statements時(shí)，mysql將檢查語(yǔ)法并準(zhǔn)備語(yǔ)句的運(yùn)行，當(dāng)你執(zhí)行query 多次時(shí)，這樣就不會(huì)在有額外的負(fù)擔(dān)了，如果，當(dāng)運(yùn)行query 很多次的時(shí)候（如：insert）這種預(yù)處理有很大的性能提高

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

以上就是MySQL SQL預(yù)處理Prepared的語(yǔ)法實(shí)例與注意事項(xiàng)是怎樣的，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見(jiàn)到或用到的。希望你能通過(guò)這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。