PG电子·(中国)官方网站

MySQL和PgSQL协议

解析与对比

神州数码集团

江坤

在实现一个数据库系统，客户端，或者中间件的时候，都需要关注数据库与客户端之间的通信，也就是数据库协议层的实现，现在业界内谈论的比较多的两种开源关系数据库就是MySQL和PostgreSQL，包括一些NewSQL的实现，也会向上兼容MySQL或者PostgreSQL，实现他们的协议层，但是这两个数据库的协议实现是完全不一样的，所以这也是为什么我们用两者的客户端无法连接另一个数据库的原因。

其实两者协议层处理，在双方的官方文档中都比较详细了，如果大家都是老手的话，可以直接上官网文档啃生肉：

PostgreSQL

http://www.postgresql.org/docs/current/protocol.html

MySQL

http://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_PROTOCOL.html

那么初学者，可以先看完这篇文章，有一定了解后，在啃官方文档会更快的吸收。话不多说，进入正题。

两者的客户端和服务端之间的通信都是基于TCP协议，在三次握手后正式建立连接。

上图展现了MySQL和PostgreSQL 在TCP连接后的通信流程。

在MySQL中，Server 端会优先再发起一次握手请求，这个过程也是三次握手，这是第一个阶段，有两个目的，第一个目标是传达服务器，也就是MySQL的一些参数信息，比如服务端版本号，通信过程中的最大消息长度和编码等等信息，第二个目的是进行用户验证，所以客户端需要输入用户密码等等信息来进行认证，当认证成功后，就可以进行正常的通信，也就是通行的客户端发送各种命令，服务端对于不同的命令返回不同的结果响应，称之为第二个阶段。

PostgreSQL的通信同样也分为两个阶段，第一个阶段是启动和认证，第二个阶段是正常通信，图中仅仅只展示了启动认证阶段，后面的正常通信和MySQL相同，不做过多解释，我们可以看到，在启动阶段，是由前端（PostgreSQL将客户端/服务端称之为前端/后端，为做区分，下文在PostgreSQL上统称前段/后端）优先发送启动包，与MySQL不同，启动包中含有用户名，访问的数据库名，客户端名称等等信息，后端收到后，会进行判断，该用户是否需要密码认证，如果需要则会发送一个Auth Request 来请求密码认证信息，这个地方，PosgtreSQL的密码认证方式非常多，后面会介绍到，前端此时发送密码认证成功后，后端会开始发送各种Parameter Status，也就是一系列后端参数，比如数server_version，server_encoding，client_encoding，session_authorization，DateStyle等等参数，这个过程中前端不需要做任何响应，甚至可以直接忽略掉，整个过程中一直到最后，后端给前段发送Ready For Query 为止，代表第一个阶段结束，下面前段可以进行正常的查询命令等操作。

通信过程我们都了解了，下面开始看一下通信的报文是怎么样的，前面讲到过，两个数据库在通信时都会分为两个阶段，所以两个阶段过程中的报文是不同的。

先看一下第一阶段，两个数据库的报文是怎样的：

MySQL这边的初始化握手包是由服务端发送的，包含一系列的服务端参数，协议版本，数据库版本，线程ID，挑战随机数，服务器状态和字符编码等等，而PostgreSQL的启动包由客户端发出，主要包含的是协议版本，还有用户名与数据库名等等了客户端参数，PostgreSQL的服务端参数会在后面发送 Parameter Status 报文的时候，一个一个的发送到客户端。这是两者在启动和初始化上最大的区别。（PostgreSQL启动具体流程会放在文章最后）

当两者数据库完成启动和初始化连接后，客户端与服务端就开始了正常的命令交互阶段，称之为第二阶段，这个阶段中，两者报文就比较相似了。

上图，可以非常直观的看出两者报文格式，其共同拥有的部位是报文长度，命令类型和具体命令的语句，除这以外MySQL会比PostgreSQL多一个序列号，然后比较直观的区别则是命令的长度和命令类型两者位置不同。

在报文通信的过程中需要着重注意以下三点：

1. MySQL的报文采用的小端序，PostgreSQL的报文采用的大端序。小端序左边为低位，大端序右边为低位，不了解的可以自行百度。

2. 在命令类型，也就是cmd指令上，MySQL是一个16进制的整数数来判断是哪一种命令，而PostgreSQL是以一个char字符来判断命令类型的。

3. 无论是MySQL还是PostgreSQL的报文中，报文长度Length都是包含自身的，MySQL中还包含序列号的一个字节，所以两者的Payload长度都要用Length - 4 来计算获取到。

MySQL的命令类型（客户端发送）：

PostgreSQL的命令类型（客户端发送）：

列举了，想要更加深入的了解可以查看官方文档中详细介绍。

从服务端发送的命令类型，其实我们就可以看出两个数据库的不同特性，也能很直观的看到两个数据库各自独有的一些功能，MySQL在报文指令这一块要划分的更为详细，很多普通查询的操作都提出来作为单独的报文指令，例如创建数据库，切换数据库等等，而在PostgreSQL这边则会显得更简洁，大部分命令都是直接走简单查询的指令，其他的则是几个大的功能，比如扩展查询，复制操作等。

下面就报文的具体内容，我提取了两个数据库中几个同类型但内容信息差异较大的报文进行对比。

我们先从预处理查询下手，在PostgreSQL叫扩展查询，预处理查询会在查询时候先输入语句进行预处理，语句内部含有一些标识符，代表参数，后面执行的时候再将参数进行填充执行。

Select * from student where id = ?

上诉语句中 ? 就代表标识符，当在执行的时候，再去传入具体的 id 值进行查询。

MySQL 预处理流程：

1. Prepare : 0x16 COM_STMT_PREPARE 预处理语句

回复报文：COM_STMT_PREPARE_OK

2. Execute : 0x17 COM_STMT_EXECUTE 执行预处理语句，带参数

回复报文：OK_Packet, ERR_Packet or Binary Protocol Resultset

3. Close : 0x19 COM_STMT_CLOSE 释放预处理语句

PostgreSQL 预处理流程：

1. Parse : 'P' 预处理语句

回复报文：ParseComplete or ErrorResponse

2. Bind : 'B' 给预处理语句参数绑定具体值，并且指定返回数据格式

回复报文：BindComplete or ErrorResponse

3. Describe : 'D' 描述预处理语句，主要获取解析出来的Param类型和查询计划返回的行信息

回复报文：ParameterDescription, RowDescription, NoData or ErrorResponse

4. Execute : 'E' 执行预处理语句

回复报文：CommandComplete, EmptyQueryResponse, ErrorResponse, or PortalSuspended.

5. Close : 'C' 释放预处理语句

回复报文：CloseComplete or ErrorResponse

6. Sync : 'S' 同步

回复报文：ReadyForQuery or ErrorResponse

两个流程中类型相同的指令是Prepare(Parse)和Execute，Prepare(Parse)阶段两者都差不多，都是传一个预处理语句Stmt，在MySQL中会生成一个ID作为该预处理语句的唯一Stmt ID，后面就会通过这个StmtID来指定具体预处理语句，而在PostgreSQL中会直接拿Stmt Name作为唯一表示。在Execute阶段，两者就会差距非常大，MySQL需要在Execute阶段获取到绑定具体的值，包括其长度，类型，是否为空等等，而PostgreSQL只需要传一个语句名称即可，参数绑定这一块的工作，PostgreSQL 在Bind阶段会完成，我们可以简单理解为PostgreSQL将MySQL的Execute报文分为Bind和Execute两个阶段来进行。

在这个扩展查询过程中，PostgreSQL的报文中在确定预处理语句的时候，会在Bind阶段绑定参数值后会执行该计划，并生成一个可直接执行的对象，称之为 Portal，那么在后面的报文中，Execute过程中指定具体执行的预处理语句时不再是指定语句名称 Stmt Name，而是Portal Name，包括一些其他的报文操作也可以选择使用Portal Name，比如Describe，Execute和Close等。

再看看一些其他的同类型差距较大的报文，刚刚一直讨论的是从客户端发送到服务端的，下面看看从服务端发往客户端的数据写回报文。

当我们要想客户端写回一大段数据的时候，服务端会写回两种报文，第一种是行信息描述，也就是返回字段有多少个，每个字段的含义，类型，格式等等。第二种报文则是行数据了，数据返回多行数据的时候，每一行数据都将作为一个报文发出，返回多少行数据，就会发多好个行数据报文。所以在一次查询中，第一种报文只用发一次，第二种报文则需要发多次。

行数据报文其实两者差距不大，都是返回一行数据，可能在格式上有所差异，但是整体信息上相差无几，相差较大的是行信息描述的报文，在MySQL中称为ColumnDefinition，在PostgreSQL中称为RowDescription。这个报文的主要内容就是列字段信息，一个表一般都有多行，所以这个报文是一个列信息的集合，那么我们主要关注则是一个列信息的差异。

type FieldDescription struct {

Name []byte // 字段名称

TableOID uint32 // 特定表OID

TableAttributeNumber uint16 // 特定表的列属性编号，其他为0

DataTypeOID uint32 // 数据类型OID

DataTypeSize int16 // 数据类型大小

TypeModifier int32 // 类型修改器

Format int16 // 字段的格式 0为text, 1为binary

}

type ColumnInfo struct {

Schema string // Schema名称

Table string // 表名称

OrgTable string // 原始表名称

Name string // 字段名

OrgName string // 原始字段名

ColumnLength uint32 // 列长度

Charset uint16 // 编码

Flag uint16 // 类型特定的标志

Decimal uint8 // 十进制

Type uint8 // 字段类型

DefaultValueLength uint64 // 默认值长度

DefaultValue []byte // 默认值

}

上面给出了两个数据库在实现上一个列信息的结构体，通过两个结构中的属性，可以思考出两者之间的差异，相同的部分是都会返回这一个字段的名称，所属的表，数据类型，数据大小和编码等信息，但是我们可以注意到，PostgreSQL中部分字段是用OID来进行描述，比如表名和字段类型，因为PostgreSQL内部的实现上，会对于表和数据类型等都分配唯一的OID来进行标示，OID是PostgreSQL实现过程中非常重要的一点。而MySQL这边的字段会稍微多一点，对于一些名称会带上原始字段名称，而且还有Schema信息。我们都知道MySQL在实现上追求的是简单简洁，但是在这个位子却显的更为复杂一点。

关于两者的报文格式，这里不再画图详解了，大家可以去官网链接查看：

MySQL

http://dev.mysql.com/doc/internals/en/com-query-response.html#packet-ProtocolText::ResultsetRow

PostgreSQL

http://www.postgresql.org/docs/13/protocol-message-formats.html

最后再看一下 Error Response 报文之间的差异，数据库在执行的过程中，如果出现任何错误，都会返回Error Response 报文，MySQL和PostgreSQL都有Error Response Message，并且差异非常大，还是通过两者返回字段的结构体来看一下。

type ErrorResponse struct {

Severity string

SeverityUnlocalized string // only in 9.6 and greater

Code string

Message string

Detail string

Hint string

Position int32

InternalPosition int32

InternalQuery string

Where string

SchemaName string

TableName string

ColumnName string

DataTypeName string

ConstraintName string

File string

Line int32

Routine string

UnknownFields map[byte]string

}

type SQLError struct {

Code uint16

Message string

State string

}

从结构体上面可以很直观发现，和前面的列信息相反，PostgreSQL在这一块返回的字段非常多，而MySQL仅仅只返回了三个字段，Code，Message和State，这三个字段很好理解，就是错误码，错误信息和状态，具体的内容非常多，想要了解，可以看官方文档：

MySQL

http://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

PostgreSQL在错误处理上，返回的信息就远多于MySQL了，包括有错误等级，详细信息，Hint等等，但实际上，在报文中，并不会将所有字段都填满，只是由于什么原因报错的时候，才会进行相关字段的填充，而其他的字段是不需要返回的，那么为什么字段看着非常多，只是报错的原因太多罢了，至于具体的字段都是什么含义大家也可以看官网介绍：

PostgreSQL

http://www.postgresql.org/docs/13/protocol-error-fields.html

到这里我们也对MySQL和PostgreSQL两者协议层有了一个基本的了解，包括其通信过程还有报文格式等等，那么在我们去实现自己的数据库客户端和中间件的时候，就可以更加迅速的上手。

文章由于篇幅原因，然后协议层也是非常庞大的知识点，所以很多地方并没有深入的讲解和分析，大部分都是简单提出来进行了介绍。当然大家如果有任何的问题，想要探讨，都可以进行留言或者私聊。

PostgreSQL 通信流程

1. postgresql SQL 会先发送一个 SSLRequest报文 询问是否开启SSL加密。

2. 如果需要开启则服务端回复一个 'S' 否则回复一个 'N'。

3. 当回复为 'S' 时，客户端则进行SSL起始握手（先不做实现），后面所有通信都使用SSL加密。

4. 完成SSL起始握手或者在2中直接回复 'N'后, 客户端向服务端发送 StartupMessage 启动包。

5. 服务端接收到启动包奥觉得是否需要授权信息，若需要则发送 AuthenticationRequest

在认证过程中可以采用多种认证方式，不同方式交流不同，最终结束于服务端发送

(1)AuthenticationOk 或者 ErrorResponse。（这个过程中可能会发送如下报文）

(2)ErrorResponse ：连接尝试被拒绝。服务里立即关闭连接。

(3)AuthenticationOk ：认证成功。

(4)AuthenticationKerberosV5 ：前端必须加入服务器的Kerberos V5认证对话。如果成功，服务器响应AuthenticationOk，否则是ErrorResponse。

(5)AuthenticationCleartextPassword：前端发送PasswordMessage，包含密码的clear-text(明文)形式。如果密码正确，服务器响应AuthenticationOk，否则是ErrorResponse。

(6)AuthenticationCryptPassword：前端必须发送PasswordMessage，包含通过crypt(3)加密的密码，AuthenticationCryptPassport报文中提供2字符的salt。

(7)AuthenticationMD5Password：前端必须发送PasswordMessage，包含MD5加密过的密码，AuthenticationMD5Password报文提供4字符的salt。

(8)AuthenticationSCMCredential：响应只能是local Unix-domain连接到平台支持的SCM凭据报文。前端必须生成SCM凭据报文，然后发送单一数据字节。数据字节的内容是无趣的，只用于确保服务器等待足够长事件来接受凭据报文。

(9)AuthenticationGSS：前端发起GSSAPI流。前端发送PasswordMessage，包含GSSAPI数据流的第一部分，后续的报文由服务器的AuthenticationGSSContinue要求。

(10)AuthenticationSSPI：前端发起(initite)SSPI流。前端发送PasswordMessage包含SSPI数据流，后续报文由服务器的AuthenticationGSSContinue要求。

(11)AuthenticationGSSContinue：这个报文包含GSSAPI和SSPI流的后续响应数据。如果GSSAPI或SSPI数据需要更多数据来完成认证，前端必须发送另一个PasswordMessage。如果GSSAPI或SSPI认证完成了，服务器会发送AuthenticationOK来表示认证成功，ErrorResponse表示失败。

6. 服务端验证完成后发送一些参数信息，即 ParameterStatus ，包括 server_version ， client_encoding 和 DateStyle 等。（这个过程中可能会发送如下报文）

(1)BackendKeyData：这个报文提供了以后前端用来取消请求的密码键数据。前端无需理会此报文，但是必须等待ReadyForQuery报文。

(2)ParameterStatus：这个报文告诉(informs)前端当前配置参数，例如 client_encoding 或者 DataStyle 。前端可以忽略此报文，或者记录下来供以后使用。前端无需响应此报文，但是应该继续等待ReadyForQuery报文。

(3)ReadyForQuery：初始化结束，前端可以发送命令了。

(4)ErrorResponse：初始化失败，发送此报文后连接关闭。

(5)NoticeResponse：一个警告信息。前端应该显示该消息然后继续等待ReadyForQuery或ErrorResponse。

7. 服务端最后发送一个 ReadyForQuery 表示一切准备就绪，可以进行创建连接成功。

8. 从 7 发送完 AuthenticationOk 和 ErrorResponse 到最后发送 ReadyForQuery 客户端不会发送信息，会一直等待。

9. 完成初始化流程后开始正常查询通信（这个过程中可能会发送如下报文）

(1)CommandComplete：一个SQL命令正常结束。

(2)CopyInResponse：后端准备好从前端拷贝数据到表格。

(3)CopyOutResponse：后端准备好从表格拷贝数据到前端。

(4)RowDescription：表明由SELECT、FETCH等返回的行。报文内容描述了列布局。这个报文跟随DataRow报文。

(5)DataRow：由SELECT、FETCH返回的行集合。

(6)EmptyQueryResponse：检测到空的查询字符串。

(7)ErrorResponse：发生了错误。

(8)ReadyForQuery：查询过程结束了。一个分隔报文告知前端，因为查询字符串可能包含多个SQL命令。(CommandComplete标记一个SQL命令处理结束，而不是整个字符串)ReadyForQuery总是会发送，无论执行成功还是失败。

(9)NoticeResponse：表示查询有相关的警告信息。提示会在其他报文，如后端会继续执行命令。