J9集团

MySQL和PgSQL和谈

解析与对比

J9集团数码集团

江坤

在实现一个数据库系统，客户端，或者中央件的时辰，都必要关注数据库与客户端之间的通讯，也就是数据库和谈层的实现，此刻业界内讨论的比力多的两种开源关系数据库就是MySQL和PostgreSQL，蕴含一些NewSQL的实现，也会向上兼容MySQL或者PostgreSQL，实现他们的和谈层，但是这两个数据库的和谈实现是齐全不一样的，所以这也是为什么我们用两者的客户端无法衔接另一个数据库的原因。

其实两者和谈层处置，在双方的官方文档中都比力具体了，若是各人都是老手的话，能够直接上官网文档啃生肉：

PostgreSQL

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

MySQL

https://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

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

PostgreSQL

https://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

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

PostgreSQL在谬误处置上，返回的信息就远多于MySQL了，蕴含有谬误等级，具体信息，Hint等等，但现实上，在报文中，并不会将所有字段都填满，只是由于什么原因报错的时辰，才会进行有关字段的填充，而其他的字段是不必要返回的，那么为什么字段看着极度多，只是报错的原因太多而已，至于具体的字段都是什么寓意各人也能够看官网介绍：

PostgreSQL

https://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：暗示查问有有关的忠告信息。提醒会在其他报文，如后端会持续执行号令。