PostgreSQL DML（数据操作语言）深度详解文档

以下是专为 Java 后端开发者（尤其是使用 Spring Boot 的团队）撰写的 PostgreSQL DML（数据操作语言）深度详解文档，全面覆盖定义、作用、核心语句、企业级最佳实践、避坑指南与真实场景示例，全部采用清晰中文注释式结构，可直接作为团队开发规范、代码审查标准与培训教材。

---

🔧 PostgreSQL DML（数据操作语言）深度详解文档

—— 数据的“搬运工”与业务一致性的守护者

适用对象：Java 开发、后端工程师、测试工程师、技术负责人
目标：系统掌握 PostgreSQL DML 的所有核心操作，统一团队增删改标准，杜绝“脏数据”“误删”“性能灾难”，实现安全、高效、可追溯的数据操作。

---

一、DML 是什么？（Definition）

DML（Data Manipulation Language，数据操作语言）是用于操作数据库中具体数据记录的 SQL 子集。
它直接修改、读取或删除数据内容，是业务逻辑最直接的数据库交互层。

✅ 核心作用：

作用	说明
新增数据	插入用户、订单、日志等业务实体
更新数据	修改状态、金额、配置、时间戳等业务状态
删除数据	移除无效、过期或错误记录（必须谨慎！）
实现业务事务	与 TCL（事务控制）配合，确保多步操作原子性

💡 关键认知：
DML 是业务系统最频繁的数据库操作，占 80% 以上的 SQL 执行量。
一条错误的 UPDATE 可能导致百万用户数据错乱，
一条无索引的 DELETE 可能锁死整张表。
DML 不是“写个 SQL 就完事”，而是系统稳定性的生命线。

---

二、DML 包含哪些内容？（Core Components）

PostgreSQL 的 DML 主要包含以下四类语句：

语句	中文名称	作用	企业级重要性
✅ INSERT	插入	向表中添加新记录	高频，需支持批量、冲突处理
✅ UPDATE	更新	修改已有记录的字段值	最易出错，必须带 WHERE，禁止全表更新
✅ DELETE	删除	从表中移除记录	生产环境禁止直接使用！
✅ MERGE	合并（Upsert）	插入或更新（PostgreSQL 15+）	替代“查-改-插”循环，原子性更强

⚠️ 注意：SELECT 属于 DQL，不在 DML 范畴，但常与 DML 搭配使用（如 UPDATE ... WHERE id IN (SELECT ...)）。

---

三、DML 核心语句详解与企业级标准示例（带中文注释）

以下所有示例均为生产环境推荐写法，遵循 PostgreSQL 最佳实践与 Java 团队协作规范。

---

✅ 1. INSERT —— 插入数据（安全、高效、可追溯）

📌 核心原则：

• 禁止单条循环插入（Java 中用 for 循环执行 INSERT）。
• 必须使用批量插入（一次 SQL 插入多行）。
• 推荐使用 ON CONFLICT 实现 Upsert，避免“先查后插”。
• 插入前确保字段类型、约束、默认值匹配。

-- 📌 示例1：插入单条用户记录（标准写法）
INSERT INTO users (
    username, 
    email, 
    password_hash, 
    metadata, 
    status
) VALUES (
    'alice', 
    'alice@company.com', 
    '$2a$10$abc123...',   -- ✅ 密码哈希值（BCrypt）
    '{"theme": "dark", "notifications": {"email": true}}'::JSONB,  -- ✅ 明确转换为 JSONB
    'active'
);

-- 📌 示例2：批量插入用户（性能提升 10x+，Java 中必须使用）
INSERT INTO users (
    username, 
    email, 
    password_hash, 
    metadata, 
    created_at, 
    updated_at, 
    status
) VALUES 
    ('bob', 'bob@company.com', '$2a$10$bcd456...', '{"lang": "zh"}'::JSONB, NOW(), NOW(), 'active'),
    ('charlie', 'charlie@company.com', '$2a$10$cde789...', '{}'::JSONB, NOW(), NOW(), 'active'),
    ('diana', 'diana@company.com', '$2a$10$def012...', '{"theme": "light"}'::JSONB, NOW(), NOW(), 'active');

-- 📌 示例3：使用 ON CONFLICT 实现 Upsert（推荐！替代“查-改-插”）
-- 场景：用户登录时，若不存在则创建，若存在则更新最后登录时间
INSERT INTO users (
    username, 
    email, 
    password_hash, 
    metadata, 
    created_at, 
    updated_at, 
    status
) VALUES (
    'eve', 
    'eve@company.com', 
    '$2a$10$efg345...', 
    '{"last_login": "2025-10-17T10:00:00Z"}'::JSONB,
    NOW(), 
    NOW(), 
    'active'
)
ON CONFLICT (email) DO UPDATE SET
    password_hash = EXCLUDED.password_hash,  -- ✅ 冲突时更新密码（如重置）
    metadata = users.metadata || EXCLUDED.metadata,  -- ✅ 合并 JSONB（旧值 + 新值）
    updated_at = NOW();  -- ✅ 自动更新时间戳

-- 📌 示例4：从其他表插入数据（数据迁移、备份恢复）
INSERT INTO users_archive (
    id, username, email, created_at, status
)
SELECT 
    id, username, email, created_at, status
FROM users 
WHERE status = 'deleted' 
  AND created_at < '2024-01-01';

-- 📌 示例5：插入时使用序列生成自定义ID（如订单号）
INSERT INTO orders (
    order_no, 
    user_id, 
    total_amount, 
    status
) VALUES (
    'ORD-' || nextval('order_seq'),  -- ✅ 生成：ORD-1000001
    1001,
    299.90,
    'pending'
);

-- 📌 示例6：插入 JSONB 字段（推荐使用 JSONB 操作符构造）
INSERT INTO user_preferences (
    user_id, 
    settings
) VALUES (
    1001,
    jsonb_build_object(
        'theme', 'dark',
        'notifications', jsonb_build_object(
            'email', true,
            'push', false,
            'sms', null
        ),
        'language', 'zh-CN'
    )
);
-- ✅ 动态构建 JSONB，避免手动拼接字符串出错

✅ 团队规范建议：

• 所有插入必须显式指定字段名，禁止 INSERT INTO users VALUES (...)。
• 批量插入每批不超过 1000 条，避免 SQL 过长。
• 使用 EXCLUDED 关键字访问冲突行的值，比 SELECT + UPDATE 更安全、高效。
• JSONB 字段使用 jsonb_build_object()、jsonb_agg() 等函数构造，避免字符串拼接。

---

✅ 2. UPDATE —— 更新数据（高危操作，必须规范）

📌 核心原则：

• 必须带 WHERE 条件，禁止无条件更新（UPDATE table SET ...）。
• 更新前必须校验业务状态（如订单状态必须为 pending）。
• 必须更新 updated_at 字段（由触发器或代码保证）。
• 避免更新主键、外键（除非业务明确要求）。
• 使用事务包裹多步更新。

-- 📌 示例1：更新用户状态（标准写法）
UPDATE users 
SET 
    status = 'banned',
    updated_at = NOW()
WHERE email = 'spam@fake.com' 
  AND status = 'active';  -- ✅ 必须校验当前状态，避免重复操作

-- 📌 示例2：批量更新订单状态（按用户ID）
UPDATE orders 
SET 
    status = 'cancelled',
    updated_at = NOW()
WHERE user_id IN (1001, 1002, 1003) 
  AND status = 'pending';  -- ✅ 仅更新待支付订单

-- 📌 示例3：更新 JSONB 字段（追加或修改键值）
-- 场景：用户修改了偏好设置，需合并新值
UPDATE users 
SET 
    metadata = metadata || '{"theme": "light", "fontSize": 16}'::JSONB,
    updated_at = NOW()
WHERE id = 1001;

-- ✅ 说明：`||` 操作符合并两个 JSONB 对象，同名键被覆盖

-- 📌 示例4：更新 JSONB 中的嵌套字段（路径更新）
-- 场景：开启用户推送通知
UPDATE users 
SET 
    metadata = jsonb_set(
        metadata, 
        '{notifications,push}', 
        'true'::JSONB, 
        true  -- ✅ true 表示创建键（若不存在）
    ),
    updated_at = NOW()
WHERE id = 1001;

-- 📌 示例5：更新多个字段 + 条件判断（使用 CASE）
UPDATE products 
SET 
    price = CASE 
        WHEN category = 'premium' THEN price * 1.1
        WHEN category = 'discount' THEN price * 0.8
        ELSE price
    END,
    updated_at = NOW()
WHERE updated_at < '2025-01-01';

-- 📌 示例6：使用子查询更新（关联更新）
-- 场景：根据最新库存更新商品状态
UPDATE products p
SET 
    status = CASE 
        WHEN i.stock > 0 THEN 'in_stock'
        ELSE 'out_of_stock'
    END,
    updated_at = NOW()
FROM inventory i
WHERE p.id = i.product_id;

-- 📌 示例7：更新时使用 RETURNING 返回更新后的数据（Java 中常用）
UPDATE users 
SET 
    status = 'inactive',
    updated_at = NOW()
WHERE id = 1001
RETURNING id, username, status, updated_at;  -- ✅ 直接返回更新后的记录，避免二次查询

✅ 团队规范建议：

• 所有 UPDATE 必须包含 WHERE，且条件字段必须有索引。
• 所有更新操作必须更新 updated_at，由触发器自动维护（推荐）。
• 禁止更新 id、uuid、created_at 等核心标识字段。
• 所有更新必须在 @Transactional 事务中执行。
• 使用 RETURNING 获取更新结果，减少一次 SELECT 查询。

---

✅ 3. DELETE —— 删除数据（生产环境的“雷区”）

📌 核心原则：

❗ 生产环境禁止物理删除（DELETE）！一律使用软删除！

风险	说明
数据不可恢复	误删用户、订单，无法回滚
破坏外键关联	删除用户导致订单丢失，报表出错
违反审计合规	金融、医疗系统必须保留历史
性能灾难	大表 DELETE 会锁表、阻塞写入

✅ 推荐做法：软删除（Soft Delete）

-- 📌 示例1：软删除（推荐！）
UPDATE users 
SET 
    status = 'deleted',
    deleted_at = NOW(),  -- ✅ 新增字段，记录删除时间
    updated_at = NOW()
WHERE id = 1001;

-- 📌 示例2：批量软删除（按时间）
UPDATE users 
SET 
    status = 'deleted',
    deleted_at = NOW(),
    updated_at = NOW()
WHERE created_at < '2024-01-01' 
  AND status = 'inactive';

-- 📌 示例3：查询“未删除”的数据（所有查询都必须过滤）
SELECT * FROM users 
WHERE status != 'deleted' 
  AND status IS NOT NULL;

-- 📌 示例4：创建视图，自动过滤删除记录（推荐）
CREATE VIEW active_users AS
SELECT id, username, email, created_at, metadata
FROM users 
WHERE status != 'deleted';

-- ✅ Java 服务只查询 active_users，无需写 WHERE

⚠️ 何时允许物理删除？（极少数场景）

• 日志表：超过 2 年的日志，定期归档后物理删除。
• 测试数据：测试环境，可全量清空。
• 临时表：CREATE TEMP TABLE 创建的表，会自动删除。

-- 📌 示例5：物理删除（仅限日志归档）
DELETE FROM system_logs 
WHERE created_at < '2023-01-01';

-- ✅ 建议配合分区表 + 定时任务执行，避免锁表

✅ 团队规范建议：

• 所有表必须包含 status 字段和 deleted_at 字段。
• 所有查询（SELECT）必须自动过滤 status != 'deleted'。
• 禁止在任何生产环境执行 DELETE FROM table。
• 所有删除操作必须记录操作人、时间、原因（日志审计）。
• 使用 pg_dump + pg_restore 备份前，必须确认无物理删除需求。

---

✅ 4. MERGE —— 合并（Upsert）—— PostgreSQL 15+ 的终极利器

背景：在 PostgreSQL 15 之前，Upsert 用 ON CONFLICT 实现；15+ 引入标准 MERGE，语义更清晰。

📌 核心优势：

• 原子性：一次操作完成“查-改-插”，无竞态条件。
• 语法清晰：明确区分 WHEN MATCHED 和 WHEN NOT MATCHED。
• 支持复杂逻辑：可对匹配/不匹配分别执行不同操作。

-- 📌 示例：用户登录时，若存在则更新最后登录时间，若不存在则创建
MERGE INTO users AS target
USING (
    SELECT 
        'frank' AS username,
        'frank@company.com' AS email,
        '$2a$10$fgh567...' AS password_hash,
        '{"last_login": "2025-10-17T12:00:00Z"}'::JSONB AS metadata
) AS source
ON target.email = source.email
WHEN MATCHED THEN
    UPDATE SET
        password_hash = source.password_hash,
        metadata = target.metadata || source.metadata,
        updated_at = NOW()
WHEN NOT MATCHED THEN
    INSERT (username, email, password_hash, metadata, created_at, updated_at, status)
    VALUES (
        source.username,
        source.email,
        source.password_hash,
        source.metadata,
        NOW(),
        NOW(),
        'active'
    );

-- ✅ 说明：
-- 1. `USING` 子句定义要合并的源数据（可来自表、子查询、临时表）
-- 2. `ON` 定义匹配条件（必须是唯一约束或主键）
-- 3. `WHEN MATCHED`：匹配时执行 UPDATE
-- 4. `WHEN NOT MATCHED`：不匹配时执行 INSERT

✅ 团队规范建议：

• PostgreSQL 15+ 项目优先使用 MERGE 替代 ON CONFLICT。
• 适用于：用户登录、配置同步、缓存写入、商品库存更新等场景。
• MERGE 语句必须有唯一约束或主键作为 ON 条件。
• 旧版本（<15）继续使用 ON CONFLICT，二者功能等价。

---

四、DML 企业级最佳实践与避坑指南（Java 开发者必看）

项目	推荐做法	禁止行为
✅ 插入	使用批量 INSERT ... VALUES (...), (...)	循环单条 INSERT
✅ 更新	必须带 WHERE，更新 updated_at	无 WHERE 的全表更新
✅ 删除	全部使用 UPDATE status = 'deleted'	生产环境执行 DELETE
✅ JSONB 更新	使用 jsonb_set()、`
✅ 事务	所有 DML 必须在 @Transactional 中	单条 DML 无事务保护
✅ 性能	确保 WHERE 条件字段有索引	无索引的 UPDATE/DELETE
✅ 安全性	使用参数化查询（PreparedStatement）	拼接 SQL（+ 字符串）
✅ 可追溯	记录操作人、时间、IP（审计日志）	无日志、无追踪
✅ 测试	使用 TestContainers + Flyway 模拟真实环境	在本地 H2/内存数据库测试 DML

---

五、实战场景：订单系统 DML 最佳实践（完整流程）

场景：用户下单 → 扣库存 → 创建订单 → 发送通知

-- ✅ 事务内执行（Java 中用 @Transactional）
BEGIN;

-- 1. 检查库存（可选，推荐用数据库约束）
SELECT stock FROM products WHERE id = 101 FOR UPDATE;  -- ✅ 行级锁，防止超卖

-- 2. 扣库存（UPDATE）
UPDATE products 
SET stock = stock - 1 
WHERE id = 101 AND stock > 0;  -- ✅ 必须校验库存 > 0

-- 3. 插入订单（INSERT）
INSERT INTO orders (user_id, product_id, total_amount, status)
VALUES (1001, 101, 299.90, 'pending')
RETURNING id;  -- ✅ 返回订单ID

-- 4. 插入订单项（INSERT）
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (12345, 101, 1, 299.90);

-- 5. 更新用户积分（UPDATE）
UPDATE users 
SET points = points + 10 
WHERE id = 1001;

COMMIT;  -- ✅ 所有操作要么全成功，要么全失败

✅ Java 代码对应：

@Transactional
public void createOrder(Long userId, Long productId) {
    // 1. 扣库存（数据库层面保证原子性）
    productRepository.decreaseStock(productId, 1);
    
    // 2. 创建订单
    Order order = orderRepository.save(new Order(userId, productId, 299.90));
    
    // 3. 创建订单项
    orderItemRepository.save(new OrderItem(order.getId(), productId, 1, 299.90));
    
    // 4. 增加积分
    userRepository.addPoints(userId, 10);
}

💡 关键点：
所有操作都在一个事务中，数据库保证一致性，Java 无需写补偿逻辑。

---

六、附录：DML 审查清单（团队 Code Review 必查项）

检查项	是否通过	说明
✅ INSERT 是否显式指定字段？	☐	避免因表结构变更导致崩溃
✅ 是否使用批量插入？	☐	单条插入性能差，易阻塞
✅ UPDATE 是否带 WHERE？	☐	无 WHERE = 灾难
✅ UPDATE 是否更新 updated_at？	☐	必须有，否则无法追踪
✅ 是否使用 RETURNING？	☐	减少一次查询，提升效率
✅ 是否使用 ON CONFLICT 或 MERGE？	☐	避免“查-改-插”竞态
✅ 是否使用 @Transactional？	☐	多表操作必须事务
✅ 是否使用参数化查询？	☐	防止 SQL 注入
✅ 是否有 status = 'deleted' 过滤？	☐	所有查询必须过滤软删除
✅ 是否有索引支持 WHERE 条件？	☐	无索引 = 慢查询 = 系统雪崩

---

七、总结：DML 的三条铁律

铁律	说明
🔒 1. 所有更新必须带 WHERE，所有删除必须软删除	数据安全第一
🚀 2. 所有插入必须批量，所有查询必须索引	性能是底线
🔄 3. 所有 DML 必须在事务中，必须由 Java 层或触发器维护时间戳	一致性是生命

✅ 终极建议：
让数据库做它该做的事：保证数据准确、一致、安全。
让 Java 做它该做的事：协调业务流程、调用服务、记录日志。

---

📌 下一步行动建议：

1. 将本文档作为团队《DML 编码规范》核心章节，加入代码审查清单。
2. 在 GitLab CI 中集成 SQL 检查工具（如 sqlfluff），自动检测无 WHERE 的 UPDATE/DELETE。
3. 组织一次“DML 避坑实战”培训，用真实事故案例（如误删用户）震撼团队。
4. 为所有表添加 status 和 deleted_at 字段，逐步替换物理删除逻辑。