MySQL和PostgreSQL对比与选型

52474
2022/10/12 23:27:31

MySQL 和 PostgreSQL 简要对比

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

VARCHAR

  • MySQL:

    • 必须指定长度
    • 可以指定长度
    • 如果不指定长度,那么和 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 规范

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

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

JSON

  • MySQL

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

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

选型

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

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

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