MySQL是怎样通讯的？

package main

import "net"

func main()  {
	conn, err := net.Dial("tcp","127.0.0.1:3306")
	if err != nil {
		return
	}
	defer func(conn net.Conn) {
		err := conn.Close()
		if err != nil {

		}
	}(conn)
}

+--------------------+---------------------+-----+
| CHARACTER_SET_NAME | COLLATION_NAME      | ID  |
+--------------------+---------------------+-----+
| big5               | big5_chinese_ci     |   1 |
| dec8               | dec8_swedish_ci     |   3 |
| cp850              | cp850_general_ci    |   4 |
| hp8                | hp8_english_ci      |   6 |
| koi8r              | koi8r_general_ci    |   7 |
| latin1             | latin1_swedish_ci   |   8 |
| latin2             | latin2_general_ci   |   9 |
| swe7               | swe7_swedish_ci     |  10 |
| ascii              | ascii_general_ci    |  11 |
| ujis               | ujis_japanese_ci    |  12 |
| sjis               | sjis_japanese_ci    |  13 |
| hebrew             | hebrew_general_ci   |  16 |
| tis620             | tis620_thai_ci      |  18 |
| euckr              | euckr_korean_ci     |  19 |
| koi8u              | koi8u_general_ci    |  22 |
| gb2312             | gb2312_chinese_ci   |  24 |
| greek              | greek_general_ci    |  25 |
| cp1250             | cp1250_general_ci   |  26 |
| gbk                | gbk_chinese_ci      |  28 |
| latin5             | latin5_turkish_ci   |  30 |
| armscii8           | armscii8_general_ci |  32 |
| utf8               | utf8_general_ci     |  33 |
| ucs2               | ucs2_general_ci     |  35 |
| cp866              | cp866_general_ci    |  36 |
| keybcs2            | keybcs2_general_ci  |  37 |
| macce              | macce_general_ci    |  38 |
| macroman           | macroman_general_ci |  39 |
| cp852              | cp852_general_ci    |  40 |
| latin7             | latin7_general_ci   |  41 |
| cp1251             | cp1251_general_ci   |  51 |
| utf16              | utf16_general_ci    |  54 |
| utf16le            | utf16le_general_ci  |  56 |
| cp1256             | cp1256_general_ci   |  57 |
| cp1257             | cp1257_general_ci   |  59 |
| utf32              | utf32_general_ci    |  60 |
| binary             | binary              |  63 |
| geostd8            | geostd8_general_ci  |  92 |
| cp932              | cp932_japanese_ci   |  95 |
| eucjpms            | eucjpms_japanese_ci |  97 |
| gb18030            | gb18030_chinese_ci  | 248 |
| utf8mb4            | utf8mb4_0900_ai_ci  | 255 |
+--------------------+---------------------+-----+

4              capability flags, CLIENT_PROTOCOL_41 always set
4              max-packet size
1              character set
string[23]	   reserved (all [0])
string[NUL]    username


if capabilities & CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA {
  // 如果支持 CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA 标志就返回这些数据
  lenenc-int     length of auth-response
  string[n]      auth-response
} else if capabilities & CLIENT_SECURE_CONNECTION {
  // 如果支持 CLIENT_SECURE_CONNECTION 标志就返回这些数据
  1              length of auth-response
  string[n]      auth-response
} else {
  // 否则就返回这个数据
  string[NUL]    auth-response
}

if capabilities & CLIENT_CONNECT_WITH_DB {
  // 如果支持 CLIENT_CONNECT_WITH_DB 标志就返回这些数据
  string[NUL]    database
}

if capabilities & CLIENT_PLUGIN_AUTH {
  // 如果支持 CLIENT_PLUGIN_AUTH 标志就返回这些数据
  string[NUL]    auth plugin name
}

if capabilities & CLIENT_CONNECT_ATTRS {
  // 如果支持 CLIENT_CONNECT_ATTRS 标志就返回这些数据
  lenenc-int     length of all key-values
  lenenc-str     key
  lenenc-str     value
  if-more data in 'length of all key-values', more keys and value pairs
 }

SHA1( password ) XOR SHA1( "20-bytes random data from server" <concat> SHA1( SHA1( password ) ) )

int<1>			header				[00] or [fe] the OK packet header
int<lenenc>	affected_rows		受影响行数
int<lenenc>	last_insert_id		最后插入 ID

if capabilities & CLIENT_PROTOCOL_41 {
    int<2>		status_flags	状态标志
    int<2>		warnings		警告数
} elseif capabilities & CLIENT_TRANSACTIONS {
    int<2>		status_flags	Status Flags
}

if capabilities & CLIENT_SESSION_TRACK {
    string<lenenc>	info	人类可读的状态信息
    
    if status_flags & SERVER_SESSION_STATE_CHANGED {
        string<lenenc>	session_state_changes	会话状态信息
    }
    
} else {
    string<EOF>	info		人类可读的状态信息
}

int<1>		header				[ff] header of the ERR packet
int<2>		error_code			错误代码

if capabilities & CLIENT_PROTOCOL_41 {
  string<1>	sql_state_marker	SQL 状态的标记
  string<5>	sql_state			SQL 状态
}

string<EOF>	error_message		人类可读的错误信息

int<1>		header				[fe] EOF header

if capabilities & CLIENT_PROTOCOL_41 {
  int<2>	warnings			警告数
  int<2>	status_flags		状态标志
}

0f 00 00 00 	03 73 68 6f 77 20 64 61 74 61 62 61
73 65 73

// 字节长度计算方法见上面的 int<lenenc> 介绍
int<lenenc>     结果集中列（columns）的总数。

string<lenenc>      catalog				目录 (固定为 "def")
string<lenenc>      schema				数据库
string<lenenc>      table				虚拟表
string<lenenc>      org_table			源表
string<lenenc>      name				虚拟名称
string<lenenc>      org_name			源名称
string<lenenc>      length of fixed-length fields [0c]
2              		character set		字符集
4              		column length		字段的最大长度
1              		type				列类型
2              		flags				标志
1              		decimals			显示的小数位数
2              		filler [00] [00]	两个空占位符

if command was COM_FIELD_LIST {
    int<lenenc>     length of default-values
    string[$len]   	default values
}

type MySQLClient struct {
	conn net.Conn
	addr string
	username string
	password	string
}

func (m *MySQLClient) init()  {
	// 连接阶段
	handshake := m.startConn()
	m.sendHandshakeResponse41(handshake)
}


/*
	连接 MySQL 服务器
*/
func (m *MySQLClient) startConn() *HandshakeV10 {
	m.conn, _ = net.Dial("tcp",m.addr)
	initResp := make([]byte,1024)
	readLen, _ := m.conn.Read(initResp)
	return ReadHandShakeV10(initResp[:readLen])
}

/*
	解析初始握手包 HandShakeV10
*/
func ReadHandShakeV10(data []byte) *HandshakeV10 {
	index := 0
	var h = &HandshakeV10{}
	index+=4
	h.ProtocolVersion= int32(data[index])
	index++
	var serverVersion []byte
	for data[index]!=0 {
		serverVersion = append(serverVersion,data[index])
		index++
	}
	h.ServerVersion = string(serverVersion)
	index++
	connectByte := data[index:index+4]
	for i :=range connectByte{
		h.ConnectionId+=int32(connectByte[i])
	}
	index+=4
	var apdp1 []byte
	apdp1Byte := data[index:index+8]
	for i := range apdp1Byte {
		apdp1 = append(apdp1, apdp1Byte[i])
	}
	h.AuthPluginDataPart_1 = string(apdp1)
	index+=9
	// 能力低2位
	c_flag_low_1 := strings.Split(fmt.Sprintf("%b\n",data[index+1]),"")
	c_flag_low_2 := strings.Split(fmt.Sprintf("%b\n",data[index]),"")

	index+=2
	// 编码获取
	h.CharacterSet = int32(data[index])
	index++
	// 服务器状态
	index+=2
	// 能力高2位
	c_flag_up_1 := strings.Split(fmt.Sprintf("%b\n",data[index+1]),"")
	c_flag_up_2 := strings.Split(fmt.Sprintf("%b\n",data[index]),"")
	var capabilityFlags []string
	capabilityFlags = append(capabilityFlags,c_flag_up_1...)
	capabilityFlags = append(capabilityFlags,c_flag_up_2...)
	capabilityFlags = append(capabilityFlags,c_flag_low_1...)
	capabilityFlags = append(capabilityFlags,c_flag_low_2...)
	index+=2
	if strings.EqualFold("1",capabilityFlags[19]){
		h.AuthPluginDataLen= int32(data[index])
	}
	index++
	index+=10
	if strings.EqualFold("1",capabilityFlags[15]){
		p2Len := 13
		p2len1 := int(h.AuthPluginDataLen-8)
		if p2Len < p2len1 {
			p2Len = p2len1
		}
		h.AuthPluginDataPart_2 = string(data[index:index+p2Len])
		index+=p2Len
	}
	if strings.EqualFold("1",capabilityFlags[19]) {
		var authPlugName []byte
		for data[index] != 0 {
			authPlugName = append(authPlugName,data[index])
			index++
		}
		h.AuthPluginName = string(authPlugName)
	}

	return h
}
type HandshakeV10 struct {
	ProtocolVersion      int32  `protobuf:"varint,1,opt,name=protocol_version,json=protocolVersion,proto3" json:"protocol_version,omitempty"`
	ServerVersion        string `protobuf:"bytes,2,opt,name=server_version,json=serverVersion,proto3" json:"server_version,omitempty"`
	ConnectionId         int32  `protobuf:"varint,3,opt,name=connection_id,json=connectionId,proto3" json:"connection_id,omitempty"`
	AuthPluginDataPart_1 string `protobuf:"bytes,4,opt,name=auth_plugin_data_part_1,json=authPluginDataPart1,proto3" json:"auth_plugin_data_part_1,omitempty"`
	CharacterSet         int32  `protobuf:"varint,6,opt,name=character_set,json=characterSet,proto3" json:"character_set,omitempty"`
	StatusFlags          int32  `protobuf:"varint,7,opt,name=status_flags,json=statusFlags,proto3" json:"status_flags,omitempty"`
	AuthPluginDataLen    int32  `protobuf:"varint,8,opt,name=auth_plugin_data_len,json=authPluginDataLen,proto3" json:"auth_plugin_data_len,omitempty"`
	AuthPluginDataPart_2 string `protobuf:"bytes,9,opt,name=auth_plugin_data_part_2,json=authPluginDataPart2,proto3" json:"auth_plugin_data_part_2,omitempty"`
	AuthPluginName       string `protobuf:"bytes,10,opt,name=auth_plugin_name,json=authPluginName,proto3" json:"auth_plugin_name,omitempty"`
}

/*
	发送初始响应数据包 HandshakeResponse41，包含登陆信息
*/
func (m *MySQLClient) sendHandshakeResponse41(serverResp *HandshakeV10) {
	resp := make([]byte,0)
	resp = append(resp, Int32ToBytesOfLittle(19833351)...)
	resp = append(resp, Int32ToBytesOfLittle(16777215)...)
	resp = append(resp, 33)
	reserved := make([]byte,23)
	resp = append(resp, reserved...)
	resp = append(resp, []byte(m.username)...)
	resp = append(resp, 0)
	resp = append(resp, 20)
	resp = append(resp, CalcPassword([]byte(serverResp.AuthPluginDataPart_1+serverResp.AuthPluginDataPart_2)[:20],[]byte(m.password))...)
	resp = append(resp, []byte("mysql_native_password")...)
	resp = append(resp, 0)

	_, _ = m.conn.Write(Pack(resp,1))
	flag := m.handleResponse()
	if flag == 0xff {
		panic("连接失败")
	}
	return
}

/*
	解析通用响应数据包 OK_Packet、ERR_Packet、数据集
*/
func (m *MySQLClient) handleResponse() uint8 {
	resp := make([]byte,1024)
	readLen, _ := m.conn.Read(resp)
	data := resp[:readLen]
	data = data[4:]
	switch data[0] {
	case 0x00:
		fmt.Println("成功")
		return 0x00
	case 0xff:
		fmt.Println("失败")
		return 0xff
	default:
		parseResultSet(data)
		return 0xfe
	}
}

/*
	CommandQuery
	发送 COM_QUERY 命令，并读取数据
*/
func (m *MySQLClient) CommandQuery(sql string) {
	resp := make([]byte,0)
	resp = append(resp, 3)
	resp = append(resp, []byte(sql)...)
	_, _ = m.conn.Write(Pack(resp,0))
	m.handleResponse()
}

func parseResultSet(resp []byte)  {
	index := 0
	fieldLen := resp[0]
	index+=1
	headRows := make([]string,0)
	headIndex := 1
	// 读取列数据
	for headIndex <= int(fieldLen){
		n,l := readColumn(resp,index)
		index+=l
		headRows = append(headRows, n)
		headIndex++
	}
	table, err := gotable.Create(headRows...)
	if err != nil {
		fmt.Println("Create table failed: ", err.Error())
		return
	}

	// 读取行内容
	for  {
		// 判断是否是 EOF 数据包
		if resp[index+4] == 0xfe{
			packLen := 0
			for _,v :=range resp[index:index+3]{
				packLen+=int(v)
			}
			if packLen<9 {
				break
			}
		}
		rows,ll := readRow(resp,index, int(fieldLen))
		table.AddRow(rows)
		index+=ll
	}
	// 打印
	fmt.Println(table)
}
func readColumn(data []byte, startIndex int) (name string,length int) {
	packLen := data[startIndex:startIndex+3]
	for i :=range packLen{
		length+=int(packLen[i])
	}
	length += 4

	startIndex+=4
	startIndex+=int(data[startIndex]+1)
	startIndex+=int(data[startIndex]+1)
	startIndex+=int(data[startIndex]+1)
	startIndex+=int(data[startIndex]+1)
	nameLen := int(data[startIndex])
	name = string(data[startIndex+1:startIndex+nameLen+1])
	return
}
func readRow(data []byte, startIndex int, fieldNum int) (name []string,length int) {
	packLen := data[startIndex:startIndex+3]
	for i :=range packLen{
		length+=int(packLen[i])
	}
	length += 4
	startIndex+=4
	f:=0
	for f < fieldNum{
		dataLen := 0
		// 计算字节数据长度
		if data[startIndex] < 0xfb {
			// NULL
			dataLen = int(data[startIndex])
		}else if data[startIndex] == 0xfc {
			for _,v := range data[startIndex+1:startIndex+3]{
				dataLen+=int(v)
			}
		}else if data[startIndex] == 0xfd {
			for _,v :=range data[startIndex+1:startIndex+5]{
				dataLen+=int(v)
			}
		}else if data[startIndex] == 0xfe {
			for _,v :=range data[startIndex+1:startIndex+9]{
				dataLen+=int(v)
			}
		}
		name = append(name, string(data[startIndex+1:startIndex+dataLen+1]))
		startIndex += dataLen+1
		f++
	}
	return
}

+--------------------+
|      Database      |
+--------------------+
| information_schema |
|      greycode      |
|       mysql        |
| performance_schema |
|        sys         |
+--------------------+