新项目选 PostgreSQL,除非你的框架、托管平台或团队已经在用 MySQL。PostgreSQL 的 SQL 更丰富(RETURNING、FULL OUTER JOIN、MERGE、部分索引、在事务中修改表结构),有带 GIN 索引的二进制 jsonb 类型、数组,还有 PostGIS 等扩展。日常写的 SELECT、JOIN 和 INSERT 在两者中几乎一样。
| PostgreSQL 17 | MySQL 8.4 | |
|---|---|---|
| 许可证 | PostgreSQL License(宽松) | 社区版 GPLv2,Oracle 提供商业版本 |
| 返回被修改的行 | RETURNING | 不支持 |
| Upsert | INSERT ... ON CONFLICT, MERGE | INSERT ... ON DUPLICATE KEY UPDATE |
FULL OUTER JOIN | 支持 | 不支持 |
| 事务中的 DDL | 可以回滚 | 会提交当前事务 |
| JSON | json 和 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 写法
| MySQL | PostgreSQL |
|---|---|
`order` | "order" |
AUTO_INCREMENT | GENERATED ALWAYS AS IDENTITY |
IFNULL(a, b) | COALESCE(a, b) |
LIMIT 20, 10 | LIMIT 10 OFFSET 20 |
WHERE name = 'ana' 能匹配 Ana | WHERE 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 另外出售带有额外功能和支持服务的商业版本。