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,可正确执行以上操作
- 在 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 规范,但使用起来却很方便。
规范 | MySQL | PostgreSQL |
---|---|---|
区分大小写 | LIKE 不区分大小写(默认COLLATE ,可以指定区分大小写的 COLLATE ,比如 _cs 或 _bin 为后缀的) | LIKE 区分大小写,如果要不区分大小写可以使用大小写转换函数或直接使用 ILIKE 代替 |
对象名需要使用"" | 除了别名外,表名、字段名、数据库名都使用`` | 别名、表名、字段名、数据库名都使用"" |
字符串需要使用'' | 除了'' ,MySQL 还允许字符串使用"" | 严格要求使用'' |
JSON
-
MySQL
- MySQL 5.7 开始支持
JSON
数据类型,MySQL 8 扩展了JSON
类型的操作
- MySQL 5.7 开始支持
-
PostgreSQL
- 天然支持
JSON
数据类型和操作
- 天然支持
选型
近期查看了 MySQL 8,发现它的 utf8mb4
已经得到了长足的进步,对于我来说,后续的项目可能会逐步由 PG 过渡到 MySQL 8(注意,特指 MySQL 8 及其以后的版本)。
如果你也遇到了数据库选型的问题,希望本文能给到你一些启发。
如果哪天 PostgreSQL 支持无符号整型和有符号整型,我会毫不犹豫使用 PostgreSQL —— 但从官方表述看,这个特性实现的机会渺茫