挖掘机驾驶宝典

Appearance

SQL 语法入门 ​

PostgreSQL 与 MySQL 的基础查询结构相近，主要差异集中在标识符、自增列、类型、布尔值、字符串操作、Upsert、关联更新和结果返回。以下示例以 PostgreSQL 原生语法为准。

标识符与 Schema ​

未加引号的标识符会被折叠为小写，双引号保留原始大小写；字符串使用单引号。

sql
CREATE TABLE app_user (...);       -- 实际对象名 app_user
CREATE TABLE "AppUser" (...);     -- 对象名严格为 AppUser

SELECT * FROM app_user;
SELECT * FROM "AppUser";

MySQL 常用反引号引用对象，PostgreSQL 使用双引号：

目的MySQLPostgreSQL
引用对象`user`"user"
字符串'text''text'
限定对象database.tableschema.table
默认命名空间当前 Databasesearch_path，通常先使用 public

不建议创建混合大小写的带引号对象，否则后续 SQL 必须始终保持相同引号和大小写。

建表与常用类型 ​

sql
CREATE TABLE app_user (
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    username    VARCHAR(100) NOT NULL,
    enabled     BOOLEAN NOT NULL DEFAULT TRUE,
    balance     NUMERIC(18, 2) NOT NULL DEFAULT 0,
    profile     JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uk_app_user_username UNIQUE (username),
    CONSTRAINT ck_app_user_balance CHECK (balance >= 0)
);
MySQL 常用类型PostgreSQL 对应选择差异
BIGINT AUTO_INCREMENTBIGINT GENERATED ... AS IDENTITYIdentity 是标准写法；BIGSERIAL 是序列简写，不是真正类型
TINYINT(1)BOOLEANPostgreSQL 布尔值使用 TRUE、FALSE，不能普遍当作 0/1
DATETIMETIMESTAMP都不携带时区语义
TIMESTAMPTIMESTAMPTZ 或 TIMESTAMPTIMESTAMPTZ 按绝对时间保存并按会话时区显示
JSONJSONB 或 JSONJSONB 支持更丰富的检索与 GIN 索引，格式和键顺序不按原文本保留
BLOBBYTEALarge Object 是另一套独立机制
DOUBLEDOUBLE PRECISION浮点值不适合精确金额
ENUMEnum Type 或约束表PostgreSQL Enum 是 Schema 对象，演进成本需评估

GENERATED ALWAYS AS IDENTITY 默认拒绝显式写入生成值；BY DEFAULT 允许显式覆盖，更方便导入历史主键。二者都不保证序列值连续，事务回滚后产生空洞是正常现象。

插入与返回结果 ​

sql
INSERT INTO app_user (username, enabled, balance, profile)
VALUES ('alice', TRUE, 100.00, '{"level":"vip"}'::jsonb)
RETURNING id, created_at;

PostgreSQL 的 INSERT、UPDATE、DELETE 和 MERGE 可以使用 RETURNING 返回变更后的列，通常不需要再查询 LAST_INSERT_ID()。

批量插入与 MySQL 相似：

sql
INSERT INTO app_user (username, enabled)
VALUES
    ('alice', TRUE),
    ('bob', FALSE)
RETURNING id, username;

Upsert ​

MySQL 使用 ON DUPLICATE KEY UPDATE；PostgreSQL 使用 ON CONFLICT，并明确指定冲突约束或索引列。

sql
INSERT INTO app_user (username, enabled)
VALUES ('alice', TRUE)
ON CONFLICT (username)
DO UPDATE SET
    enabled = EXCLUDED.enabled,
    updated_at = CURRENT_TIMESTAMP
RETURNING id, username, enabled;

EXCLUDED 表示原本尝试插入的行。也可以使用 DO NOTHING 忽略冲突。冲突目标必须能够匹配唯一索引或唯一约束，不能把任意查询条件当作冲突条件。

查询与分页 ​

sql
SELECT id, username, created_at
FROM app_user
WHERE enabled IS TRUE
  AND username ILIKE 'a%'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;
需求MySQL 常见写法PostgreSQL
分页LIMIT 40, 20LIMIT 20 OFFSET 40
不区分大小写匹配依赖 CollationILIKE，或显式选择合适 Collation/索引
NULL 判断IS NULLIS NULL
NULL 安全相等<=>IS NOT DISTINCT FROM
字符串拼接CONCAT(a, b)`a
当前时间NOW()CURRENT_TIMESTAMP 或 now()

分页没有稳定 ORDER BY 时结果顺序不确定。深分页使用大 OFFSET 会扫描并丢弃前面的结果，持续翻页更适合使用由排序键构成的 Keyset Pagination。

更新与删除关联数据 ​

MySQL 常见 UPDATE ... JOIN，PostgreSQL 使用 UPDATE ... FROM：

sql
UPDATE app_user AS u
SET enabled = FALSE,
    updated_at = CURRENT_TIMESTAMP
FROM user_blacklist AS b
WHERE b.username = u.username
  AND b.active IS TRUE;

关联删除使用 USING：

sql
DELETE FROM app_user AS u
USING deleted_account AS d
WHERE d.user_id = u.id
RETURNING u.id;

FROM 或 USING 中同一目标行若匹配多行，更新来源可能不具有业务确定性。应通过唯一约束或先聚合、去重保证每个目标行只有一个来源。

类型转换、日期与 JSONB ​

sql
SELECT
    '42'::BIGINT,
    CAST('2026-08-20' AS DATE),
    CURRENT_DATE,
    CURRENT_TIMESTAMP,
    created_at + INTERVAL '7 days'
FROM app_user;
sql
SELECT
    profile -> 'level' AS json_value,
    profile ->> 'level' AS text_value
FROM app_user
WHERE profile @> '{"level":"vip"}'::jsonb;

PostgreSQL 的隐式类型转换通常比 MySQL 严格。例如整数列与任意文本直接比较可能报错，而不是把无法转换的文本当成数字零。类型边界应在参数绑定或显式 CAST 中处理。

事务与并发更新 ​

sql
BEGIN;

SELECT balance
FROM app_user
WHERE id = 1
FOR UPDATE;

UPDATE app_user
SET balance = balance - 10
WHERE id = 1;

COMMIT;

PostgreSQL 客户端通常也处于自动提交模式，每条独立语句自动形成事务。显式事务发生错误后，事务会进入失败状态，必须 ROLLBACK 或回滚到保存点后才能继续执行；这一点与 MySQL 常见使用体验差异明显。

DDL 绝大多数可以放入事务并回滚，但并非所有运维命令都允许在事务块中执行。

MySQL 迁移检查表 ​

MySQL 习惯PostgreSQL 调整
反引号引用表和列改用双引号，最好使用无需引号的小写名称
AUTO_INCREMENT使用 Identity Column
TINYINT(1) 表示布尔使用 BOOLEAN 和 TRUE/FALSE
ON DUPLICATE KEY UPDATE使用 ON CONFLICT
UPDATE ... JOIN使用 UPDATE ... FROM
DELETE ... JOIN使用 DELETE ... USING
LIMIT offset, size使用 LIMIT size OFFSET offset
宽松的字符串转数字显式转换并处理非法输入
IFNULL使用标准 COALESCE
GROUP_CONCAT使用 string_agg
DATE_FORMAT使用 to_char
LAST_INSERT_ID()使用 DML RETURNING

参考资料 ​