5分钟带你入门PostgreSQL

科威舟的代码笔记
161 阅读4分钟

本文结合PostgreSQL与MySQL的关键对比分析，帮助快速掌握核心操作并理解两者差异。内容基于最新PostgreSQL 16和MySQL 8.0，涵盖开发、运维高频场景。

一、数据库连接与基本操作

操作PostgreSQLMySQL差异说明
命令行连接psql -h 主机 -p 端口 -U 用户 -d 数据库mysql -h 主机 -P 端口 -u 用户 -p 数据库PostgreSQL用-U，MySQL用-u [citation:1][citation:4]
退出命令行\qexit 或 \qPostgreSQL仅支持\q
查看所有数据库\lSHOW DATABASES;PostgreSQL用反斜杠命令 [citation:4]
切换数据库\c 数据库名USE 数据库名;

⚠️ 连接安全注意
PostgreSQL默认需配置pg_hba.conf开放访问（如添加host all all 0.0.0.0/0 md5），而MySQL通过GRANT直接授权[citation:4]。

二、核心对象管理

1. 表与字段操作

-- 创建表（自增主键）
-- PostgreSQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,       -- 自增主键
    name VARCHAR(50) NOT NULL,
    active BOOLEAN DEFAULT true  -- 原生布尔类型
);
-- MySQL
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    active TINYINT(1) DEFAULT 1  -- 用TINYINT模拟布尔
);

关键差异：

  • 自增键：PostgreSQL用SERIAL（背后是序列），MySQL用AUTO_INCREMENT [citation:2][citation:7]
  • 布尔类型：PostgreSQL原生BOOLEAN，MySQL用TINYINT(1) [citation:2][citation:6]

2. 索引管理

-- 创建索引
CREATE INDEX idx_name ON users(name);  -- 两者语法相同

-- 查看索引
\di users                             -- PostgreSQL
SHOW INDEX FROM users;                -- MySQL

进阶能力：
PostgreSQL支持更多索引类型（GIN/GiST用于JSON/全文检索），MySQL以B-Tree为主[citation:6]。

三、数据操作（DML）对比

1. 插入与更新

-- 插入冲突处理（UPSERT）
-- PostgreSQL
INSERT INTO users (id, name) 
VALUES (1, 'Tom')
ON CONFLICT (id) DO UPDATE SET name = 'Tom'; 

-- MySQL
INSERT INTO users (id, name)
VALUES (1, 'Tom')
ON DUPLICATE KEY UPDATE name = 'Tom';

2. 查询过滤

-- 正则匹配
SELECT * FROM logs WHERE message ~ 'error.*';  -- PostgreSQL
SELECT * FROM logs WHERE message REGEXP 'error.*';  -- MySQL

-- NULL处理
SELECT COALESCE(name, 'Unknown') FROM users;  -- 两者相同

差异重点：

  • 正则运算符：PostgreSQL用~，MySQL用REGEXP [citation:9]
  • 字符串拼接：PostgreSQL用||，MySQL用CONCAT()（注意NULL处理不同）[citation:3][citation:9]

四、高级查询特性

1. 分页与排序

-- 分页查询
SELECT * FROM orders ORDER BY id DESC OFFSET 20 LIMIT 10;  -- PostgreSQL
SELECT * FROM orders ORDER BY id DESC LIMIT 20, 10;        -- MySQL

⚠️ OFFSET顺序：PostgreSQL要求先写OFFSET后LIMIT，MySQL相反[citation:2][citation:3]。

2. JSON查询（关键优势）

-- 查询JSON字段
SELECT data->>'name' AS username FROM profiles;  -- PostgreSQL
SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) FROM profiles;  -- MySQL

核心差异：
PostgreSQL提供->（返回JSON）、->>（返回文本）等操作符，MySQL需嵌套函数[citation:6][citation:9]。

五、用户与权限管理

1. 创建用户

-- PostgreSQL
CREATE USER dev_user WITH PASSWORD '密码';
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO dev_user;

-- MySQL
CREATE USER 'dev_user'@'%' IDENTIFIED BY '密码';
GRANT SELECT, INSERT ON 数据库.* TO 'dev_user'@'%';

权限模型差异：
PostgreSQL权限细分到SCHEMA级别，MySQL通常按库授权[citation:4]。

2. 查看活跃连接

-- PostgreSQL
SELECT * FROM pg_stat_activity;  -- 可筛选：WHERE state = 'active'

-- MySQL
SHOW FULL PROCESSLIST;

🔍 运维价值：pg_stat_activity可像普通表一样过滤分析，MySQL仅能查看列表[citation:1][citation:6]。

六、运维管理命令

任务PostgreSQLMySQL
备份数据库pg_dump -Fc 数据库 > backup.dumpmysqldump 数据库 > backup.sql
恢复数据库pg_restore -d 数据库 backup.dumpmysql 数据库 < backup.sql
查看锁阻塞SELECT * FROM pg_blocking_pids(pid);SHOW ENGINE INNODB STATUS;
服务启停pg_ctl start/stop -D 数据目录systemctl start/stop mysql

💡 备份差异：
PostgreSQL支持-Fc（自定义压缩格式），比MySQL的SQL文本备份更快更小[citation:4]。

七、核心差异总结表

功能PostgreSQLMySQL影响场景
事务性DDL✅ 支持（CREATE TABLE可回滚）❌ 不支持线上结构变更安全
JSON支持✅ JSONB（带索引/二进制存储）✅ JSON（无索引/文本存储）高频JSON读写性能
存储引擎❌ 单一（优化器更智能）✅ 多引擎（InnoDB/MyISAM）需MyISAM读密集型场景
正则表达式✅ 强大引擎（支持~*不区分大小写）✅ 基础支持复杂文本处理
复制机制✅ 物理流复制（低延迟）✅ 异步复制（可能丢数据）高可用架构设计

新员工学习建议：

  1. MySQL转PG重点：
    • 掌握SERIAL序列替代AUTO_INCREMENT
    • 用\l替代SHOW DATABASES，\dt替代SHOW TABLES
    • 熟悉ON CONFLICT实现UPSERT [citation:2][citation:7]
  2. 性能关键：
    • 所有查询必须走索引（用EXPLAIN ANALYZE验证）
    • JSON字段优先用JSONB并创建GIN索引
    • 避免SELECT *（PG的MVCC机制易产生宽表开销）[citation:6]
  3. 运维安全：
    • 生产环境必须配置pg_hba.conf限制IP访问
    • 使用pg_basebackup做物理备份 + WAL归档

附：命令速查手册
👉 PostgreSQL官方命令文档
👉 MySQL到PG迁移指南 [citation:7]

本教程重点覆盖了开发高频操作和关键差异点，更多高级功能（如窗口函数、物化视图、分区表）可在掌握基础后专项学习。

更多技术干货欢迎关注微信公众号“风雨同舟的AI笔记”~

【转载须知】：转载请注明原文出处及作者信息

avatar
文章
阅读
粉丝