Menu

PostgreSQL 和 MySQL 的区别:该选哪个

PostgreSQL 和 MySQL 都是免费的关系型数据库。PostgreSQL 的 SQL 更丰富,有 JSONB 和支持事务的 DDL;在已经默认用 MySQL 的环境里,MySQL 运维更简单。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

新项目选 PostgreSQL,除非你的框架、托管平台或团队已经在用 MySQL。PostgreSQL 的 SQL 更丰富(RETURNING、FULL OUTER JOIN、MERGE、部分索引、在事务中修改表结构),有带 GIN 索引的二进制 jsonb 类型、数组,还有 PostGIS 等扩展。日常写的 SELECT、JOIN 和 INSERT 在两者中几乎一样。

PostgreSQL 17MySQL 8.4
许可证PostgreSQL License(宽松)社区版 GPLv2,Oracle 提供商业版本
返回被修改的行RETURNING不支持
UpsertINSERT ... ON CONFLICT, MERGEINSERT ... ON DUPLICATE KEY UPDATE
FULL OUTER JOIN支持不支持
事务中的 DDL可以回滚会提交当前事务
JSONjson 和 jsonb,GIN 索引JSON,通过生成列或函数索引建索引
数组、部分索引支持不支持
默认隔离级别读已提交可重复读
默认文本比较区分大小写不区分大小写(utf8mb4_0900_ai_ci)

插入后取回新行

RETURNING 会返回 INSERT、UPDATE 或 DELETE 涉及的行,所以不用再查一次就能拿到生成的 id:

它返回两行,id 分别是 1 和 2。MySQL 没有 RETURNING。LAST_INSERT_ID() 返回最近一次插入生成的第一个 id:

INSERT INTO users (email) VALUES ('asha@example.com');
SELECT LAST_INSERT_ID();

一条语句完成插入或更新

PostgreSQL 指明发生冲突的键,并把传入的行读作 excluded:

A-1 变成 8,B-2 以 7 被插入。MySQL 8.4 的写法使用行别名(旧的 VALUES(qty) 函数已被弃用):

INSERT INTO stock VALUES ('A-1', 3), ('B-2', 7) AS new
ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty;

upsert 页面介绍了 DO NOTHING 和带条件的更新。

MySQL 没有的连接

FULL OUTER JOIN 会保留两边没有匹配上的行。MySQL 只能用 UNION 把 LEFT JOIN 和 RIGHT JOIN 合起来模拟。

free 没有注册用户,team 没有价格,但两者都会出现,缺失的列为 NULL。

回滚表结构变更

在 PostgreSQL 中,CREATE、ALTER 和 DROP 都属于事务的一部分,所以失败的迁移不会留下改了一半的表结构:

只剩下 orders.id。在 MySQL 中,每条 DDL 语句都会提交当前打开的事务,所以迁移工具无法撤销失败语句之前的那些语句。

存储和查询 JSON

PostgreSQL 的 jsonb 存储解析后的 JSON,GIN 索引可以用于它的包含(@>)和键(?)判断。MySQL 的 JSON 类型也是二进制的。你用 ->> 路径查询它,并且每次只能给一个路径建索引,方法是生成列或函数索引:

SELECT name FROM users WHERE profile->>'$.plan' = 'pro';

JSONB 页面介绍了 PostgreSQL 的各种操作符。

并发读写

两者都使用多版本并发控制(MVCC),所以读不会阻塞写。PostgreSQL 每次更新都会写入一个新的行版本,旧版本留在表中,直到 VACUUM 回收它。autovacuum 在后台做这件事,更新频繁的表要靠它跟上节奏。InnoDB 就地更新行,把旧版本保存在 undo 日志中,由 purge 线程清理。

默认隔离级别不同。PostgreSQL 使用读已提交:每条语句看到的是它开始之前已提交的数据。MySQL 使用可重复读:事务中的每次普通读取都看到同一个快照。它的 UPDATE、DELETE 和加锁读取还会锁住扫描过的索引范围(next-key 锁)。

复制、故障转移和工具

PostgreSQL 自带流复制,它保留整台服务器的物理副本,副本可以处理读请求;还有逻辑复制,通过发布和订阅复制选定的表。自动故障转移需要外部工具。MySQL 通过二进制日志复制,并提供 Group Replication,它是 InnoDB Cluster 的基础。

客户端方面,PostgreSQL 有 psql 和 pgAdmin;MySQL 有 mysql 客户端、MySQL Shell 和 MySQL Workbench。两者都在 AWS、Google Cloud 和 Azure 上提供托管服务。

把 MySQL 的习惯改成 PostgreSQL 写法

MySQLPostgreSQL
`order`"order"
AUTO_INCREMENTGENERATED ALWAYS AS IDENTITY
IFNULL(a, b)COALESCE(a, b)
LIMIT 20, 10LIMIT 10 OFFSET 20
WHERE name = 'ana' 能匹配 AnaWHERE name ILIKE 'ana'
TINYINT(1)boolean

常见问题

PostgreSQL 比 MySQL 快吗?

总体上谁也不比谁快。结果取决于工作负载、表结构、索引和服务器设置,所以如果你看重速度,先在自己的数据上测试自己的查询再做选择。

PostgreSQL 比 MySQL 难学吗?

日常写的 SQL(SELECT、JOIN、INSERT、UPDATE)在两者中几乎一样。PostgreSQL 对类型更严格,默认区分大小写,所以有些 MySQL 能接受的查询需要显式类型转换或改用 ILIKE。

能把 MySQL 数据库迁移到 PostgreSQL 吗?

能。pgloader 之类的工具可以复制表结构和数据。之后还需要改写查询:反引号换成双引号,AUTO_INCREMENT 换成标识列,ON DUPLICATE KEY UPDATE 换成 ON CONFLICT,IFNULL 换成 COALESCE。

PostgreSQL 和 MySQL 是免费的吗?

两者都可以免费使用。PostgreSQL 采用 PostgreSQL License,这是一种宽松的许可证,没有付费版本。MySQL 社区版采用 GPLv2,Oracle 另外出售带有额外功能和支持服务的商业版本。

Coddy 编程语言插图

和 Coddy 一起学编程

开始