MySQL和PostgreSQL对比与选型

MySQL 和 PostgreSQL 简要对比

下面基于本人的经验，对两款数据库产品的某些重要特性的差异进行对比。

VARCHAR

• MySQL：

  • 必须指定长度

• PostgreSQL

  • 可以指定长度
  • 如果不指定长度，那么和 TEXT 一样。即使这种情况下，依然可以设置默认值

MySQL：

PostgreSQL

• MySQL
  • MySQL 8 之前
    • 默认字符集是 latin1，需要显式指定不同的字符集
  • MySQL 8
    • 默认字符集是 utf8mb4
    • 之前版本的 utf8 指向 utf8mb3
    • 后续版本可能改变 utf8 的指向
  • 如要存储 Emoji 等特殊字符，需要使用 utf8mb4
  • UNIQUE 或其它索引
    • 在 MySQL 5.7 之前，VARCHAR(255) CHARSET=utf8mb4 UNIQUE将报错：MySQL error: The maximum column size is 767 bytes
    • MySQL 5.7 及 MySQL 8，可正确执行以上操作
• PostgreSQL
  • 默认字符集 utf8
  • 可直接存储 Emoji 等特殊字符
  • 可安全地在创建 UNIQUE 约束和其它索引

• MySQL 8 之前
  • 默认字符集是 latin1，需要显式指定不同的字符集
• MySQL 8
  • 默认字符集是 utf8mb4
  • 之前版本的 utf8 指向 utf8mb3
  • 后续版本可能改变 utf8 的指向
• 如要存储 Emoji 等特殊字符，需要使用 utf8mb4
• UNIQUE 或其它索引
  • 在 MySQL 5.7 之前，VARCHAR(255) CHARSET=utf8mb4 UNIQUE将报错：MySQL error: The maximum column size is 767 bytes
  • MySQL 5.7 及 MySQL 8，可正确执行以上操作

• 默认字符集是 latin1，需要显式指定不同的字符集

• 默认字符集是 utf8mb4
• 之前版本的 utf8 指向 utf8mb3
• 后续版本可能改变 utf8 的指向

• 在 MySQL 5.7 之前，VARCHAR(255) CHARSET=utf8mb4 UNIQUE将报错：MySQL error: The maximum column size is 767 bytes
• MySQL 5.7 及 MySQL 8，可正确执行以上操作

• 默认字符集 utf8
• 可直接存储 Emoji 等特殊字符
• 可安全地在创建 UNIQUE 约束和其它索引

CHECK 约束

• MySQL
  • MySQL 5.7之前不支持
  • MySQL 5.7能定义，但没有任何作用
  • MySQL8 支持
• PostgreSQL
  • 完全支持

• MySQL 5.7之前不支持
• MySQL 5.7能定义，但没有任何作用
• MySQL8 支持

• 完全支持

整型

• MySQL
  • 所有整数类型均可添加 UNSIGNED，保证只能存储正整数
  • 有 AUTO_INCREMENT 约束的 USIGNED 仍然有效
• PostgreSQL
  • 所有整数类型均为有符号类型，需要借助 CHECK 约束来实现只能存储正整数
  • SERIAL/BIGSERIAL 只能存储正整数
  • 无论是 CHECK 约束，还是 SERIAL/BIGSERIAL，占用的资源是没有变的，就是说，会导致一半的（负数部分）资源浪费
  • 如要安全地实现 MySQL 的 INT UNSIGNED：
    • 使用 BIGINT/BIGSERIAL
    • 借助 CHECK 约束
    • 浪费一半资源

• 所有整数类型均可添加 UNSIGNED，保证只能存储正整数
• 有 AUTO_INCREMENT 约束的 USIGNED 仍然有效

• 所有整数类型均为有符号类型，需要借助 CHECK 约束来实现只能存储正整数
• SERIAL/BIGSERIAL 只能存储正整数
• 无论是 CHECK 约束，还是 SERIAL/BIGSERIAL，占用的资源是没有变的，就是说，会导致一半的（负数部分）资源浪费
• 如要安全地实现 MySQL 的 INT UNSIGNED：
  • 使用 BIGINT/BIGSERIAL
  • 借助 CHECK 约束
  • 浪费一半资源

• 使用 BIGINT/BIGSERIAL
• 借助 CHECK 约束
• 浪费一半资源

多表连接

• MySQL
  • 不支持 FULL OUTER JOIN，需要借助 UNION [ALL] 来实现
• PostgreSQL
  • 支持 FULL OUTER JOIN

• 不支持 FULL OUTER JOIN，需要借助 UNION [ALL] 来实现

• 支持 FULL OUTER JOIN

RETURNING

• MySQL
  • 不支持任何形式的 RETURNING 子句
• PostgreSQL
  • UPDATE/INSERT/DELETE 等均支持 RETURNING 子句，在某些场景下，可以省去发送第二条 SELECT 语句的开销。

• 不支持任何形式的 RETURNING 子句

• UPDATE/INSERT/DELETE 等均支持 RETURNING 子句，在某些场景下，可以省去发送第二条 SELECT 语句的开销。

布尔值

• MySQL
  • 通常使用 TINYINT 模拟
• PostgreSQL
  • 天然支持 BOOLEAN 数据类型

• 通常使用 TINYINT 模拟

• 天然支持 BOOLEAN 数据类型

角色与权限

• MySQL
  • MySQL 8 之前：通过用户分别设置权限
  • MySQL 8：引入角色概念
• PostgreSQL
  • 天然以角色的形式管理权限

• 天然以角色的形式管理权限

SQL 规范

相对于 PostgreSQL 而言，MySQL 对 SQL 规范的支持并不是很好（包括上文讲的 FULL OUTER JOIN）。MySQL 有很多特性并不符合 SQL 规范，但使用起来却很方便。

JSON

• MySQL

  • MySQL 5.7 开始支持 JSON 数据类型，MySQL 8 扩展了 JSON 类型的操作

• PostgreSQL

  • 天然支持 JSON 数据类型和操作

MySQL

• MySQL 5.7 开始支持 JSON 数据类型，MySQL 8 扩展了 JSON 类型的操作

PostgreSQL

• 天然支持 JSON 数据类型和操作

选型

虽然本站目前所有的专题，包括 axum.rs 网站本身用的都是 PostgreSQL 数据库（以下简称 PG），但 PG 整型数据没有提供正整数的短板，让我日渐怀念起了前些年用的 MySQL，这个问题就好比，rust 只提供了 i32，而没有 u32 一样。假设 rust 只有 i32/i64等，没有 u32/u64等，那么日常开发将会多么无奈。

近期查看了 MySQL 8，发现它的 utf8mb4 已经得到了长足的进步，对于我来说，后续的项目可能会逐步由 PG 过渡到 MySQL 8（注意，特指 MySQL 8 及其以后的版本）。

如果你也遇到了数据库选型的问题，希望本文能给到你一些启发。

如果哪天 PostgreSQL 支持无符号整型和有符号整型，我会毫不犹豫使用 PostgreSQL —— 但从官方表述看，这个特性实现的机会渺茫

如果哪天 PostgreSQL 支持无符号整型和有符号整型，我会毫不犹豫使用 PostgreSQL —— 但从官方表述看，这个特性实现的机会渺茫