MySQL 向左，PostgreSQL 向右

MySQL 和 PostgreSQL 是我们熟知的两种开源关系型数据库。它们之间有很多共同之处但也存在一些重要的区别从而为新手甚至老司机带来了选择上的争论和困难。

本文对这两种数据库系统进行了详细的比较给出了它们之间的异同方便大家选择数据库时作为参考。

如果你想要直接知道结论MySQL 简单易用适合互联网业务PostgreSQL 支持丰富的功能和扩展尤其适合复杂的企业在线应用和分析系统。

基本信息

MySQL 是世界上最流行的开源关系型数据库目前属于 Oracle 公司所有最新版本为 MySQL 8.0。MySQL 提供了两种版本社区版和企业版。其中社区版使用 GPL 开源协议。MySQL 使用 C/C++ 语言开发支持 Linux、FreeBSD、OS X、Solaris、Windows 平台。支持插件式存储引擎InnoDB 和 NDB 支持事务 ACID 属性兼容大部分 SQL 标准。MySQL 使用多线程模型每个连接对应一个系统线程。每个 MySQL 实例可以创建多个数据库模式。常见的分支包括 MariaDB、Percona、Galera 等。

PostgreSQL 是世界上最先进的开源关系-对象数据库拥有一个活跃的全球社区最新版本为 PostgreSQL 15。PostgreSQL 是一个完全开源的软件使用 BSD 风格的开源协议使用更加自由。PostgreSQL 使用 C 语言开发支持 Linux、FreeBSD、NetBSD、OpenBSD、HP-UX、OS X、Solaris、Unix、Windows 平台。支持事务 ACID 属性兼容绝大多数 SQL 标准。PostgreSQL 使用多进程模型每个连接对应一个系统进程。每个 PostgreSQL 实例可以创建多个数据库每个数据库可以创建多个模式。常见的分支包括 Greenplum、EnterpriseDB、TimescaleDB、Citus 等。

功能比较

以下功能比较基于 MySQL 8.0 和 PostgreSQL 15。

SQL 查询

MySQL 提供了比较完善的 SQL 标准支持和扩展PostgreSQL 提供了非常完善的 SQL 标准支持和扩展。

功能特性	MySQL	PostgreSQL
无表查询	✔️	✔️
GROUP BY ROLLUP	✔️	✔️
GROUP BY CUBE	❌	✔️
GROUP BY GROUPING SETS	❌	✔️
PIVOT	❌	CROSSTAB 表函数
字符串聚合	GROUP_CONCAT 函数	STRING_AGG 函数
支持数据过滤的聚合函数	❌	✔️
行构造函数ROW	❌	✔️
行构造表达式WHERE	✔️	✔️
SQL 语句中的变量	✔️	❌
基于正则表达式的比较条件	✔️	✔️
基于正则表达式的子串查找	✔️	✔️
基于正则表达式的子串替换	✔️	✔️
内连接INNER JOIN	✔️	✔️
左外连接LEFT OUTER JOIN	✔️	✔️
右外连接RIGTH OUTER JOIN	✔️	✔️
全外连接FULL OUTER JOIN	❌	✔️
交叉连接CROSS JOIN	✔️	✔️
自然连接NATURAL JOIN	✔️	✔️
连接实现算法	Nested Loop Join、Hash Join	Nested Loop Join、Hash Join、Sort Merge Join
子查询	✔️	✔️
横向LATERAL子查询	✔️	✔️
并集运算UNION	✔️	✔️
交集运算INTERSECT	✔️	✔️
差集运算EXCEPT	✔️	✔️
通用表表达式WITH	✔️	✔️
递归查询WITH RECURSIVE	✔️	✔️
窗口函数	✔️	✔️
字符串聚合窗口函数	❌	STRING_AGG 函数
数组转换为表UNNEST	❌	✔️
空值排序顺序ORDER BY NULLS FIRST/LAST	❌	✔️
支持空值的等值比较IS [NOT] DISTINCT FROM	<=>	✔️
BETWEEN SYMMETRIC 运算符	❌	✔️
OVERLAPS 运算符	❌	✔️
单个查询中两次引用同一个临时表	❌	✔️
并行查询	❌	✔️
全文搜索	✔️	✔️

DML 语句

DML 语句用于对表中的数据执行增删改合操作。

功能特性	MySQL	PostgreSQL
多行插入语句	✔️	✔️
插入查询结果INSERT SELECT	✔️	✔️
关联更新	UPDATE JOIN	UPDATE FROM
元组多字段更新	❌	✔️
SELECT FOR UPDATE NOWAIT	✔️	✔️
关联删除	DELETE JOIN	DELETE USING
数据合并MERGE	❌	✔️
数据合并专用语句	INSERT ON DUPLICATE、REPLACE	INSERT ON CONFLICT
DML 语句中的 RETURNING 子句	❌	✔️
DML 语句中的 CTE	✔️	✔️
CTE 中的 DML 语句	❌	✔️
DML 操作过程中的读一致性	❌	✔️
子查询中引用 DML 目标表	❌	✔️

DDL 语句

DDL 语句用于定义、修改、删除数据库中的对象。

功能特性	MySQL	PostgreSQL
事务型 DDL	❌	✔️
计算列	✔️	✔️
序列	❌	✔️
自增字段	AUTO_INCREMENT	SERIAL、IDENTITY
非阻塞式的索引创建	❌	✔️
分区表	RANGE、LIST、HASH、KEY 以及子分区	RANGE、LIST、HASH
表继承	❌	✔️
DROP TABLE	不支持 CASCADE 级联删除	支持 CASCADE 级联删除
TRUNCATE TABLE	不支持 CASCADE 和事务安全性等选项删除之后无法回滚	支持 CASCADE 和事务安全性等选项在事务中可以回滚
增加字段时指定字段的位置	✔️	❌
物化视图	❌	✔️
会话级本地临时表	✔️	✔️

服务器编程

服务器编程在数据库中提供了类似编程语言的功能例如变量、控制流结构if-else、while、for、游标以及异常处理等。

功能特性	MySQL	PostgreSQL
存储过程	SQL 存储过程	PL/pgSQL、SQL、Python、Java、JavaScript、Perl、TCL 等
自定义函数	✔️	✔️
表值函数	❌	✔️
自定义聚合函数	❌	✔️
函数重载	❌	✔️
自定义运算符	❌	✔️
触发器	DML 触发器	DML 触发器和 DDL 触发器
语句级触发器	❌	✔️
行级触发器	✔️	✔️
替代触发器	❌	✔️
级联删除操作是否触发删除触发器	❌	✔️
动态 SQL 语句	❌	✔️
RETURNING 子句	❌	✔️
计划任务	内置	基于 pgAgent 组件
消息队列	❌	✔️NOTIFY/LISTEN

数据类型

MySQL 支持基本的数字、日期、字符串以及空间数据类型。PostgreSQL 支持更丰富的数据类型包括数字、日期、字符串、空间、IP 地址、数组、范围、UUID、域以及自定义数据类型。

功能特性	MySQL	PostgreSQL
SQL 标准类型	✔️	✔️
无符号整型	✔️	❌
布尔类型	使用 TINYINT 替代	✔️
TIME WITH TIME ZONE	❌	✔️
TIMESTAMP WITH TIME ZONE	❌	✔️
INTERVAL 类型	❌支持 INTERVAL 常量	✔️
数组类型	❌	✔️
范围类型	❌	✔️
枚举类型	✔️	✔️
集合类型	✔️	❌
IP 地址数据类型	❌	✔️
UUID	❌	✔️
域类型	❌	✔️
空间数据类型	✔️	✔️
自定义数据类型	❌	✔️

索引

索引可以用于提高数据库的查询性能但是索引需要占用额外的磁盘空间修改数据时也需要进行索引的维护。

功能特性	MySQL	PostgreSQL
索引类型	B-树、哈希、空间、FULLTEXT	B-树、哈希、GiST、SP-GiST、GIN、BRIN、布隆过滤器
聚集索引	✔️	❌
复合索引	✔️	✔️
降序索引	✔️	✔️
部分索引	❌	✔️
包含额外字段的索引INCLUDE	❌	✔️
函数索引	✔️	✔️
全文索引	✔️	✔️
不可见索引	✔️	❌
自定义函数索引	❌	✔️
基于多个字段的信息统计	❌	✔️

约束

SQL 标准定义了 6 种完整性约束非空约束NOT NULL、唯一约束UNIQUE、主键约束Primary Key、外键约束Foreign Key、检查约束CHECK以及默认值。

MySQLInnoDB 和 NDB支持了完整的约束PostgreSQL 则提供了更多的约束选项。

功能特性	MySQL	PostgreSQL
非空约束	✔️	✔️
唯一约束	✔️	✔️
主键约束	✔️	✔️
自定义主键约束名称	❌	✔️
外键约束	InnoDB 和 NDB	✔️
外键约束延迟校验	❌	✔️
语句级约束校验	❌	✔️
外键的级联更新和删除	✔️	✔️
多字段外键的 MATCH FULL 选项	❌	✔️
检查约束	✔️	✔️
基于自定义函数的检查约束	❌	✔️
排除约束	❌	✔️
默认值	✔️	✔️

视图

视图就是一个预定义的查询语句。MySQL 和 PostgreSQL 都提供了视图。

功能特性	MySQL	PostgreSQL
可更新视图	✔️	✔️
可更新视图的 WITH CHECK OPTION 选项	✔️	✔️
基于子查询的视图	❌	✔️
基于视图的触发器	❌	✔️

NoSQL

MySQL 提供了 JSON 数据类型以及文档操作接口PostgreSQL 提供了 JSON 以及 XML 数据类型。

功能特性	MySQL	PostgreSQL
XML 数据类型	❌	✔️
XPath	✔️	✔️
JSON 数据类型	✔️	✔️
SQL/JSON 路径	✔️	✔️
JSON 文档类型	❌	✔️
文档 CRUD 操作	✔️	❌
Key/Value 存储	InnoDB memcached 插件	hstore 插件

安全性

功能特性	MySQL	PostgreSQL
用户组/角色	✔️	✔️
ACL	✔️	✔️
行级安全	❌	✔️
列级授权	✔️	✔️
加密和安全连接	TLS、SSH、LDAP、SHA-256、PAM、证书	SSL、SSH、GSSAPI、SSPI、LDAP、SCRAM-SHA-256、MFA、PAM、证书

备份与恢复

备份对于数据库至关重要因为当数据库出现系统崩溃、硬件故障或者用户误删除数据时可以利用备份进行恢复。

功能特性	MySQL	PostgreSQL
逻辑备份	mysqldump、mysqlpump	pg_dump、pg_dumpall
物理备份	Xtrabackup、mysqlbackup	Barman、pg_basebackup、pg_probackup

复制与集群

数据库复制可以将数据从主数据库同步到从数据库可以用于提高系统的可用性和读写性能。

功能特性	MySQL	PostgreSQL
复制类型	逻辑复制	物理复制、逻辑复制
复制模式	异步复制、半同步复制	同步复制、异步复制
集群方案	MHA、MGR、InnoDB Cluster、NDB Cluster	Citus、Greenplum、Pgpool-II、Slony-I
外部数据集成	同构数据库MySQL、文本文件CSV	同构数据库PostgreSQL、异构数据库MySQL、Oracle、SQL Server、SQLite、HBase、Cassandra、ClickHouse、CouchDB、MongoDB、Neo4j、Redis、Hadoop、HIve等以及文本文件CSV、JSON、XML等

总结

MySQL 以其简单易用和卓越的性能而备受欢迎同时也因功能相对简单和部分 SQL 标准支持而饱受诟病。不过最近几年随着新版本MySQL 8.0的发布弥补了大量缺失的功能比如通用表表达式、窗口函数、检查约束等等。

PostgreSQL 以其功能强大和可扩展性而备受开发者喜爱同时也因其学院派的风格而错过了互联网的发展浪潮。随着开源、数字化、国产化的兴起PostgreSQL 也逐渐迎来了新的发展契机让我们拭目以待

如果觉得文章有用欢迎评论📝、点赞👍、推荐🎁