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)
}
复制代码

# Capabilities 字节数据中的低 2 位
Server Capabilities: 0xffff
.... .... .... ...1 = Long Password: Set
.... .... .... ..1. = Found Rows: Set
.... .... .... .1.. = Long Column Flags: Set
.... .... .... 1... = Connect With Database: Set
.... .... ...1 .... = Don't Allow database.table.column: Set
.... .... ..1. .... = Can use compression protocol: Set
.... .... .1.. .... = ODBC Client: Set
.... .... 1... .... = Can Use LOAD DATA LOCAL: Set
.... ...1 .... .... = Ignore Spaces before '(': Set
.... ..1. .... .... = Speaks 4.1 protocol (new flag): Set
.... .1.. .... .... = Interactive Client: Set
.... 1... .... .... = Switch to SSL after handshake: Set
...1 .... .... .... = Ignore sigpipes: Set
..1. .... .... .... = Knows about transactions: Set
.1.. .... .... .... = Speaks 4.1 protocol (old flag): Set
1... .... .... .... = Can do 4.1 authentication: Set
# Capabilities 字节数据中的高 2 位
Extended Server Capabilities: 0xc1ff
.... .... .... ...1 = Multiple statements: Set
.... .... .... ..1. = Multiple results: Set
.... .... .... .1.. = PS Multiple results: Set
.... .... .... 1... = Plugin Auth: Set
.... .... ...1 .... = Connect attrs: Set
.... .... ..1. .... = Plugin Auth LENENC Client Data: Set
.... .... .1.. .... = Client can handle expired passwords: Set
.... .... 1... .... = Session variable tracking: Set
.... ...1 .... .... = Deprecate EOF: Set
1100 000. .... .... = Unused: 0x60

+--------------------+---------------------+-----+
| 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         |
+--------------------+