PHP小码农

Appearance

PostgreSQL vs MySQL vs Oracle ​

本节把 PostgreSQL 放在主流关系数据库的横向坐标系里对比：许可协议、数据类型、扩展能力、JSON 支持、事务与并发、性能特点、生态工具与适用场景。学完本节，你将能对一次真实的选型讨论给出有依据的判断，而不是停留在"哪个更流行"。

前置知识

阅读本节前，建议先了解：发展历史与版本演进

基础概念 ​

横向对比数据库时，最容易犯的错误是只比"功能列表"。更有效的做法是先想清楚负载画像（Workload Profile），再看每个数据库的设计取舍。对比前先明确几个术语：

  • 许可协议（License）：决定你能不能在商业闭源产品中自由使用、修改、再分发。
  • 存储引擎（Storage Engine）：数据落盘与索引的实现层。MySQL 有 InnoDB、MyISAM 等多套引擎；PostgreSQL 只有一套内置堆表（Heap）引擎，但其索引与类型可扩展。
  • 多版本并发控制（MVCC, Multi-Version Concurrency Control）：让读不阻塞写、写不阻塞读的机制。三者都实现了 MVCC，但默认隔离级别不同。
  • 扩展（Extension）：以插件形式向数据库内核注入新类型、函数、索引方法的能力。这是 PostgreSQL 最独特的开放点。
  • 生态（Ecosystem）：周边工具、云厂商托管形态、社区活跃度、资料丰富程度。
  • 分布式（Distributed）：跨多机横向扩展的能力。PostgreSQL 原生为单机主从架构，分布式依赖外部方案（如 Citus、Greenplum）或分库分表中间件。

三者的定位差异可以先用一张表概括：

维度PostgreSQLMySQLOracle Database
许可协议PostgreSQL License（类 BSD/MIT，极度宽松）GPLv2 + 商业双许可（Oracle 持有）商业闭源授权
归属方全球社区（无单一公司控制）Oracle 公司Oracle 公司
定位通用型对象关系数据库（可扩展为多模）互联网 OLTP 主力大型企业核心业务
默认隔离级别READ COMMITTEDREPEATABLE READ（InnoDB）READ COMMITTED
扩展能力极强（自定义类型/索引/函数/存储引擎接口）弱（仅存储引擎层面的插件）中（有 Extensibility Framework，但门槛与成本高）
成本免费社区版免费，企业版收费许可费高，按 CPU 核数计费
典型场景复杂查询、地理空间、JSON、分析混合负载高并发简单读写、Web 应用金融、电信、大型 ERP

语法与用法 ​

三者在"同一件事怎么写"上差异明显。以下对比围绕最常用的几个语法点展开，便于迁移时快速定位。

1. 自增主键

sql
-- PostgreSQL：标准 SQL 写法（推荐）
CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);
sql
-- MySQL：AUTO_INCREMENT
CREATE TABLE users (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
sql
-- Oracle：12c 之前必须借助 SEQUENCE + TRIGGER；12c 起支持 IDENTITY
CREATE TABLE users (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100) NOT NULL
);

2. 标识符引号规则

sql
-- PostgreSQL：未加引号的标识符统一转为小写；双引号才是标识符引用
SELECT id, name FROM users WHERE name = 'Alice';

-- 不推荐：双引号引用会强制大小写敏感，后续必须一直加引号
SELECT "Id", "Name" FROM "Users";
sql
-- MySQL：默认双引号是字符串字面量，标识符用反引号
-- 在 ANSI_QUOTES 模式下双引号才等价于 PostgreSQL 的行为
SELECT `id`, `name` FROM `users` WHERE `name` = "Alice";

3. 行数限制与分页

sql
-- PostgreSQL / Oracle 12c+：标准 FETCH FIRST 语法
SELECT id, name FROM users ORDER BY id FETCH FIRST 10 ROWS ONLY;
sql
-- MySQL：LIMIT
SELECT id, name FROM users ORDER BY id LIMIT 10;

4. UPSERT（插入或更新）

sql
-- PostgreSQL 9.5+：ON CONFLICT，需要指定冲突目标
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
sql
-- PostgreSQL 15+：标准 MERGE 语句，可覆盖更复杂的双表合并场景
MERGE INTO users AS t
USING (VALUES (1, 'Alice')) AS s (id, name)
   ON t.id = s.id
WHEN MATCHED THEN UPDATE SET name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);
sql
-- MySQL：ON DUPLICATE KEY UPDATE
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

5. 完整外连接

sql
-- PostgreSQL：FULL OUTER JOIN 原生支持
SELECT u.name, o.order_no
FROM users AS u
FULL OUTER JOIN orders AS o ON o.user_id = u.id;
sql
-- MySQL：不支持 FULL OUTER JOIN，必须用 LEFT JOIN UNION RIGHT JOIN 拼
SELECT u.name, o.order_no FROM users u LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.name, o.order_no FROM users u RIGHT JOIN orders o ON o.user_id = u.id;

详细说明 ​

许可协议与商业模式 ​

PostgreSQL 采用 PostgreSQL License，本质是类 BSD/MIT 的宽松协议：可以自由用于闭源商业产品，修改后无需开源，也无需向任何实体付费或报备。这个"无商业实体控制"的特性，是很多团队把它作为长期基础设施底座的核心原因——不存在"上游公司被收购后改授权"的风险。

MySQL 采用 GPLv2 与商业许可的双授权模式，由 Oracle 持有。社区版免费，但企业版插件（如企业级备份、审计、线程池）与官方支持需要付费。GPLv2 对"分发衍生作品"有传染性要求，闭源分发时通常需要购买商业许可，这一点在商业合规评审中必须提前确认。

Oracle Database 是纯商业闭源授权，按 CPU 核数计费，且大量高级特性（分区、RAC、Advanced Compression、Active Data Guard）需要额外购买选件。它的优势不在单价低，而在于超大规模核心系统的稳定性验证与配套服务体系。

数据类型与扩展能力 ​

这是 PostgreSQL 优势最明显的一栏。它的类型系统本身可扩展：你能用 CREATE TYPE 定义复合类型、枚举类型、范围类型，甚至为新类型定义专属的操作符与索引方法。

sql
-- PostgreSQL：自定义复合类型 + 自定义枚举
CREATE TYPE money_breakdown AS (currency CHAR(3), amount NUMERIC(14, 2));

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'closed');

CREATE TABLE orders (
  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status  order_status   NOT NULL DEFAULT 'pending',
  total   money_breakdown NOT NULL
);

INSERT INTO orders (status, total) VALUES ('paid', ROW('CNY', 199.00));

-- 复合类型用括号访问字段，也可整体展开
SELECT status, (total).currency, (total).amount FROM orders;

原生类型覆盖也最完整：ARRAY、JSONB、UUID、INET/CIDR/MACADDR、TSVECTOR、RANGE、MULTIRANGE（14+）、INTERVAL、任意精度 NUMERIC。

MySQL 的类型系统开放度低得多，没有数组类型，JSON 类型也不能索引内部路径（只能建函数索引或生成列），不具备自定义类型与操作符的机制。Oracle 有对象类型（Object Type）体系与 VARRAY、嵌套表，能力上接近，但定义与使用复杂，且 NUMBER、VARCHAR2、DATE（含时间）等类型语义与标准 SQL 有偏差异。

JSON 支持对比 ​

三者的 JSON 能力有代际差异：

能力PostgreSQLMySQLOracle
JSON 类型JSON（文本）与 JSONB（二进制，可索引）JSON（5.7+，内部二进制存储）JSON（12c+，21c 起有原生类型）
路径查询-> / ->> / #> / #>>-> / ->> / JSON_EXTRACT()JSON_VALUE() / JSON_QUERY()
包含查询@>、<@、?、`?、?&`JSON_CONTAINS()
索引方式GIN 索引直接建在 JSONB 上需生成列 + 索引，或函数索引需函数索引或 JSON 搜索索引
SQL/JSON 标准16 起 JSON_TABLE、IS JSON（16/17+）部分支持 JSON_TABLE（8.0.4+）支持 JSON_TABLE
JSONB 下标访问14+ 支持 col['key'] 写法不支持不支持
sql
-- PostgreSQL：GIN 索引让 JSONB 包含查询走索引
CREATE INDEX idx_products_attrs ON products USING GIN (attrs);

SELECT sku, name
FROM products
WHERE attrs @> '{"brand": "Logitech"}'::jsonb;   -- 一行命中，走 GIN

PostgreSQL 的 JSONB 与索引的结合是最成熟的：不需要预先声明"要索引哪个路径"，@> 这类包含查询可以直接利用 GIN 的倒排结构。对"半结构化字段多、查询模式不固定"的业务，这一点的收益非常直接。

事务与并发控制 ​

三者都实现了 MVCC，但默认隔离级别与失败行为不同，这是迁移时最容易踩坑的地方。

维度PostgreSQLMySQL（InnoDB）Oracle
默认隔离级别READ COMMITTEDREPEATABLE READREAD COMMITTED
快照建立时机每条语句开始时（READ COMMITTED）事务中第一次读时每条语句开始时
唯一约束冲突事务整体回滚，需应用重试语句失败，可重试单条语句语句级回滚
写-写冲突后到者等待，可能触发序列化失败行锁等待，超时回滚行锁等待
串行化实现SSI（可序列化快照隔离，9.1+）基于间隙锁的模拟基于行锁的模拟
sql
-- 显式提升隔离级别（PostgreSQL 语法）
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

-- 或只在当前事务内设置
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 串行化下冲突会抛 40001，应用层必须实现重试
COMMIT;

PostgreSQL 的 READ COMMITTED 有一个与 Oracle 相似、与 MySQL 不同的行为：同一事务内两次执行相同查询，可能看到不同结果（因为每条语句取新快照）。如果业务依赖"事务内读一致"，必须显式升级到 REPEATABLE READ。

MySQL InnoDB 默认为 REPEATABLE READ，事务内读一致是"默认给到"的，迁移到 PostgreSQL 时这里往往是隐性 bug 的来源。反过来，PostgreSQL 的 REPEATABLE READ 是通过快照实现的，不会像 MySQL 那样出现间隙锁导致的额外死锁。

性能特点 ​

三者的性能特征差异来自架构选择：

  • PostgreSQL：单条复杂查询的优化器更强，支持 Hash Join、Merge Join、并行查询、JIT 编译、增量排序、B-tree 跳跃扫描（18+）。对分析型混合查询（HTAP）友好。写路径上每行更新都会产生新元组版本，需要 VACUUM 回收，高更新频率场景的表膨胀（Bloat）必须监控。
  • MySQL：短事务、简单主键查询的吞吐表现优秀，主从复制与中间件分库分表生态成熟。但多表关联与复杂子查询的优化器能力弱于 PostgreSQL，容易退化为嵌套循环。
  • Oracle：大规模核心交易系统的稳定性与优化器成熟度经过长期验证，有 AWR、ASH、SQL Plan Management 等体系化诊断工具。代价是成本与生态锁定。
sql
-- PostgreSQL 18+：B-tree 跳跃扫描让"非前导列"查询也能用上多列索引
-- PostgreSQL 18+
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

-- 即使只过滤 created_at，也可能借助 Skip Scan 使用该索引
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at > now() - interval '1 day';
sql
-- PostgreSQL 14+：并行查询对分析型语句收益明显
-- PostgreSQL 14+
SET max_parallel_workers_per_gather = 4;
EXPLAIN (ANALYZE)
SELECT status, count(*) FROM orders GROUP BY status;

生态与工具链 ​

  • PostgreSQL：psql、pgAdmin、pg_dump/pg_restore、pg_upgrade、pg_basebackup 等官方工具齐全；扩展生态是最大亮点（PostGIS、pgvector、TimescaleDB、Citus、pg_partman、pg_stat_statements）；所有主流云厂商提供托管（RDS、Aurora、Cloud SQL、Azure Database）；ORM 与驱动覆盖全语言。
  • MySQL：生态成熟度同样很高，工具链偏向"运维友好"（mysqldump、XtraBackup、gh-ost、pt-toolkit）；在互联网公司积累了大量分库分表与中间件实践（ShardingSphere、Vitess、MyCat）。
  • Oracle：企业级工具链最完善（RMAN、Data Guard、Enterprise Manager、GoldenGate），但工具与云服务绑定深，迁移出口成本高。

实战示例 ​

下面是一个真实场景：某项目从 MySQL 迁移到 PostgreSQL，需要改写 DDL、修正隐式类型转换问题，并用上 PostgreSQL 独有的特性。

第 1 步：原 MySQL 表结构

sql
-- 迁移前（MySQL 语法，仅作对照，不在 PostgreSQL 中执行）
-- CREATE TABLE `t_order` (
--   `id`         BIGINT       NOT NULL AUTO_INCREMENT,
--   `order_no`   VARCHAR(64)  NOT NULL,
--   `user_id`    BIGINT       NOT NULL,
--   `amount`     DOUBLE       NOT NULL,
--   `is_paid`    TINYINT(1)   NOT NULL DEFAULT 0,
--   `created_at` DATETIME     NOT NULL,
--   PRIMARY KEY (`id`),
--   UNIQUE KEY `uk_order_no` (`order_no`)
-- ) ENGINE = InnoDB;

第 2 步：改写为 PostgreSQL DDL

sql
-- demo 库中执行
CREATE TABLE t_order (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   TEXT        NOT NULL UNIQUE,
  user_id    BIGINT      NOT NULL,
  amount     NUMERIC(12, 2) NOT NULL CHECK (amount >= 0),  -- 金额禁止用 FLOAT/DOUBLE
  is_paid    BOOLEAN     NOT NULL DEFAULT false,           -- TINYINT(1) → BOOLEAN
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),           -- DATETIME → TIMESTAMPTZ
  remark     TEXT,
  attrs      JSONB       NOT NULL DEFAULT '{}'             -- 新增：半结构化扩展字段
);

改写要点对照：

MySQL 写法PostgreSQL 写法原因
AUTO_INCREMENTGENERATED ALWAYS AS IDENTITY标准 SQL，避免序列与列脱钩
VARCHAR(64)TEXT长度约束对性能无收益，改为 CHECK 表达业务规则
DOUBLE 存金额NUMERIC(12, 2)二进制浮点有舍入误差，金额必须精确
TINYINT(1)BOOLEAN语义明确，避免 0/1 与 true/false 混用
DATETIMETIMESTAMPTZ带时区存储，避免跨时区歧义
反引号无引号或双引号PostgreSQL 反引号非法

第 3 步：准备数据

sql
INSERT INTO t_order (order_no, user_id, amount, is_paid, attrs) VALUES
  ('SO-20260901', 1001, 199.00, true,  '{"channel": "app", "coupon": "C10"}'),
  ('SO-20260902', 1002,  59.90, false, '{"channel": "web"}'),
  ('SO-20260903', 1001, 520.00, true,  '{"channel": "app", "gift": true}');

第 4 步：用 RETURNING 一次拿到写入结果（MySQL 需再查一次）

sql
INSERT INTO t_order (order_no, user_id, amount)
VALUES ('SO-20260904', 1003, 88.00)
RETURNING id, order_no, created_at;
text
 id |  order_no   |          created_at
----+-------------+-------------------------------
  4 | SO-20260904 | 2026-09-21 10:32:41.517823+08
(1 row)

第 5 步：用 JSONB 做条件筛选

sql
SELECT order_no, amount, attrs ->> 'channel' AS channel
FROM t_order
WHERE attrs @> '{"channel": "app"}'::jsonb
ORDER BY amount DESC;
text
  order_no   | amount  | channel
-------------+---------+---------
 SO-20260903 |  520.00 | app
 SO-20260901 |  199.00 | app
(2 rows)

第 6 步：聚合校验迁移前后数据是否一致

sql
SELECT
  count(*)                       AS order_count,
  count(*) FILTER (WHERE is_paid) AS paid_count,   -- FILTER 是标准 SQL 聚合过滤
  sum(amount)                    AS total_amount
FROM t_order;
text
 order_count | paid_count | total_amount
-------------+------------+--------------
           4 |          2 |       866.90
(1 row)

第 7 步：迁移完成后的回归检查

bash
# 导出 MySQL 侧数据并在 PostgreSQL 侧比对行数与关键聚合值
psql -d demo -c "SELECT count(*) FROM t_order;"
psql -d demo -c "SELECT sum(amount) FROM t_order;"

# 用 pg_stat_statements 排查迁移后出现的高耗时 SQL
psql -d demo -c "SELECT queryid, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;"

注意事项 ​

  • 不要直接照搬 MySQL 的"列长度限制"习惯。PostgreSQL 的 TEXT 与 VARCHAR(255) 性能完全相同，长度约束只应表达业务规则而非存储优化。
sql
-- 不推荐：用 VARCHAR 长度做"性能优化"
CREATE TABLE t_bad (name VARCHAR(255), remark VARCHAR(255), note VARCHAR(255));

-- 推荐：需要业务约束时显式声明，且用 CHECK 表达更复杂的规则
CREATE TABLE t_good (
  name   TEXT NOT NULL CHECK (length(name) BETWEEN 1 AND 64),
  remark TEXT
);
  • 金额、比例等精确值绝不能用浮点类型。MySQL 用 DOUBLE、PostgreSQL 用 REAL/DOUBLE PRECISION 存金额都会引入误差。
sql
-- 错误！浮点存金额会累积舍入误差
CREATE TABLE t_pay_bad (amount DOUBLE PRECISION);
INSERT INTO t_pay_bad VALUES (0.1), (0.2);
SELECT sum(amount) FROM t_pay_bad;   -- 结果 0.30000000000000004，而非 0.3

-- 正确：使用 NUMERIC
CREATE TABLE t_pay_good (amount NUMERIC(12, 2));
INSERT INTO t_pay_good VALUES (0.1), (0.2);
SELECT sum(amount) FROM t_pay_good;  -- 结果 0.30
  • 小心 MySQL 的隐式类型转换习惯。MySQL 允许 WHERE varchar_col = 123 这类隐式转换；PostgreSQL 更严格，会直接报错，因为它无法为字符串列使用数字索引。
sql
-- 错误！PostgreSQL 中会报 operator does not exist: text = integer
-- SELECT * FROM t_order WHERE order_no = 20260901;

-- 正确：显式写成字符串字面量
SELECT * FROM t_order WHERE order_no = '20260901';
  • 不要在 PostgreSQL 中依赖事务内读一致性而不改隔离级别。默认 READ COMMITTED 下，同一事务的两条 SELECT 之间若有其他事务提交，第二次会看到新数据。
  • 不要用 LIMIT 代替 "Top-N" 的业务保证。三者都要求 ORDER BY 与 LIMIT 搭配，否则顺序未定义。MySQL 的 LIMIT offset, count 与 PostgreSQL 的 LIMIT count OFFSET offset 语义相同但语法顺序相反，迁移时容易写错。
  • 对比"性能"时必须先明确负载类型。拿 OLTP 短查询吞吐去比较 PostgreSQL 与 MySQL，与拿复杂分析查询去比较，结论会完全相反。脱离负载画像的性能结论没有意义。
  • 不要低估迁移成本。真正的成本不在 DDL 改写，而在存储过程与触发器改写、驱动与 ORM 方言适配、SQL 语义差异（隐式转换、大小写、空字符串与 NULL 的区分）、运维工具链替换。

最佳实践 ​

  • 先做负载画像再做选型：写出"读/写比、单条 SQL 复杂度、数据量级、并发连接数、是否需要地理空间/向量/时序"这几个问题的答案，再对照上表判断。
  • 需要复杂查询、多模能力、无授权风险时优先 PostgreSQL：它几乎没有"能力天花板"，后续需求变化时不必换库。
  • 团队已有大量 MySQL 运维积累、业务以简单高并发写入为主时，MySQL 仍是合理选择：不必为了"技术先进"而迁移。
  • Oracle 的替换决策应从成本与合规出发：把许可、选件、硬件、运维人力折算成 TCO，再与 PostgreSQL 的迁移成本对比。
  • 新项目默认统一到 PostgreSQL：可以显著减少技术栈数量，降低跨库 SQL 方言带来的认知与维护成本。
  • 迁移用逻辑复制做增量同步，而不是停机 mysqldump。先用工具（如 pgloader）做全量，再用逻辑复制追增量，最后切换。
  • 迁移后必须做数据一致性校验：逐表比对行数、关键字段的 sum/count 聚合值、以及核心业务报表结果。
  • 不要为了"兼容性"在 PostgreSQL 里模拟其他数据库的方言。例如把 JSON 当字符串处理、用 text 模拟 TINYINT。用原生写法才能拿到该数据库设计上的收益。
  • 把选型结论写成 ADR（架构决策记录）：注明负载假设、候选方案、放弃理由与复审触发条件，避免同一问题反复讨论。

下一节

继续学习：版本选择建议（LTS / 最新版）

参考链接 ​