mysql 预编译_预编译语句(Prepared Statements)介绍，以MySQL为例

1. 背景

本文重点讲述MySQL中的预编译语句并从MySQL的Connector/J源码出发讲述其在Java语言中相关使用。

注意：文中的描述与结论基于MySQL 5.7.16以及Connect/J 5.1.42版本。

2. 预编译语句是什么

通常我们的一条sql在db接收到最终执行完毕返回可以分为下面三个过程：

词法和语义解析

优化sql语句，制定执行计划

执行并返回结果

我们把这种普通语句称作Immediate Statements。

但是很多情况，我们的一条sql语句可能会反复执行，或者每次执行的时候只有个别的值不同(比如query的where子句值不同，update的set子句值不同,insert的values值不同)。

如果每次都需要经过上面的词法语义解析、语句优化、制定执行计划等，则效率就明显不行了。

所谓预编译语句就是将这类语句中的值用占位符替代，可以视为将sql语句模板化或者说参数化，一般称这类语句叫Prepared Statements或者Parameterized Statements

预编译语句的优势在于归纳为：一次编译、多次运行，省去了解析优化等过程；此外预编译语句能防止sql注入。

当然就优化来说，很多时候最优的执行计划不是光靠知道sql语句的模板就能决定了，往往就是需要通过具体值来预估出成本代价。

3. MySQL的预编译功能

注意MySQL的老版本(4.1之前)是不支持服务端预编译的，但基于目前业界生产环境普遍情况，基本可以认为MySQL支持服务端预编译。

下面我们来看一下MySQL中预编译语句的使用。

首先我们有一张测试表t，结构如下所示：

mysql> show create table t\G

*************************** 1. row ***************************

Table: t

Create Table: CREATE TABLE `t` (

`a` int(11) DEFAULT NULL,

`b` varchar(20) DEFAULT NULL,

UNIQUE KEY `ab` (`a`,`b`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

3.1 编译

我们接下来通过 PREPARE stmt_name FROM preparable_stm的语法来预编译一条sql语句

mysql> prepare ins from 'insert into t select ?,?';

Query OK, 0 rows affected (0.00 sec)

Statement prepared

3.2 执行

我们通过EXECUTE stmt_name [USING @var_name [, @var_name] ...]的语法来执行预编译语句

mysql> set @a=999,@b='hello';

Query OK, 0 rows affected (0.00 sec)

mysql> execute ins using @a,@b;

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from t;

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

| a | b |

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

| 999 | hello |

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

1 row in set (0.00 sec)

可以看到，数据已经被成功插入表中。

MySQL中的预编译语句作用域是session级，但我们可以通过max_prepared_stmt_count变量来控制全局最大的存储的预编译语句。

mysql> set @@global.max_prepared_stmt_count=1;

Query OK, 0 rows affected (0.00 sec)

mysql> prepare sel from 'select * from t';

ERROR 1461 (42000): Can't create more than max_prepared_stmt_count statements (current value: 1)

当预编译条数已经达到阈值时可以看到MySQL会报如上所示的错误。

3.3 释放

如果我们想要释放一条预编译语句，则可以使用{DEALLOCATE | DROP} PREPARE stmt_name的语法进行操作:

mysql> deallocate prepare ins;

Query OK, 0 rows affected (0.00 sec)

4. 通过MySQL驱动进行预编译

以上介绍了直接在MySQL上通过sql命令进行预编译/缓存sql语句。接下来我们以MySQL Java驱动Connector/J(版本5.1.42)为例来介绍通过MySQL驱动进行预编译。

4.1 客户端预编译

首先，简要提一下JDBC中java.sql.PreparedStatement是java.sql.Statement的子接口,它主要提供了无参数执行方法如executeQuery和executeUpdate等，以及大量形如set{Type}(int, {Type})形式的方法用于设置参数。

在Connector/J中，java.sql.connection的底层实现类为com.mysql.jdbc.JDBC4Connection，它的类层次结构如下图所示：

下面是我编写如下测试类，程序中做的事情很简单，就是往test.t表中插入一条记录。

test.t表的结构在上述服务端预编译语句中已经有展示，此处不再赘述。

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

/**

* Test for PreparedStatement.

*

* @author Robin Wang

*/

public class PreparedStatementTest {

pu