MySQL和PostgreSQL语法特性对比

发表于2020-03-24字数统计1.2k阅读时长8分

MySQL 和 PostgreSQL 是目前两大主流的数据库，MySQL 以简单易用和高性能著称，适合中小型 Web 应用和读写分离场景；PostgreSQL 则以其强大的功能（如 JSONB、窗口函数、递归查询）和高并发事务支持，更适合复杂查询、数据分析和高可靠系统。以下分别从语法特性、适用场景以及 SQL 示例对比两者的差异：

1. 语法特性对比

（1）数据类型

特性 MySQL PostgreSQL
布尔类型 用 TINYINT(1) 模拟 原生支持 BOOLEAN
JSON 支持 MySQL ≥5.7 支持 JSON 原生支持 JSON/JSONB（支持更复杂查询）
数组类型 不支持，需字符串模拟 原生支持数组（如 INT[]、TEXT[]）
UUID 不支持（需字符串存储） 原生支持 UUID 类型
枚举类型 支持 ENUM 支持 ENUM（需自定义类型）

示例：创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- MySQL
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    is_active TINYINT(1),        -- 模拟布尔值
    roles JSON                   -- JSON 类型
);

-- PostgreSQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    is_active BOOLEAN,           -- 原生布尔类型
    roles JSONB,                 -- JSONB 类型（存储更高效）
    tags TEXT[]                  -- 数组类型
);

（2）字符串处理

特性 MySQL PostgreSQL
字符串拼接 CONCAT(a, b, c) 支持 CONCAT(a, b, c) 或 `a
正则表达式 简单支持 REGEXP 支持丰富的正则语法 ~, ~*, !~
大小写敏感 默认不敏感（取决于排序规则） 严格区分大小写

示例：字符串操作

1
2
3
4
5
6
7
-- MySQL
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
SELECT * FROM users WHERE name REGEXP '^A.*';

-- PostgreSQL
SELECT first_name || ' ' || last_name AS full_name FROM employees;
SELECT * FROM users WHERE name ~ '^A.*';  -- 标准正则语法

（3）事务与锁

特性 MySQL PostgreSQL
事务隔离级别 支持标准隔离级别 支持标准隔离级别，并允许“可序列化”
行级锁 支持 支持更细粒度的锁（如 FOR UPDATE SKIP LOCKED）

示例：行级锁

1
2
3
4
5
-- MySQL
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;

-- PostgreSQL
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE SKIP LOCKED;  -- 跳过被锁定的行

2. 高级功能对比

特性 MySQL PostgreSQL
窗口函数 MySQL ≥8.0 支持 原生支持更复杂的窗口函数
全文检索 支持 MATCH() AGAINST() 支持 tsvector 和 tsquery（更灵活）
存储过程 支持 支持，且功能更强大
地理空间数据 支持（GIS 扩展） 原生 PostGIS 扩展（行业标准）

示例：窗口函数

1
2
3
4
-- MySQL & PostgreSQL（通用写法）
SELECT name, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

3. 适用场景

（1）MySQL 适用场景

  • Web 应用读写密集场景
    MySQL 写性能优化较好，适用于高并发 OLTP 系统（如电商订单、用户管理）。
  • 简单数据模型
    对复杂查询和 JOIN 需求较少的场景。
  • 云原生集成
    与云服务（如 AWS RDS、阿里云）深度集成，适合快速部署。
  • 示例场景： 社交应用的 feed 流、支付系统日志记录。

（2）PostgreSQL 适用场景

  • 复杂查询与分析
    支持窗口函数、CTE（公共表表达式）、JSONB 查询，适合 OLAP 场景。
  • 高扩展性要求
    支持自定义函数、扩展插件（如 PostGIS、TimescaleDB），适合物联网、GIS 系统。
  • 数据一致性要求高
    严格的 ACID 特性，适合金融、科研领域。
  • 示例场景： 地理位置数据处理（轨迹分析）、实时数据分析。

4. 具体语法差异示例

（1）分页查询

1
2
3
4
5
6
7
-- MySQL
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20; -- 第3页（每页10条）

-- PostgreSQL（兼容 MySQL，也支持 SQL:2008 语法）
SELECT * FROM products ORDER BY id OFFSET 20 LIMIT 10; 
-- 或
SELECT * FROM products ORDER BY id FETCH NEXT 10 ROWS ONLY OFFSET 20;

（2）日期处理

1
2
3
4
5
6
7
-- MySQL
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);        -- 7 天后
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

-- PostgreSQL
SELECT NOW() + INTERVAL '7 days';             -- 7 天后
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');

（3）递归查询（CTE）

1
2
3
4
5
6
7
8
9
-- MySQL (8.0+) 和 PostgreSQL 通用语法
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

5. 性能调优差异

特性 MySQL PostgreSQL
索引类型 B-Tree、全文索引、空间索引 B-Tree、GIN、GiST、BRIN、哈希索引
查询优化器 基于规则的优化器（RBO） 基于成本的优化器（CBO）
并行查询 MySQL ≥8.0 有限支持 原生支持并行查询

6. 如何选择？

  • 选择 MySQL：
    需要快速读写、高频事务处理，且数据结构较为简单的场景（如电商、内容平台）。
  • 选择 PostgreSQL：
    需要复杂分析、自定义类型扩展、高可靠性的业务（如金融系统、地理信息处理）。

通过对比可见，PostgreSQL 在功能扩展性和复杂查询支持上更胜一筹，而 MySQL 在简单场景的读写性能优化上更有优势。根据具体业务需求选择更合适的数据存储方案。