概述

今天主要分享下MySQL的SQL预处理方面内容，也是属于优化必须会的一方面，建议大家都能掌握。

一、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 hwb\';

mysql> SET @a = 3;

mysql> SET @b = 4;

mysql> EXECUTE stmt1 USING @a, @b;

mysql> DEALLOCATE PREPARE stmt1;

2、利用变数定义预处理 SQL (直角三角形计算)

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

mysql> PREPARE stmt2 FROM @s;

mysql> SET @c = 6;

mysql> SET @d = 8;

mysql> EXECUTE stmt2 USING @c, @d;

mysql> DEALLOCATE PREPARE stmt2;

3、解决无法传参问题

我们知道，对于 LIMIT 子句中的值，必须是常量，不得使用变数，也就是说不能使用：SELECT * FROM TABLE LIMIT @skip, @numrows; 如此，这里就可以用 PREPARE 语句解决此问题。

mysql> SET @skip = 1;

mysql> SET @numrows = 3;

mysql> SELECT host,user FROM mysql.user LIMIT @skip, @numrows;

mysql> PREPARE stmt3 FROM "SELECT host,user FROM mysql.user LIMIT ?, ?";

mysql> EXECUTE stmt3 USING @skip, @numrows;

mysql> DEALLOCATE PREPARE stmt3;

如此一来，结合2中介绍的利用变数定义预处理 SQL 也就基本解决了传参时语法报错问题了，类似的：用变数传参做表名时，MySQL 会把变数名当做表名，这样既不是本意，也会是语法错误，在 SQL Server 的解决办法是利用字串拼接穿插变数进行传参，再将整条 SQL 语句作为变数，最后是用 sp_executesql 呼叫该拼接 SQL 执行，而 Prepared SQL Statement 可谓异曲同工之妙。

mysql> SET @table = \'mysql.user\';

mysql> SET @s = CONCAT(\'SELECT HOST,USER FROM \', @table);

mysql> PREPARE stmt4 FROM @s;

mysql> EXECUTE stmt4;

mysql> DROP PREPARE stmt4;