场景一：建表与基础数据类型

核心差异： 自增主键写法、字符串大小写敏感、注释语法。

sql

-- ========== PostgreSQL 写法 ==========
-- 1. 表名和字段名建议全小写，避免加双引号
-- 2. 自增用 SERIAL 或 IDENTITY (PG 16+)
-- 3. 布尔值直接用 BOOLEAN，存储 TRUE/FALSE

CREATE TABLE employees (
    id          SERIAL PRIMARY KEY,          -- 自增整数
    emp_no      VARCHAR(20) UNIQUE NOT NULL,
    full_name   VARCHAR(100) NOT NULL,
    is_active   BOOLEAN   DEFAULT TRUE,      -- 原生布尔类型
    salary      NUMERIC(10, 2),
    created_at  TIMESTAMP DEFAULT NOW()
);

COMMENT ON TABLE  employees IS '员工信息表';
COMMENT ON COLUMN employees.salary IS '月薪（单位：元）';


-- ========== MySQL 写法 (InnoDB) ==========
-- 1. 默认大小写不敏感，表名、字段名可混用大小写
-- 2. 自增用 AUTO_INCREMENT
-- 3. 布尔值实际是 TINYINT(1)，存 0/1

CREATE TABLE employees (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    emp_no      VARCHAR(20) UNIQUE NOT NULL,
    full_name   VARCHAR(100) NOT NULL,
    is_active   TINYINT(1) DEFAULT 1,        -- 用 0/1 代表 false/true
    salary      DECIMAL(10, 2),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

场景二：增删改查（CRUD）

核心差异： RETURNING 子句（PG神器）、插入自增ID的获取方式、分页写法。

sql

-- ========== PostgreSQL 写法 ==========
-- 1. 插入并立刻返回生成的ID（不需要额外查询）
INSERT INTO employees (emp_no, full_name, salary) 
VALUES ('E1001', 'zhang san', 15000.00) 
RETURNING id;   -- 直接拿到刚插入的id值

-- 2. 更新并返回旧值（审计场景极好用）
UPDATE employees SET salary = 18000 
WHERE emp_no = 'E1001' 
RETURNING salary AS old_salary, full_name;   -- 可返回任意字段

-- 3. 删除并返回删除记录（用于备份或日志）
DELETE FROM employees WHERE id = 1 RETURNING *;

-- 4. 分页查询 (必须用 LIMIT + OFFSET)
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;  -- 第21-30条


-- ========== MySQL 写法 ==========
-- 1. 插入后需要执行 LAST_INSERT_ID() 获取自增ID
INSERT INTO employees (emp_no, full_name, salary) 
VALUES ('E1001', 'zhang san', 15000.00);
SELECT LAST_INSERT_ID();   -- 额外执行一次查询

-- 2. 更新和删除不支持 RETURNING，必须单独 SELECT 查看变更
UPDATE employees SET salary = 18000 WHERE emp_no = 'E1001';
SELECT * FROM employees WHERE emp_no = 'E1001';  -- 再查一次看结果

-- 3. 分页查询 (两种写法都支持)
SELECT * FROM employees ORDER BY id LIMIT 20, 10;    -- 跳过20条取10条
-- 或者标准写法
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;

场景三：高级查询（窗口函数 + 递归CTE）

核心差异： 两者语法几乎一致（因为都遵循SQL标准），但 PG在复杂JOIN和大数据量分析时，优化器表现更稳定。

sql

-- ========== 1. 窗口函数：按部门排名（两者语法完全相同） ==========
-- 给每个员工的工资按高低排序，从高到低编号
SELECT 
    full_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

-- ========== 2. 递归查询：经典的组织架构树 (PG 和 MySQL 8.0+ 写法一致) ==========
-- 假设有 department 表 (id, name, parent_id)
WITH RECURSIVE dept_tree AS (
    -- 初始查询：找到根节点
    SELECT id, name, parent_id, 1 AS level
    FROM departments
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归部分：找到子节点
    SELECT d.id, d.name, d.parent_id, dt.level + 1
    FROM departments d
    JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree ORDER BY level, name;

场景四：事务与并发（MVCC）

核心差异： PG的 读操作绝对不会被写阻塞（即使锁表），而 MySQL（InnoDB）在特定隔离级别（如可重复读）下可能会产生间隙锁（Gap Lock）影响并发插入。

sql

-- ========== PostgreSQL 事务写法（更严格） ==========
BEGIN;  -- 或者 START TRANSACTION;

-- 1. 悲观锁：FOR UPDATE（PG会锁住行，但读操作依然能查到旧数据快照）
SELECT * FROM employees WHERE id = 1 FOR UPDATE;

-- 2. 执行更新
UPDATE employees SET salary = salary + 1000 WHERE id = 1;

-- 3. 提交事务（若异常则 ROLLBACK）
COMMIT;

-- 小技巧：PG中可以用 SAVEPOINT 实现嵌套事务回滚
BEGIN;
SAVEPOINT sp1;
INSERT INTO employees (emp_no, full_name) VALUES ('E999', 'temp');
ROLLBACK TO SAVEPOINT sp1;  -- 只回滚这条插入，事务继续
RELEASE SAVEPOINT sp1;
COMMIT;


-- ========== MySQL 事务写法（默认自动提交，需先关闭） ==========
SET autocommit = 0;  -- 关闭自动提交
START TRANSACTION;

-- MySQL 默认可重复读，此时 FOR UPDATE 可能触发间隙锁，影响范围大于PG
SELECT * FROM employees WHERE id = 1 FOR UPDATE;
UPDATE employees SET salary = salary + 1000 WHERE id = 1;

COMMIT;  -- 或 ROLLBACK
SET autocommit = 1;  -- 恢复自动提交

避坑指南（必看）

易错点	PostgreSQL 表现	MySQL 表现	应对代码策略
字符串引号	只能用单引号 'text'，双引号表示字段名。	单双引号都可以。	强制统一：所有字符串写单引号，字段名不加引号。
布尔值传参	必须传 true 或 false（或 't' / 'f'）。	传 1 或 0（或 true/false，但底层存1/0）。	Java/Python代码里：用常量包一层，如 true 映射 PG 用 TRUE，映射 MySQL 用 1。
大小写问题	employees ≠ EMPLOYEES（除非加了引号）。	不敏感，大小写都认。	统一规范：所有表名、字段名强制用小写+下划线，杜绝引号。
获取刚插入的ID	RETURNING id	LAST_INSERT_ID()	代码层适配：PG直接取返回结果；MySQL执行单独查询。
模糊查询大小写	LIKE 区分大小写；用 ILIKE 不区分。	LIKE 默认不区分。	PG查中文无影响；查英文且要忽略大小写时，用 ILIKE '%ABC%'。

💡 一句总结给开发

• 如果你在写 新项目且追求功能强大、数据精确：PostgreSQL 的 RETURNING、ILIKE、原生JSON支持会让你爽到。

• 如果你的项目是 极简CRUD、高并发读为主、已经和MySQL适配好：继续用 MySQL，但要小心 自增ID事务回滚后跳号、间隙锁导致死锁 这两个坑。