MySQL和PostgreSQL对比与选型

AXUM和MySQL
52094
2022/10/12 23:27:31
axum数据库postgresqlmysql

MySQL 和 PostgreSQL 简要对比

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

VARCHAR

    • 必须指定长度

  • PostgreSQL

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

字符集与特殊字符

  • 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 约束和其它索引

CHECK 约束

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

整型

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

多表连接

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

RETURNING

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

布尔值

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

角色与权限

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

SQL 规范

规范MySQLPostgreSQL
区分大小写LIKE不区分大小写（默认COLLATE，可以指定区分大小写的 COLLATE，比如 _cs 或 _bin 为后缀的）LIKE区分大小写，如果要不区分大小写可以使用大小写转换函数或直接使用 ILIKE代替
对象名需要使用""除了别名外，表名、字段名、数据库名都使用``别名、表名、字段名、数据库名都使用""
字符串需要使用''除了''，MySQL 还允许字符串使用""严格要求使用''

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等，那么日常开发将会多么无奈。

5.7 版本之前的 MySQL 最大的问题是字符集，相较于 PG 没有正整数的问题，在我看来字符集的问题更大，所以我选择了 PG。

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

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