总体对比（速览）

• 相似处：都支持 SQL 标准的大部分 DDL/DML（CREATE TABLE/INDEX、SELECT、JOIN、GROUP BY、事务等）。很多基本语法 SELECT ... FROM ... WHERE ...、JOIN、GROUP BY 基本一致。

• 主要不同点（高频影响）：

  • 标识符引用：MySQL 用反引号 `col`；Postgres 用双引号 "col"（通常不需要引用，除非大小写/特殊字符）。

  • 自增主键：MySQL AUTO_INCREMENT；Postgres 用 SERIAL / BIGSERIAL（老）或标准 GENERATED {ALWAYS|BY DEFAULT} AS IDENTITY（推荐）。

  • 字段类型：MySQL 有 TINYINT、ENUM 等；Postgres 有更强的类型系统（SERIAL、TEXT、JSONB、ARRAY、UUID、TIMESTAMP WITH(OUT) TIME ZONE 等），没有 unsigned。

  • 字符串连接：MySQL CONCAT(a,b)；Postgres a || b 或也可用 concat()。

  • NULL/函数：MySQL IFNULL(x,y) -> Postgres COALESCE(x,y)。MySQL IF(expr, true, false) -> Postgres CASE WHEN ... THEN ... ELSE ... END 或 filter/bool表达式。

  • upsert：MySQL INSERT ... ON DUPLICATE KEY UPDATE；Postgres INSERT ... ON CONFLICT (...) DO UPDATE SET ...（更强）。

  • 分区：Postgres 自 v10+ 支持 declarative partitioning（RANGE/LIST/HASH）；语法和实现与 MySQL（InnoDB 分区）不同，细节不一样。

  • 存储过程/函数：MySQL 用 DELIMITER 与 CREATE PROCEDURE / CREATE FUNCTION；Postgres 用 CREATE FUNCTION（或 CREATE PROCEDURE 自 v11 起）并用 LANGUAGE plpgsql，语法不同（不需要 DELIMITER）。

  • 定时任务：MySQL 有 EVENT（内置事件调度器）；Postgres 没有内置 scheduler，需要 cron、pg_cron、pgAgent 等扩展或外部 cron 调用 psql。

  • 索引类型：Postgres 支持 btree、hash、GIN、GiST、BRIN、SP-GiST 等，能针对 jsonb、全文、trigram 做专用索引。

  • 事务与锁：两者都支持事务；但 Postgres 的 MVCC、行级可见性、VACUUM/autovacuum 等机制和细节更重要（需要维护统计信息）。

  • 配置/管理：Postgres 倾向于通过配置文件、扩展（extensions）与维护任务（VACUUM、ANALYZE）来管理性能。

命名 & 类型 & 语法差异常见对照表

MySQL                      | PostgreSQL (等价/推荐)
--------------------------|-------------------------------
`col` (backticks)         | "col" (double quotes) 或 col（不推荐用引号）
AUTO_INCREMENT            | SERIAL / BIGSERIAL 或 IDENTITY
TINYINT(1) (bool usage)   | boolean
DATETIME                  | timestamp without time zone
TIMESTAMP                 | timestamp with (or without) time zone
ENUM                      | CREATE TYPE ... AS ENUM OR CHECK constraint
IFNULL(a,b)               | COALESCE(a,b)
CONCAT(a,b)               | a || b  或 concat(a,b)
LIMIT offset, count       | LIMIT count OFFSET offset
INSERT ... ON DUPLICATE   | INSERT ... ON CONFLICT ... DO UPDATE
SHOW TABLES               | \dt  (psql meta-command) / SELECT from information_schema
ENGINE=InnoDB             | PostgreSQL 不用 ENGINE
UNSIGNED int              | no unsigned; use bigger signed type or domain check
JSON                      | json / jsonb (建议 jsonb)

1) 表和数据类型：从 MySQL 转到 PostgreSQL 的典型写法

MySQL 示例：

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  role ENUM('user','admin') DEFAULT 'user',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARACTER SET utf8mb4;

Postgres 等价（推荐）：

-- 推荐使用 IDENTITY（符合 SQL 标准）
CREATE TYPE user_role AS ENUM ('user','admin');

CREATE TABLE users (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  username varchar(50) NOT NULL,
  role user_role DEFAULT 'user',
  created_at timestamp with time zone DEFAULT now()
);

或者用 CHECK 替代 ENUM：

CREATE TABLE users2 (
  id serial PRIMARY KEY,
  username varchar(50) NOT NULL,
  role varchar(10) NOT NULL DEFAULT 'user' CHECK (role IN ('user','admin')),
  created_at timestamptz DEFAULT now()
);

注意：

• Postgres 推荐 timestamptz（timestamp with time zone）用于表示绝对时间，除非你确有理由用无时区 timestamp.

• SERIAL 是便利语法，会创建序列；IDENTITY 更符合 SQL 标准且行为更明确。

2) 基础 SELECT / JOIN / LIMIT 差异与范例

MySQL:

SELECT u.id, u.username, p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.username LIKE '%john%'
ORDER BY u.id DESC
LIMIT 10 OFFSET 20;

Postgres 基本相同（几乎不变）：

SELECT u.id, u.username, p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.username ILIKE '%john%'  -- ILIKE 支持大小写不敏感匹配（MySQL 用 COLLATE 或 LOWER）
ORDER BY u.id DESC
LIMIT 10 OFFSET 20;

注意：

• Postgres 支持 ILIKE（case-insensitive LIKE），MySQL 可用 COLLATE 或 LOWER.

• 字符串连接多用 ||：first_name || ' ' || last_name。

3) INSERT ... ON CONFLICT（Upsert） vs MySQL 的 ON DUPLICATE KEY

MySQL:

INSERT INTO users (id, username) VALUES (1, 'alice')
ON DUPLICATE KEY UPDATE username=VALUES(username);

Postgres:

INSERT INTO users (id, username)
VALUES (1, 'alice')
ON CONFLICT (id) DO UPDATE
SET username = EXCLUDED.username;

EXCLUDED 表示插入时遇到冲突的那行（等同 MySQL 的 VALUES()）。

4) 存储过程 / 函数（MySQL 的 DELIMITER -> Postgres 的 plpgsql）

MySQL 存储过程通常：

DELIMITER //
CREATE PROCEDURE add_user(p_name VARCHAR(100))
BEGIN
  INSERT INTO users (username) VALUES (p_name);
END //
DELIMITER ;

Postgres（函数）：

CREATE OR REPLACE FUNCTION add_user(p_name text)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO users (username) VALUES (p_name);
END;
$$;

如果需要事务控制（BEGIN/COMMIT）内部，Postgres FUNCTION 不能包含事务控制语句（COMMIT/ROLLBACK），但 PROCEDURE（Postgres v11+）可以：

CREATE PROCEDURE add_user_proc(p_name text)
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO users (username) VALUES (p_name);
  -- 不能在 function 中 COMMIT，但在 procedure 中可
END;
$$;

常见模式：函数返回 SETOF 或复合类型，用于复杂查询、返回表格。

示例：函数带返回值

CREATE OR REPLACE FUNCTION get_user_posts(uid bigint)
RETURNS TABLE(post_id bigint, title text, created timestamptz)
LANGUAGE sql
AS $$
  SELECT p.id, p.title, p.created_at
  FROM posts p
  WHERE p.user_id = uid;
$$;

5) 触发器（Triggers）

MySQL：

CREATE TRIGGER before_insert_posts
BEFORE INSERT ON posts
FOR EACH ROW
SET NEW.created_at = IFNULL(NEW.created_at, NOW());

Postgres（触发器函数 + CREATE TRIGGER）：

-- 触发器函数
CREATE OR REPLACE FUNCTION trg_posts_before_insert()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.created_at IS NULL THEN
    NEW.created_at := now();
  END IF;
  RETURN NEW;
END;
$$;

-- 绑定触发器
CREATE TRIGGER posts_before_insert
BEFORE INSERT ON posts
FOR EACH ROW
EXECUTE FUNCTION trg_posts_before_insert();

注意：

• Postgres 的触发器函数必须返回 NEW（对于 BEFORE INSERT/UPDATE）或 NULL（删除行），或返回 OLD 等。

• Postgres 支持 FOR EACH ROW 与 FOR EACH STATEMENT。

6) 定时任务（Scheduler）

MySQL 有 EVENT。Postgres 没有内建 scheduler（到目前为止），常见做法：

• 使用操作系统的 cron 调用 psql -c "SELECT my_task();".

• 使用扩展 pg_cron（需要安装扩展并在 postgresql.conf 加载）：

  -- 安装（由管理员在数据库中创建扩展）
  CREATE EXTENSION pg_cron;
  -- 添加一个每天午夜运行的任务
  SELECT cron.schedule('daily_agg', '0 0 * * *', $$SELECT daily_aggregate();$$);
  

• 使用 pgAgent（pgAdmin 附带的任务调度器）。

• 使用外部任务队列（如 rabbitmq + worker）或应用层 Cron。

给出 cron 外部示例（Linux crontab）：

# /etc/cron.d/myjob
0 2 * * * postgres psql -d mydb -c "SELECT daily_aggregate();"

7) 事务与隔离级别

Postgres 支持标准事务与隔离级别（READ COMMITTED 默认、REPEATABLE READ、SERIALIZABLE）：

-- 默认事务
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 指定隔离级别（在 BEGIN 时指定）
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- 或
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

保存点（Savepoint）：

BEGIN;
SAVEPOINT sp1;
-- 做一些操作
ROLLBACK TO SAVEPOINT sp1;  -- 回到保存点但不结束事务
COMMIT;

注意 Postgres 的 MVCC：事务不会阻塞读（默认），但写冲突和冻结（VACUUM）需要关注。

8) 索引优化（常见技巧与示例）

Postgres 支持多种索引类型与高级技巧：

• 常规 B-Tree（默认）：

  CREATE INDEX idx_users_username ON users (username);
  

• 部分索引（partial index）：对经常查询的子集加索引，节省空间

  CREATE INDEX idx_active_users ON users (last_login) WHERE active = true;
  

• 表达式索引（expression index）：对函数结果建索引

  CREATE INDEX idx_lower_username ON users (lower(username));
  

• 覆盖索引（INCLUDE）——类似 MySQL 的 covering index：

  CREATE INDEX idx_posts_user_title ON posts (user_id) INCLUDE (title, created_at);
  

• GIN（用于 jsonb/数组/全文）：

  -- jsonb 路径索引
  CREATE INDEX idx_posts_data_gin ON posts USING gin (data jsonb_path_ops);
  -- 或默认 gin (data)
  CREATE INDEX idx_posts_jsonb ON posts USING gin (data);
  

• Trigram 索引（pg_trgm extension）用于模糊搜索：

  CREATE EXTENSION IF NOT EXISTS pg_trgm;
  CREATE INDEX idx_username_trgm ON users USING gin (username gin_trgm_ops);
  

• BRIN（大型顺序表，廉价）：

  CREATE INDEX idx_logs_time_brin ON logs USING brin (created_at);
  

性能诊断：

• 使用 EXPLAIN ANALYZE <query> 查看实际执行计划和时间。

• 使用 pg_stat_statements 扩展追踪慢查询。

• 记得 ANALYZE（或 VACUUM ANALYZE）以更新统计信息，让优化器正确选择索引。

清理：

• VACUUM 和 AUTOVACUUM 在 Postgres 中必不可少，尤其有大量 UPDATE/DELETE 时要注意表 bloat。

9) 分区表（Declarative Partitioning 示例）

Postgres 现代分区（推荐）：

-- 按 created_at 做 RANGE 分区
CREATE TABLE events (
  id bigserial PRIMARY KEY,
  created_at timestamptz NOT NULL,
  type text,
  payload jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE events_2025 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

List 分区：

CREATE TABLE orders (
  id bigserial,
  region text,
  ...
) PARTITION BY LIST (region);

CREATE TABLE orders_asia PARTITION OF orders FOR VALUES IN ('CN','JP','MY');

注意：

• 分区键应在查询的 WHERE 子句中被使用，这样 planner 能做分区裁剪（pruning）。

• 可以为分区单独创建索引（自 Postgres 11+ 支持分区继承索引等）。

• Postgres 支持 attach partition 用于后期添加历史数据分区。

10) EXPLAIN/性能工具示例

EXPLAIN ANALYZE
SELECT u.id, count(p.*)
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE u.created_at > now() - interval '30 days'
GROUP BY u.id;

看输出，观察是否走索引、是否有排序/HashAggregate、是否有重大 I/O。

建议启用并查询统计扩展：

CREATE EXTENSION pg_stat_statements;
-- 在postgresql.conf中启用 shared_preload_libraries = 'pg_stat_statements'
SELECT query, calls, total_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

11) 事务隔离下的常见坑与迁移注意点

• MySQL 的默认隔离级别在 InnoDB 通常是 REPEATABLE READ（但行为与 Postgres 不完全相同，MySQL 的 gap locks 有不同效果）。Postgres 默认是 READ COMMITTED。

• MySQL 的 SELECT ... FOR UPDATE 锁行为与 Postgres 的行级锁类似，但细节要注意（Postgres 不会自动锁定扫描范围之外的 gap）。

• 无符号（unsigned）：MySQL 有 unsigned，但 Postgres 没有，迁移时需检查上限，可能要用 bigint 或约束。

• 时间类型：MySQL DATETIME vs Postgres timestamp with time zone — 需要在迁移时核对时区逻辑。

12) 示例：综合案例 — 从 MySQL 查询改写到 Postgres + 优化

MySQL 查询（示例）：

SELECT u.id, u.username, COUNT(p.id) AS cnt
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE u.status = 'active' AND p.created_at > '2025-01-01'
GROUP BY u.id
HAVING cnt > 10
ORDER BY cnt DESC
LIMIT 50;

Postgres 等价并优化：

-- 建议索引
CREATE INDEX idx_posts_user_created ON posts (user_id, created_at);
CREATE INDEX idx_users_status ON users (status);

-- 查询
EXPLAIN ANALYZE
SELECT u.id, u.username, COUNT(p.id) AS cnt
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE u.status = 'active' AND p.created_at > DATE '2025-01-01'
GROUP BY u.id, u.username
HAVING COUNT(p.id) > 10
ORDER BY cnt DESC
LIMIT 50;

优化要点：

• 对 posts 建复合索引 (user_id, created_at) 有助于按 user_id 筛选并利用 created_at 范围。

• EXPLAIN ANALYZE 看是否走索引；可能需要调整统计或重建索引。

13) 管理与维护补充（重要但容易忽视）

• VACUUM / ANALYZE：Postgres 需要清理死行并统计表格统计信息。autovacuum 通常开启，但需监控。

• pg_stat_activity 可用于查看当前连接与锁。

• 长事务会阻碍 VACUUM；确保不要长时间持有 open transaction。

• 备份/恢复：Postgres 常用 pg_dump（逻辑）和 pg_basebackup（物理）或工具 pgBackRest、Barman。

• 字符集：Postgres 数据库在创建时指定 ENCODING（如 UTF8）和 LC_COLLATE / LC_CTYPE（排序/大小写规则）。

14) 常见迁移片段（MySQL -> Postgres）

• 把 AUTO_INCREMENT -> GENERATED ... AS IDENTITY 或 SERIAL。

• 把 ENUM -> CREATE TYPE ... AS ENUM（或者 CHECK）。

• 把 TINYINT(1) 变成 boolean。

• 把 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 移除，确保数据库 UTF8。

• ON DUPLICATE KEY UPDATE -> ON CONFLICT DO UPDATE.

• 转义标识符：从 `name` 改成 "name" 或直接 name（小写）。

15) 一些实用的小技巧和常见命令（psql 客户端）

• 登录：psql -h host -U user -d dbname

• 列表表：\dt

• 查看表结构：\d tablename

• 执行 SQL 文件：psql -d dbname -f script.sql

• 退出：\q

16) 快速参考：常用函数对照

MySQL         | Postgres
--------------|-------------------------
NOW()         | now()
CURDATE()     | CURRENT_DATE
IFNULL(a,b)   | COALESCE(a,b)
SUBSTRING(a,1,3) | SUBSTRING(a FROM 1 FOR 3)
CONCAT(a,b)   | a || b  OR concat(a,b)
UNIX_TIMESTAMP() | EXTRACT(EPOCH FROM now())
FROM_UNIXTIME()  | to_timestamp(epoch)

示例合集（把核心都放一起，便于复制执行）

-- 1. create table (identity, jsonb, enum)
CREATE TYPE user_role AS ENUM ('user','admin');

CREATE TABLE users (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  username text NOT NULL UNIQUE,
  profile jsonb,
  role user_role DEFAULT 'user',
  created_at timestamptz DEFAULT now()
);

-- 2. function that upserts
CREATE OR REPLACE FUNCTION upsert_user(uname text, role_in user_role)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO users (username, role) VALUES (uname, role_in)
  ON CONFLICT (username) DO UPDATE
  SET role = EXCLUDED.role;
END;
$$;

-- 3. trigger example
CREATE TABLE posts (
  id bigserial PRIMARY KEY,
  user_id bigint REFERENCES users(id),
  title text,
  body text,
  created_at timestamptz DEFAULT now()
);

CREATE OR REPLACE FUNCTION trg_posts_before_insert()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.created_at IS NULL THEN
    NEW.created_at := now();
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER posts_before_insert
BEFORE INSERT ON posts
FOR EACH ROW
EXECUTE FUNCTION trg_posts_before_insert();

-- 4. index examples
CREATE INDEX idx_posts_user_created ON posts (user_id, created_at);
CREATE INDEX idx_users_lower_username ON users (lower(username));

-- 5. upsert usage
INSERT INTO users (username, role) VALUES ('bob','user')
ON CONFLICT (username) DO UPDATE SET role = EXCLUDED.role;

-- 6. partition example
CREATE TABLE events (
  id bigserial PRIMARY KEY,
  created_at timestamptz NOT NULL,
  info jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2025 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

总结与建议

• 如果你来自 MySQL：最重要的是适应 类型差异（无 unsigned）、标识符引用、SERIAL/IDENTITY、ON CONFLICT、trigger/func 语法、以及 PostgreSQL 的维护（VACUUM/ANALYZE/autovacuum）。

• 性能方面学会用 EXPLAIN ANALYZE、pg_stat_statements、并熟悉多种索引（GIN/BRIN/GIN_TRGM/GiST）——Postgres 在复杂查询/JSON/全文搜索上比 MySQL 更灵活，但也更依赖正确索引和维护。

• 调度任务：Postgres 没有内置 scheduler，推荐 pg_cron 或外部 cron/pgAgent。

• 开发流程：多使用 psql 的元命令（如 \d、\dt）、并为大表规划分区。