Postgres 与 MySQL 终极对比博客文章

我或许应该先声明一下，我热爱 Postgres，而且我死也不会mysql>再看到这种提示符了。这并非一个客观的比较——但无论如何，那些提示符也没什么意思。

场景：两个应用程序使用Massive.js来存储和检索数据。Massive 在设计上与 Postgres 紧密耦合。Massive 的特化使其能够利用一些仅存在于部分或根本不存在于其他关系数据库中的功能，从而以比传统对象关系映射器更轻量、更“JavaScript 化”的方式简化数据访问。Massive 非常适合处理各种任务，因为其基础操作简单，而且对于复杂的、无论如何都需要编写 SQL 语句的操作来说，它非常实用……您只需编写 SQL 语句，将其存储在一个中心位置以便重复使用，然后通过 API 轻松运行。

如果你需要支持其他 RDBMS，那么 Massive 就没那么有用了。理想情况下，你应该事先了解这一点。总之，事情总会发生，有时你不得不回答这个问题：“如果我们需要在 MySQL 上运行这些轻量级但紧密耦合的数据层应用程序，会是什么样子？”

答案显而易见：不好，但不太明显的是它到底有多不好。我知道 Postgres 能做到一些 MySQL 做不到的事情，但我也知道后者有很多我从未尝试过的功能。所以，当我开始着手解决这个问题时，我开始做笔记。以下是我找到的所有记录。

架构布局

既然我们基本上已经摆脱了“无模式”未来的集体幻想，那么可以说，数据存储最重要的方面是信息在数据库中的建模方式。Postgres 和 MySQL 都是关系数据库，将记录分组到严格定义的表中。但在这个主题中，还有很大的变化空间。

多个模式

首先要说的是：“模式”的含义并不总是相同的。对于 MySQL 来说，“模式”与“数据库”同义。对于 Postgres 来说，“模式”是数据库中的命名空间，它允许你将表、视图和函数组合在一起，而无需将它们拆分到不同的数据库中。

MySQL 在这方面的简单性通过提供跨数据库查询得到了改善：

SELECT *
FROM db1.table1 t1
JOIN db2.table2 t2 ON t2.t1_id = t1.id;

使用 Postgres，您可以跨模式工作，但如果您需要查询不同数据库中的信息，那就是...

外部数据包装器

外部数据包装器让 Postgres 几乎可以与任何将信息表示为离散记录的对象进行通信。您可以在 Postgres 数据库中创建“外部表”，也SELECT可以JOIN像其他表一样使用它——只不过在底层，它实际上是在读取 CSV 文件、与其他 DBMS 通信，甚至查询 REST API。这项功能非常强大，以至于 NoSQL 的忠实拥趸 MongoDB偷偷地在 Postgres 之上使用外部数据包装器构建了他们的 BI 连接器。Multicorn 允许您使用 Python 编写新的 FDW，您甚至不需要了解 C 语言！

Oracle 和 SQL Server 都提供了注册外部数据源的功能，但据我所知，Postgres 提供的扩展性是最高的。MySQL 除了上面提到的跨数据库查询支持外，没有任何功能。

表继承

继承通常被认为是面向对象编程语言而非数据库的属性，但 Postgres 从技术上来说是一个ORDBMS（对象关系数据库管理系统）。因此，您可以拥有一个cities包含name和列的表population，以及一个capitals继承 定义cities但添加一of_country列（当然，该列仅与首都相关）的表。如果您SELECT从cities，您将获得来自 的行capitals——它们也是城市！当然，您可以SELECT name FROM ONLY cities排除首都。这是一个小众功能，但当您有合适的用例时，它确实会大放异彩。

MySQL 作为一种传统的 RDBMS，不会这样做。

物化视图

物化视图与常规视图类似，不同之处在于指定查询的结果以物理方式存储（“物化”），并且必须显式刷新。这允许数据库开发人员在结果无需实时获取的情况下，缓存较慢查询的结果。

Oracle 有物化视图，SQL Server 的索引视图类似，但 MySQL 没有物化视图支持。

检查约束

约束通常用于确保不存储无效数据。最常见的约束是NOT NULL，它阻止插入或更新非空列中没有值的记录。当对另一个表中记录的引用无效时，外键约束也起着同样的作用。检查约束是最灵活的，可以验证任何可以放入子句中的谓词WHERE——例如，断言价格必须为正数，或者美国邮政编码必须为五位数。

根据 MySQL 文档：该CHECK子句被所有存储引擎解析但忽略。

JSONB 和索引

Postgres 和 MySQL 都拥有JSON列类型（MySQL 的替代品 MariaDB 也有，但目前只是 的别名LONGTEXT）以及用于构建、处理和查询 JSON 字段的函数。Postgres 实际上更进一步，提供了一种JSONB将输入数据处理为二进制格式的类型。这意味着写入速度稍慢，但查询速度要快得多。

这也意味着您可以索引二进制数据。GIN（广义倒排索引）允许查询检查特定键或键值对的存在，以避免扫描每条记录以查找匹配项。如果您运行查询并深入查询WHERE子句中的 JSON 字段，这将非常有用。

函数定义的默认值

DEFAULT是语句中列的实用规范CREATE TABLE。在最简单的层面上，如果语句没有给出明确的值，它可以用于将布尔字段的基线设置为true或。但除了设置标量值之外，您还可以做更多的事情：时间戳可以默认为，UUID 可以设置为各种生成 UUID 的函数，任何其他字段可以设置为您想要编写的任何函数的返回值——一切皆有可能！falseINSERTnow()

除非您使用 MySQL，在这种情况下，您可以在子句中引用的唯一函数DEFAULT是now()。

类型差异

不过，布局只是故事的一部分。类型支持的差异也同样重要。健壮的类型系统的好处在于，它能够使数据库架构师以尽可能高的精度表示信息。如果某个值难以或无法用内置类型表示，那么开发人员的处理难度也会随之增加。如果为了适应数据而不得不做出妥协，那么这可能会影响整个应用程序。某些类型甚至会影响整体数据库设计，例如数组和枚举。通常来说，选项越多越好。

UUID

Postgres 有 UUID 类型，而 MySQL 没有。如果您想在 MySQL 中存储 UUID，您可以选择 CHAR（如果您希望值像 UUID 一样易于阅读）或 BINARY（如果您希望速度更快但手动操作更困难）。Postgres 还生成更多类型的 UUID。

布尔值

布尔值看似很基础的类型！然而，MySQL 的布尔值实际上是 TINYINT(1) 的别名。这就是为什么查询结果会显示 0 或 1 而不是true或false。这也是为什么你可以将一个表面上是布尔值的字段的值设置为 2。试试看！

Postgres：具有适当的布尔值。

Varlena 和长度

然而，MySQL 并非唯一一个以奇怪方式为标准类型添加别名的数据库。Postgres 中的 CHAR、VARCHAR 和 TEXT 类型都是同一结构的别名表示——唯一的区别是，如果指定了长度限制，则会强制执行。文档指出，这实际上会降低速度，并建议将无界文本直接定义为 TEXT 类型，而不是赋予任意的最大长度。

这里发生的事情是，Postgres 使用一种称为varlena（可变长度数组）的数据结构来存储信息。varlena 的前四个字节存储值的长度，这使得数据库可以轻松地从存储中获取整个值。TEXT 只是使用此结构的类型之一，但它是最常见的。

如果 varlena 的长度超过了内联存储的长度，数据库会使用一种名为 TOAST（“超大属性存储技术”）的系统，以透明的方式将其卸载到扩展存储中。除非经过精心设计和索引，否则包含涉及 TOASTable 字段的谓词的查询在处理大型表时可能性能不佳。不过，当数据库返回记录时，很容易遵循 TOAST 指针，因此在大多数情况下，开销几乎不可察觉。

就大多数人而言，所有这些的结果是：使用 Postgres，您只需担心对需要长度限制的字段设置长度限制。如果没有明确的要求限制字段中可以容纳多少信息，则无需选择一个任意数字并尝试将其与页面大小匹配。

数组

记录里居然有非标量值！简直疯了！猫狗竟然住在一起！任何使用过 JSON、XML、YAML 甚至 HTML 的人都知道，信息并不总是扁平的。关系型架构传统上要求将所有向量（更不用说更复杂的值）拆分到新表中。有时这样做很有用，但通常情况下，它只会徒增复杂性，而没有任何实际意义。内联数组使许多任务（例如标记记录）变得更加轻松。

Postgres 有数组，Oracle 也有；MySQL 和 SQL Server 没有。

自定义类型

如果内置类型不够用，您可以随时添加自己的类型。自定义类型允许您将值定义为您想要的。域是一个相关概念：类型（自定义或内置）对值强制约束。例如，您可以创建一个域，将邮政编码表示为 TEXT 值，并在CHECK子句中使用正则表达式来确保值由五位数字组成，后跟一个短划线和另外四位数字（可选）。

如果您使用的是 Postgres，情况就是这样。Oracle 和 SQL Server 都提供了一些自定义类型功能，但 MySQL 却没有任何功能。您甚至无法使用表级CHECK约束，因为引擎会直接忽略它们。

枚举

枚举并没有得到足够的重视。如果我看到每个代表一组固定潜在值的 INT（或者更糟的是 VARCHAR）字段都能赚到一美元，我可能仍然无法退休，但至少可以度过一个相当不错的夜晚。当然，使用枚举也有一些缺点：添加新值需要 DDL，而且根本无法删除值。但枚举的适用场景仍然相当常见。

MySQL 和 Postgres 都提供枚举。关键区别在于 Postgres 的枚举是真正的可重用类型。MySQL 的枚举更像是那些被忽略的CHECK约束，它为单个表中的单个列指定一个有效的值列表。允许布尔列包含 -100 有什么改进吗？

查询数据

数据建模就讲到这里。接下来还有一大半：实际操作存储的信息。SQL 本身分为两部分：“数据定义语言”（定义数据库结构）和“数据操作语言”。后者包括SELECT、INSERT和其他语句，大多数人一听到“SQL”这个名字就会想到它们。与建模一样，Postgres 和 MySQL 在查询方面也存在很大差异。

回归

自动生成主键可以大大简化数据存储。但有一个问题：当你向表中插入一条新记录时，你并不知道它的主键值被设置成了什么。大多数关系数据库会在你调用特殊函数时告诉你最后一个自动生成的主键是什么；有些数据库，比如 SQL Server，甚至允许你筛选到你感兴趣的单个表。

Postgres 的子句功能远超预期RETURNING。任何写入语句 -- INSERT、UPDATE、DELETE-- 都可以以 结尾，它对受影响的记录起到RETURNING [column-list]的作用。它会根据您刚才的操作返回整个记录集，或者您可以将您感兴趣的内容限制在某些列中。SELECTRETURNING *

这意味着你可以这样做：

INSERT INTO foos (name)
VALUES ('alpha'), ('beta')
RETURNING *;

 id │ name  
────┼───────
  1 │ alpha
  2 │ beta
(2 rows)

LAST_INSERT_ID()使用 MySQL，你只能在添加新记录后调用。如果你添加了多条记录，LAST_INSERT_ID它只会返回最早的新 ID，剩下的 ID 就需要你自己计算了。当然，这只适用于整数主键。

UPDATE对于s 和s， MySQL 也没有与此功能对应的功能DELETE。竞争对手 MariaDB 支持RETURNINGon DELETE，但不支持任何其他类型的语句。

通用表表达式

通用表表达式（CTE）允许将复杂的查询分解成独立的部分，然后组装成一个完整的查询。你可以这样写：

WITH page_visits AS (
  SELECT p.id, p.site_id, p.title, COUNT(*) AS visits
  FROM pages AS p
  JOIN page_visitors AS v ON v.page_id = p.id
  GROUP BY p.id, p.site_id, p.title
), max_visits AS (
  SELECT DISTINCT ON (site_id)
    site_id, title, visits
  FROM page_visits
  ORDER BY site_id, visits DESC
)
SELECT s.id, s.name,
  max_visits.title AS most_popular_page,
  SUM(page_visits.visits) AS total_visits
FROM sites AS s
JOIN page_visits ON page_visits.site_id = s.id
JOIN max_visits ON max_visits.site_id = s.id
GROUP BY s.id, s.name, max_visits.title
ORDER BY total_visits DESC;

在第一个查询中，我们汇总访问次数；在第二个查询中，我们利用DISTINCT ON第一个查询的结果过滤掉除最热门页面之外的所有页面；最后，我们将两个中间结果连接起来，得到我们想要的输出。CTE 是一种非常易读的提取查询逻辑的方法，它能让你在一条语句中完成一些其他方法无法完成的操作。

MySQL 确实有 CTE！但是：多亏了该RETURNING子句，Postgres 可以将记录写入 CTE并对其结果进行操作。这对于应用程序逻辑来说意义重大。下一个查询将一条记录写入 CTE，然后将相应的条目添加到连接表中——所有这些都在同一个事务中完成。

WITH wine AS (
  INSERT INTO wines (name, year)
  VALUES ('Herrenreben', 2015)
  RETURNING id
), reviewer AS (
  SELECT id
  FROM reviewers
  WHERE name = 'Wine Enthusiast'
)
INSERT INTO wine_ratings (wine_id, reviewer_id, score)
SELECT wine.id, reviewer.id, 92
FROM wine
JOIN reviewer ON TRUE;

铸件

有时查询需要将值视为具有其他类型，无论是存储它还是以某种方式对其进行操作。Postgres 甚至允许您使用 定义类型之间的额外转换CREATE CAST。

MySQL 支持将值强制转换为二进制、字符/Nchar、日期/日期时间/时间、十进制、JSON 以及有符号和无符号整数。但 Tinyint 不在此列表中，因为布尔值实际上就是 Tinyint，这意味着当您需要将值强制转换为 true 或 false 以存储在“布尔”列中时，只能使用条件语句。

横向连接

横向连接与相关子查询基本相似，因为它会针对当前结果集的每一行执行。然而，相关子查询仅限于为列表SELECT或WHERE子句返回单个值；FROM子句中的子查询是独立运行的。横向连接可以引用结果集其余部分的信息：

CREATE TABLE docs (id serial, body jsonb);

INSERT INTO docs (body) VALUES ('{"a": "one", "b": "two"}'), ('{"c": "three"}');

SELECT docs.id, keys.*
FROM docs
JOIN LATERAL jsonb_each(docs.body) AS keys ON TRUE;

 id │ key │ value  
────┼─────┼─────────
  1 │ a   │ "one"
  1 │ b   │ "two"
  2 │ c   │ "three"
(3 rows)

它还可以调用unnest返回多行和多列的表函数：

CREATE TABLE multiple_arrays(arr1 int[], arr2 int[]);

INSERT INTO multiple_arrays (arr1, arr2)
VALUES
    ('{1,2,3}', '{4,5}'),
    ('{6,7}', '{8,9,10}');

SELECT raw.*
FROM multiple_arrays
JOIN LATERAL unnest(arr1, arr2) AS raw ON TRUE;

 unnest │ unnest 
────────┼────────
      1 │ 4
      2 │ 5
      3 │ (null)
      6 │ 8
      7 │ 9
 (null) │ 10
(6 rows)

Oracle 和 SQL Server 提供了类似的功能，LATERAL前者使用关键字，而后者使用CROSS APPLY/ OUTER APPLY。MySQL 没有。

可变函数参数

函数！过程，如果你相信这种区分的话！它们很棒！你可以声明可变参数——在其他语言中称为“varargs”或“rest parameter”——将任意数量的参数拉入以最终参数命名的单个集合中。

在 Postgres 中。

谓词运算

一些有用的操作允许WHEREPostgres 使用更具表现力的子句：

• IS DISTINCT FROM及其对应项IS NOT DISTINCT FROM提供了对空值敏感的相等性测试。由于 Null 表示值不存在WHERE field <> 1，因此通常不具有可比性，因此该谓词不会返回值为fieldNull 的记录。返回所有非 1 的WHERE field IS DISTINCT FROM 1记录，包括值为 Null 的记录。field
• ILIKE是不区分大小写的LIKE操作。MySQL 确实具有不区分大小写的模式匹配功能，但这取决于你的排序规则，并且无法在每个查询的基础上进行切换（公平地说，默认排序规则是不区分大小写的）。
• ~、~*、!~和!~*构成一组 POSIX 正则表达式测试：匹配、不区分大小写匹配、不匹配和不区分大小写匹配。MySQL 确实有 和REGEXP；NOT REGEXP然而，Postgres 的实现有向前看和向后看。

常规数据库工作

以上就是我发现的架构和查询语言功能方面的差距。不过，我还发现了一些其他值得一提的问题：

依赖项

MySQL 并不关心数据库对象之间的依赖关系。你可以告诉它删除视图或过程所依赖的表，它会直接删除它。直到下次尝试调用该视图或过程时，你才会知道出了什么问题。Postgres 可以帮你省去很多麻烦，除非你非常确定，并且用 删除依赖项CASCADE。

触发器和表写入

光是提到触发器，可能就让一些人望而却步了。说实话，它们没那么糟糕（好吧，它们确实可能很糟糕，但这并不是它们的错）。总之，重点是：有时你需要编写一个触发器，用来修改被激活的表中的其他行。

嗯，在 MySQL 中你不能这么做。

结束了吗？

这可能让我筋疲力尽，但我确信这仍然不是 Postgres 和 MySQL 之间功能差异的详尽列表。我一开始确实表明了自己的偏好，但花了六周时间进行转换比较之后，结果还是相当令人失望。我认为仍然有理由选择 MySQL——但我不确定这些理由是否是技术层面的。