mysql prestatement_MySQL之Prepared Statements

1.概述

prepared statement在MySQL4.1中引进并且增加了一些新的命令：

COM_STMT_PREPARE

COM_STMT_EXECUTE

COM_STMT_CLOSE

COM_STMT_RESET

COM_STMT_SEND_LONG_DATA

它还定义了一个更紧凑简洁的结果集格式代替ProtocolText::Resultset来返回结果集。

记住不是所有的语句都是可以预处理的：

1.1 预处理说明

sql预处理首先要求客户端提交需要执行的sql，这时提交的sql不传递真实的参数值，参数以问号的形式传递过去。

eg:

insert into user(id, name) values(?, ?);select * from user where id = ?;

在mysql服务端完成预编译解析，这里的预编译解析包括解析参数的个数、类型等，然后响应给客户端。接下来客户端第二次交互只需传递参数过去就可以完成一个完整的sql的执行。相比传统的sql执行，预处理需要两次交互，才能完成一次sql执行。

预处理的优势：

(1)预处理sql能一定程度上防止sql注入

(2)sql预编译效率更高

(3)二进制包协议让sql预处理更加高效。

mysql预处理命令参数的封装以及结果集的返回，均采用二进制格式封装数据，体积更小，面向底层，能直接被mysql服务端利用。相比普通sql文本协议传输的数据，二进制协议传输数据更加高效。

2.二进制协议结果集

二进制协议结果集类似ProtocolText::Resultset.它仅包含二进制协议结果集行格式。

ProtocolBinary::Resultset:

Packet:

lenenc_int column_cout > 0

column_count * Protocol::ColumnDefinition

没有或者很多ProtocolBinary::ResultsetRow

EOF_Packet

注意：如果CLIENT_DEPRECATE_EOF客户端性能标志被设置，发送OK_Packet，否则发送EOF_Packet。

例如：

3. 二进制协议结果集行

3.1 NULL-Bitmap

二进制协议结果集行由NULL位图组成，该位图包含与结果集+ 2中的列一样多的位以及二进制协议值格式中非NULL的列的值。

ProtocolBinary::ResultsetRow:

3.2 二进制结果集的行(COM_STMT_EXECUTE)

payload:

1　　　　　　packet header[00]

string[$len]      NULL-bitmap,length: (列数 + 7 + 2 )/8

string[$len]      values

例子：

3.2.1 NULL-Bitmap

二进制协议将NULL值作为位发送到位图内而不是像ProtocolText :: ResultsetRow那样发送完整字节。 如果发送了许多NULL值，则它比旧方法更有效。

警告：

对于二进制协议结果集行，num-fields和field-pos需要添加2的偏移量。对于COM_STMT_EXECUTE，此偏移量为0。

NULL位图需要足够的空间来为发送的每个列存储可能的NULL位。 其空间计算如下：

NULL-bitmap-bytes = (num-fields + 7 + offset) / 8

导致：

4. 预处理命令说明

4.1 COM_STMT_PREPARE

COM_STMT_PREPARE命令用于客户端往服务端提交一个预处理的sql，如上面提到的：

1 insert into user(id, name) values(?, ?);

4.2 COM_STMT_EXECUTE

COM_STMT_EXECUTE用于执行预处理sql，正如前面说到的，如果预处理sql需要传递参数，这个命令会发送预处理语句所需要的参数到服务端。如上面的例子，需要传递两个参数id和user的具体值到服务端。

4.3 COM_STMT_CLOSE

COM_STMT_CLOSE用于关闭服务端预处理sql，每一个预处理预处理的sql提交后都保存在mysql服务端的内存当中，每个预处理sql都有一个唯一的id标识，这个命令将发送需要关闭的sql的id，通知服务端可以将所有该预处理sql的资源释放掉(过多的预处理sql保留在服务端会占用较多的内存，因此有必要执行该命令清理无用的预处理sql)。

4.4 COM_STMT_RESET

COM_STMT_RESET命令用于重置COM_STMT_SEND_LONG_DATA命令发送的blob数据。

4.5 COM_STMT_SEND_LONG_DATA

COM_STMT_SEND_LONG_DATA用于往服务端发送字节流数据，通常来说只有在发送blob字段数据才用到该命令。可以多次调用该命令连续传同一个字段的字节的数据，这个命令必须在COM_STMT_EXECUTE命令发送之前执行。

5. 预处理协议结果包说明

mysql预处理结果集采用了二进制协议包进行封装，与普通的查询结果集格式不同。(普通的结果集包采用文本协议包进行封装)。

5.1 普通查询结果集协议包

普通sql查询(相比预处理sql查询)返回的结果集包用文本协议(官方称为Text Protocol)封装。文本协议的结果集包格式根据官网的一个图来说明：

一个结果集包主要包括以下部分(顺序传输)：

one pakcet show field count(第一个packet用于表示返回结果集列数)

column defines packets(一个列就是一个packet, 格式参考Column Define Pakcet)

row packets(一行数据就是一个packet, 格式参考ResultsetRow Packet)

EOF Packet

5.2 预处理结果集协议包

预处理结果集包的组成和普通协议包类似，区别只在于row packet(数据以二进制协议格式存放)。

one packet show field count(第一个packet用于表示返回结果集列数)

column define packets(一列就是一个packet,格式参考普通协议包的Column Define Packet)

EOF Packet

binary row packets(一行数据一个packet，格式参考Binary Row Pakcet)

EOF Packet

说明：

Binary Row Packet的第一个字节恒为0， 表示paket header, 接下来，由NULL-Bitmap标识那些值为NULL的列，NULL-Bitmap的长度计算方式为(column-count +7 + 2)/8,其中column-count表示列数，而非空的列值以二进制协议格式(协议格式参考Binary Protocol Value)顺序存储在NULL-Bitmap的后面。

提示：

返回相同结果行，预处理协议包所占字节比普通协议包小，在列数越多，列越长的情况下，相差的大小越明显。

5.3 mysql jdbc 预处理

java.sql.preparestatement可以执行预处理sql，mysql jdbc实现了该接口，并且将预处理分为客户端和服务端预处理

5.3.1 jdbc客户端预处理

mysql jdbc默认情况下采用的就是客户端预处理。客户端预处理的意思是，所有预处理参数都将被缓存在mysql jdbc层，而不是缓存在mysql server。在PrepareStatement执行的时候，在jdbc端完成sql语句的拼接(主要是使用缓存的参数对sql中问号?进行替换， 最终发送到mysql的就是完整的sql语句)。客户端预处理走得是普通的查询协议，而不是真正的mysql预处理协议。

6 Mycat预处理实现机制

Mycat也实现了mysql的预处理协议，可以接收预处理命令的处理。当使用预处理查询，也可以返回正确的二进制结果集包。Mycat预处理的实现是一种取巧的设计，查询走到后端mysql实际上不是发送了预处理命令，而是普通的COM_QUERY命令，后端mysql返回给Mycat的结果集包也是文本协议包，只是在Mycat将结果集包发送往客户端的中间过程，将普通的文本协议结果集包包装成为二进制协议结果集包，然后再返回给客户端。

Mycat预处理的处理流程：

(1)Mycat接收到客户端发送的COM_STMT_PREPARE命令后，解析协议包的内容得到预处理sql语句，eg：insert into user(id, name)value(?, ?),将这些预处理语句缓存在Mycat里面；

(2)当Mycat再次接收到客户端发送的COM_STMT_EXECUTE命令，就把相应的问号替换为实际传递过来的参数值，这时候已经得到了完整的sql语句。

(3)接下来，直接把这个语句丢给Mycat sql查询处理器去执行，中间会经过sql解析模块，路由解析模块以及最后的执行。

(4)最后，当收到后端mysql传递给Mycat的数据准备发往客户端的时候，做一个协议转换，将普通文本结构集协议包转换为二进制结果集协议包并发往客户端。