垦荒学园
  • 中文

      • PostgreSQL 清空表 TRUNCATE TABLE

      概述

      TRUNCATE TABLE语句用于快速删除表中的所有数据，但保留表结构。相比DELETE，TRUNCATE更快速高效，特别适用于清空大型表。

      TRUNCATE语句

      基本语法

      TRUNCATE TABLE table_name;

      简单示例

      -- 清空表
TRUNCATE TABLE users;

-- 可以省略TABLE关键字
TRUNCATE users;

      完整语法

      TRUNCATE [TABLE] [ONLY] table_name [, ...]
    [RESTART IDENTITY | CONTINUE IDENTITY]
    [CASCADE | RESTRICT];

      选项说明

      重置序列

      -- 重置自增序列到初始值
TRUNCATE TABLE users RESTART IDENTITY;

-- 保持序列当前值（默认）
TRUNCATE TABLE users CONTINUE IDENTITY;

      级联清空

      -- 同时清空有外键引用的表
TRUNCATE TABLE departments CASCADE;

-- 如果有外键引用则拒绝清空（默认）
TRUNCATE TABLE departments RESTRICT;

      清空多个表

      -- 一次清空多个表
TRUNCATE TABLE orders, order_items, users;

-- 带选项清空多个表
TRUNCATE TABLE orders, order_items RESTART IDENTITY CASCADE;

      TRUNCATE vs DELETE

      性能对比

      特性TRUNCATEDELETE
      速度非常快较慢（逐行删除）
      日志最小日志记录每行删除
      事务可回滚可回滚
      触发器默认不触发触发行级触发器
      WHERE条件不支持支持
      返回行数不返回返回删除行数
      外键检查需要CASCADE自动检查

      使用场景

      -- 使用TRUNCATE：清空整个表
TRUNCATE TABLE logs RESTART IDENTITY;

-- 使用DELETE：按条件删除
DELETE FROM logs WHERE created_at < '2024-01-01';

      实际应用示例

      清空测试数据

      -- 清空测试表并重置ID
TRUNCATE TABLE test_users, test_orders RESTART IDENTITY CASCADE;

      数据重置脚本

      -- 事务中清空多表
BEGIN;

-- 禁用外键检查（通过CASCADE）
TRUNCATE TABLE order_items CASCADE;
TRUNCATE TABLE orders CASCADE;
TRUNCATE TABLE products RESTART IDENTITY;
TRUNCATE TABLE users RESTART IDENTITY;

COMMIT;

      定期清理日志表

      -- 清空旧日志并重置序列
TRUNCATE TABLE audit_logs RESTART IDENTITY;
TRUNCATE TABLE access_logs RESTART IDENTITY;

      触发器行为

      默认不触发行级触发器

      -- TRUNCATE不会触发行级DELETE触发器
-- 但可以创建TRUNCATE触发器
CREATE OR REPLACE FUNCTION log_truncate()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO truncate_log (table_name, truncated_at)
    VALUES (TG_TABLE_NAME, CURRENT_TIMESTAMP);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_truncate_trigger
    BEFORE TRUNCATE ON users
    FOR EACH STATEMENT
    EXECUTE FUNCTION log_truncate();

      外键约束处理

      有外键引用时

      -- 方法1：使用CASCADE
TRUNCATE TABLE departments CASCADE;

-- 方法2：先清空引用表
TRUNCATE TABLE employees;
TRUNCATE TABLE departments;

-- 方法3：临时禁用触发器
ALTER TABLE employees DISABLE TRIGGER ALL;
TRUNCATE TABLE departments;
ALTER TABLE employees ENABLE TRIGGER ALL;

      注意事项

      1. 权限要求：需要表的TRUNCATE权限或所有者权限
      2. 锁定：TRUNCATE会获取ACCESS EXCLUSIVE锁
      3. MVCC：TRUNCATE是MVCC安全的
      4. 分区表：可以TRUNCATE单个分区
      5. 复制：在逻辑复制中，TRUNCATE会被复制

      权限设置

      -- 授予TRUNCATE权限
GRANT TRUNCATE ON users TO developer;

-- 撤销TRUNCATE权限
REVOKE TRUNCATE ON users FROM developer;

      常见错误

      外键约束错误

      -- 错误信息
ERROR: cannot truncate a table referenced in a foreign key constraint

-- 解决方法
TRUNCATE TABLE parent_table CASCADE;

      权限不足

      -- 错误信息
ERROR: permission denied for table users

-- 解决方法：授予权限
GRANT TRUNCATE ON users TO username;