PostgreSQL 入门 - SQL 数据库

01. PostgreSQL 跟 MySQL 有什么不一样

PostgreSQL（简称 PG）和 MySQL 是两大开源数据库，但设计理念差别挺大。PG 追求标准和功能完备，MySQL 追求简单和速度。 PG 的优势：对 SQL 标准的支持更完整；有真正的全文搜索、地理空间数据（PostGIS）、JSONB（比 MySQL 的 JSON 强太多了）；复制方式多；窗口函数和 CTE 更成熟。 MySQL 的优势：读多写少的场景简单粗暴；几百万访问量的网站用 MySQL 够用；PHP 时代积累的生态太好。选型建议：新项目没有历史包袱选 PG。需要地理信息选 PG。简单 CRUD Web 应用 MySQL 足够。

sql

-- PG 和 MySQL 都支持标准 SQL，但细节有差异
-- PG: SERIAL 自增，MySQL: AUTO_INCREMENT
-- PG: TEXT 无限制，MySQL: VARCHAR/TEXT
-- PG: 严格模式默认开启，MySQL: 需手动配置

02. 安装与 psql 客户端

psql 是 PG 的命令行客户端，相当于 MySQL 的 mysql 命令。它比 mysql 强大不少——支持自动补全、语法高亮、反斜杠开头的元命令特别好用。装好 PG 后直接用 psql 连上：psql -U postgres -d mydb。进去后反斜杠 dt 看所有表，反斜杠 d 表名看表结构，反斜杠 l 列所有数据库，反斜杠 du 列所有用户。 PG 的用户和数据库是强绑定的——默认情况下同名用户可以免密登录同名数据库。Peer 认证（本机用操作系统用户验证）是 PG 的默认方式。

bash

# Ubuntu 安装
sudo apt install postgresql postgresql-contrib

# 切换到 postgres 用户
sudo -u postgres psql

# psql 元命令
\l          # 数据库中所有表
\c mydb     # 切换到 mydb
\dt         # 所有表
\d users    # users 表结构
\du         # 所有用户
\q          # 退出

03. JSONB 数据类型

JSONB 是 PG 的杀手级特性——在关系型数据库里给你 NoSQL 的体验。它把 JSON 数据以二进制格式存储，既能存不规则的文档数据，又能建索引快速查询。跟 MySQL 的 JSON 比：JSONB 支持 GIN 索引（查询飞快）、支持 JSONPath 查询、支持部分更新。PG 的 JSONB 比 MySQL 的 JSON 类型稳定得多。什么时候用 JSONB？配置数据（每行字段不一样）、API 返回的原始数据（先存下来再说）、用户自定义属性（不同用户有不同字段）。但不适合当主键、不适合频繁 JOIN。

sql

-- 建 JSONB 列
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  attributes JSONB
);

-- 插入数据
INSERT INTO products (name, attributes)
VALUES ('iPhone', '{"color": "blue", "storage": 256}');

-- JSONB 查询
SELECT * FROM products WHERE attributes->>'color' = 'blue';
SELECT * FROM products WHERE attributes @> '{"storage": 256}';

-- 给 JSONB 建索引
CREATE INDEX idx_attrs ON products USING GIN (attributes);

GIN 索引对包含、存在键这些操作符有效。普通等于不需要 GIN，B-tree 就行。

04. 窗口函数与 CTE

PG 的窗口函数写法跟 MySQL 一样（都是 SQL 标准），但 PG 的实现更完整。 CTE（Common Table Expression，WITH 子句）是 PG 的一大亮点。它让你把一个查询定义为临时的虚拟表，后面可以在主查询里多次引用。比子查询清晰，比临时表轻量。 PG 的 CTE 还能做递归查询——查组织架构树、分类树、评论回复树。语法是 WITH RECURSIVE，逐层展开。这在处理树形数据时特别实用。

sql

-- CTE 基本用法
WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_sal
  FROM employees GROUP BY department
)
SELECT e.name, e.salary, d.avg_sal
FROM employees e JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_sal;

-- 递归 CTE
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, t.level + 1
  FROM employees e JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree;

05. PG 特有的高级特性

PG 有一些 MySQL 没有或不如的高级特性：物化视图——把查询结果存成物理表，定期刷新。复杂报表不想每次都跑时特别好用。表继承——子表可以继承父表的列，查询父表时自动包含子表数据。做分区表和历史数据归档很实用。全文搜索——内置 tsvector 和 tsquery 类型，支持中文分词（需装扩展），不用单独搭 Elasticsearch 就能完成搜索。扩展系统——PG 有超丰富的扩展：PostGIS（地理信息）、pgcrypto（加密）、uuid-ossp（UUID 生成）、pg_stat_statements（查询统计）。装扩展就一句 CREATE EXTENSION。

sql

-- 物化视图
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount)
FROM orders GROUP BY month;
REFRESH MATERIALIZED VIEW monthly_sales;

-- 安装扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";

pg_stat_statements 是 PG 的性能分析神器——记录所有 SQL 的执行统计，相当于 MySQL 的 performance_schema。

知识测验

第 1/5 题正确 0

PG 相比 MySQL，JSONB 的主要优势是？