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)
- EOF Packet
- 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的数据准备发往客户端的时候，做一个协议转换，将普通文本结构集协议包转换为二进制结果集协议包并发往客户端。
