源码分析mycat1.6之mysql通信协议篇之存储过程调用

来源：互联网发布：素描绘画软件编辑：程序博客网时间：2024/06/05 18:37

1、环境搭建

创建一个测试存储过程，支持入参与出参

DELIMITER &&

create PROCEDURE test_proc( in a_id int , out r_user_name varchar(50))

BEGIN

SELECT a.user_name INTO r_user_name

FROM acc_user a

WHERE a.id=a_id ;

END &&

DELIMITER ;

//执行存储过程

call test_proc(1, @username);

select @username;

其中@username定义一个变量，用来接收存储过程的出参，然后用select @username获取该参数。

// 官方实例代码

CREATE TEMPORARY TABLE ins ( id INT );DROP PROCEDURE IF EXISTS multi;DELIMITER $$CREATE PROCEDURE multi() BEGIN  SELECT 1;  SELECT 1;  INSERT INTO ins VALUES (1);  INSERT INTO ins VALUES (2);END$$DELIMITER ;

该例的解析，最终使用官方给出的存储过程调用。

2、存储过程命令发送以及报文格式

存储过过程的调用，命令与上文中ResultSet协议解析的命令是一样的，通用使用COM_QUERY命令类型（请求报文），服务端会返回多个ResultSet报文。

关于mysql通信协议多ResultSet结果集返回协议定义：https://dev.mysql.com/doc/internals/en/multi-resultset.html

多ResultSet结果集描述：

1）Multi-resultsets are sent up by stored procedures if more than one resultset was generated inside of it:

2）a resultset

see the EOF_Packet: 05 00 00 03 fe 00 00 0a 00 with its status-flag being0a

3） the 2nd resultset:

see the EOF_Packet: 05 00 00 0a fe 00 00 0a 00 with its status-flag being0a

4） nd a closing empty resultset, an OK_Packet:

If the SERVER_MORE_RESULTS_EXISTS flag is set, that indicates more resultsets will follow.

The trailing OK_Packet is the response to the CALL statement and contains the affected-rows count of the last statement. In our case we inserted 2 rows, but only theaffected_rows of the last INSERT statement is returned as part of the OK_Packet. If the last statement is a SELECT, the affected-rows count is 0.

As of MySQL 5.7.5, the resultset is followed by an OK_Packet, and this OK_Packet has theSERVER_MORE_RESULTS_EXISTS flag set to start processing the next resultset.

The client has to announce that it wants multi-resultsets by either setting theCLIENT_MULTI_RESULTS or CLIENT_PS_MULTI_RESULTS capability.

关于协议的解读

Multi-ResultSet报文响应结果：

1个ResultSet报文（其EOF中serverStatus字段中会展示是否有下一个ResultSet包） + 第n个ResultSet包(最后的一个ResultSet包的serverStatus字段同样指示还有下一个数据包) + OK包（该serverStatus的指示是否还有下一个包的状态设置为0），表示Multi-ResultSet包的结束。

命令请求包：

第一个ResultSet包

第二个ResultSet包

多ResultSet结束包：

完整源码地址：https://git.oschina.net/zhcsoft/StudyDemo ,包路径：persistent.prestige.console.mysql

核心代码类如下：

package persistent.prestige.console.mysql.protocol;import java.util.ArrayList;import java.util.List;import persistent.prestige.console.mysql.connection.Connection;/** * 注：本次解析，重在将select 查询出来的数据，使用List<Object[]>返回，甚至转换为List<Bean> *  * @author dingwei2 * */@SuppressWarnings("serial")public class ResultSetPacket extends Packet {    private static final int STATUS_NONE = 0; //未开始解析    private static final int STATUS_COLUMN = 1;//列信息解析中    private static final int STATUS_COLUMN_END = 2;//列信息解析完成    private static final int STATUS_ROWDATA = 4;//数据解析中    private static final int STATUS_END = 8;    //包解析结束    private Connection conn;    /** 列的长度 */    private int columnCount;    private List<ColumnDefinition41Packet> columnDefinition41Packets;    private List<Object[]> rowDatas;    private int status; // 0:未开始;1: 解析column definition;2:解析rowdata中 ,3:结束    private boolean hasNext = false; //是否有下一个ResultSet包    /** 响应包类型   1:OK包;2:Error包;3:LoadDataFile包;4:ResultSetData包*/    private int responseType;    public ResultSetPacket(Connection conn) {        this.conn = conn;        this.rowDatas = new ArrayList<Object[]>();//        this.columnCount = columnCount;//        columnDefinition41Packets = new ArrayList<ColumnDefinition41Packet>(columnCount);    }    /**     * 由于是演示代码，内存使用的是堆内存，故内存的管理交给了垃圾回收器     * @param msg     */    public void read(MysqlMessage msg) {        if(responseType < STATUS_COLUMN ) {//说明该包还是第一次解析，需要判断响应包的类型            int packetLen = msg.getPacketLength();            byte packetSeq = msg.getPacketSeq();            short pType = msg.getPTypeByFrom1Byte();            System.out.println("数据包类型:" + pType + ",数据实体长度:" + packetLen);            if(pType == 0xFf) { // Error Packet                ErrorPacket errorPacket = ErrorPacket.newInstance(msg, packetSeq, packetLen);                System.out.println(errorPacket);                conn.endCmd();                this.responseType = 2;                this.status = STATUS_END; //包解析结束                return;            } else if(pType == 0) { //OK Packet,,目前这里发的是EOF包                OkPacket ok = OkPacket.newInstance(msg, packetSeq, packetLen);                System.err.println(ok);                 conn.endCmd();                this.responseType = 1;                this.status = STATUS_END; //包解析结束                return;            } else if(pType == 0xFB) { // load_data_request 包                conn.endCmd();                this.responseType = 3;                this.status = STATUS_END; //包解析结束                return;            } else {                this.responseType = 4;                //判断是否是LengthCodeInt类型                try {                    long columnCount = msg.getBinaryLengthCode();                    System.out.println("字段长度：" + columnCount);                    this.columnCount = (int) columnCount;                    this.columnDefinition41Packets = new ArrayList<ColumnDefinition41Packet>(this.columnCount);                    this.status = STATUS_COLUMN; //column definition 解析中                } catch (UnsupportedOperationException e) {                    System.out.println("不是一个合法的LengthCodeBinary包");                    conn.endCmd();                    this.responseType = 4;                    this.status = STATUS_END; //包解析结束                    return;                }            }        }        //开始包的解析        if(status == STATUS_COLUMN) { //列信息解析            int i = 0;            while (msg.hasRemaining() && i++ < this.columnCount) {                System.out.println("正在解析第" + (this.columnDefinition41Packets.size() + 1 ) + "列");                this.columnDefinition41Packets.add( ColumnDefinition41Packet.newInstance(msg, false) );                }            if( this.columnDefinition41Packets.size() < this.columnCount) {  //列描述包未全部解析完，待下次数据的到来                return;            }            //列信息解析完，进入到 ResultData解析            this.status = STATUS_COLUMN_END;//列信息解析完成后，会发送一个新的mysql数据包,故本方法就会结束，因为上层调用方只会传入一个完整的数据包        } else if(status == STATUS_COLUMN_END ) { //这是一个OK包或EOF包，在这里，只需忽略掉这个包即可//            while(msg.hasRemaining()) {//                System.out.print(msg.byte2hex(msg.get()));//            }            this.status = STATUS_ROWDATA;        } else if( status == STATUS_ROWDATA) {            //需要判断该包是结束包，还是ResultData包//            while(msg.hasRemaining()) {//                System.out.print(msg.byte2hex(msg.get()));//            }            int packetLen = msg.getPacketLength();            byte packetSeq = msg.getPacketSeq();            short pType = msg.getPTypeByFrom1Byte();            //结尾需要判断一下是 EOF包，还是OK包，重点关注服务器状态字段，判断是有更多ResultSet            if(pType == 0xFE && packetLen < 9) { //EOF 包                //msg.skipReadBytes(packetLen); //跳过协议头部和整个EOF包,,这里不能，得解析 是否还有ResultSet,因为可能支持多ResultSet                //整个解析结束                EOFPacket packet = EOFPacket.newInstance(msg, packetSeq, packetLen);                if( (packet.getStatusFlags() & Packet.SERVER_MORE_RESULTS_EXISTS ) != 0 ) { //表明还有下一个                    this.hasNext = true;                }                 this.status = STATUS_END;            } else if (pType == 0x00 && packetLen >= 7) { // OK包                OkPacket packet = OkPacket.newInstance(msg, packetSeq, packetLen);                if( (packet.getStatusFlags() & Packet.SERVER_MORE_RESULTS_EXISTS ) != 0 ) { //表明还有下一个                    this.hasNext = true;                }                 this.status = STATUS_END;            } else {                while(msg.hasRemaining()) {                    rowDatas.add( ResultSetDataPacket.newInstance(columnDefinition41Packets, msg).values()  );                }            }        }    }    public boolean isEnd() {        return this.status == STATUS_END;    }    public int getColumnCount() {        return columnCount;    }    public void setColumnCount(int columnCount) {        this.columnCount = columnCount;    }    public List<ColumnDefinition41Packet> getColumnDefinition41Packets() {        return columnDefinition41Packets;    }    public void setColumnDefinition41Packets(List<ColumnDefinition41Packet> columnDefinition41Packets) {        this.columnDefinition41Packets = columnDefinition41Packets;    }    public List<Object[]> getRowDatas() {        return rowDatas;    }    public void setRowDatas(List<Object[]> rowDatas) {        this.rowDatas = rowDatas;    }    public int getStatus() {        return status;    }    public void setStatus(int status) {        this.status = status;    }    public boolean isHasNext() {        return hasNext;    }    public int getResponseType() {        return responseType;    }}

本文在上文ResultSet的解析的基础上，支持多ResultSet结果集的解析，以调用存储过程为例展示多ResultSet报文及解析。

0 0