Supabase数据删除 #

-- 删除单条记录
DELETE FROM users WHERE id = 1;

-- 删除并返回
DELETE FROM users WHERE id = 1 RETURNING *;

-- 删除多条记录
DELETE FROM users WHERE status = 'inactive';

-- 删除所有记录
DELETE FROM users;

// 删除单条记录
const { data, error } = await supabase
  .from('users')
  .delete()
  .eq('id', 1)
  .select()

// 删除多条记录
const { data, error } = await supabase
  .from('products')
  .delete()
  .eq('category', 'discontinued')
  .select()

重要提示
├── delete()必须配合过滤条件使用
├── 默认删除所有匹配的记录
├── 建议使用.eq('id', xxx)限制范围
└── 使用.select()返回删除的数据

// 按ID删除
const { data } = await supabase
  .from('posts')
  .delete()
  .eq('id', postId)

// 按多个条件删除
const { data } = await supabase
  .from('comments')
  .delete()
  .eq('post_id', postId)
  .lt('created_at', '2024-01-01')

// 删除特定用户的文章
const { data } = await supabase
  .from('posts')
  .delete()
  .eq('author_id', userId)
  .eq('id', postId)

-- 多条件删除
DELETE FROM products
WHERE category = 'discontinued' AND stock = 0;

-- 使用子查询删除
DELETE FROM products
WHERE id IN (
    SELECT product_id 
    FROM order_items 
    GROUP BY product_id 
    HAVING COUNT(*) = 0
);

-- 使用EXISTS删除
DELETE FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items o 
    WHERE o.product_id = p.id
);

// 删除指定ID列表
const { data, error } = await supabase
  .from('products')
  .delete()
  .in('id', [1, 2, 3, 4, 5])
  .select()

// 删除日期范围内的数据
const { data, error } = await supabase
  .from('logs')
  .delete()
  .lt('created_at', '2023-01-01')
  .select()

-- 添加软删除字段
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ DEFAULT NULL;
ALTER TABLE users ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;

-- 创建索引
CREATE INDEX idx_users_deleted ON users(deleted_at) WHERE deleted_at IS NULL;

// 软删除
const { data, error } = await supabase
  .from('users')
  .update({ 
    deleted_at: new Date().toISOString(),
    is_deleted: true
  })
  .eq('id', 1)
  .select()

// 恢复
const { data, error } = await supabase
  .from('users')
  .update({ 
    deleted_at: null,
    is_deleted: false
  })
  .eq('id', 1)
  .select()

// 永久删除已软删除的数据
const { data, error } = await supabase
  .from('users')
  .delete()
  .not('deleted_at', 'is', null)
  .select()

-- 创建视图
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;

-- 使用视图查询
SELECT * FROM active_users;

-- 创建表时设置级联删除
CREATE TABLE comments (
    id BIGSERIAL PRIMARY KEY,
    post_id BIGINT REFERENCES posts(id) ON DELETE CASCADE,
    content TEXT NOT NULL
);

-- 修改现有外键
ALTER TABLE comments
DROP CONSTRAINT comments_post_id_fkey,
ADD CONSTRAINT comments_post_id_fkey
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE;

外键删除选项
├── CASCADE: 删除父记录时自动删除子记录
├── SET NULL: 删除父记录时将外键设为NULL
├── SET DEFAULT: 删除父记录时设为默认值
├── RESTRICT: 阻止删除有子记录的父记录
└── NO ACTION: 与RESTRICT类似，延迟检查

// 手动删除关联数据
async function deletePost(postId: number) {
  // 删除评论
  await supabase
    .from('comments')
    .delete()
    .eq('post_id', postId)

  // 删除文章
  const { data, error } = await supabase
    .from('posts')
    .delete()
    .eq('id', postId)
    .select()

  return { data, error }
}

-- 创建级联删除函数
CREATE OR REPLACE FUNCTION delete_user_cascade(user_id UUID)
RETURNS VOID AS $$
BEGIN
    -- 删除用户的所有文章评论
    DELETE FROM comments WHERE user_id = user_id;
    
    -- 删除用户的所有文章
    DELETE FROM posts WHERE author_id = user_id;
    
    -- 删除用户资料
    DELETE FROM profiles WHERE id = user_id;
    
    -- 最后删除用户
    DELETE FROM auth.users WHERE id = user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

// 调用级联删除函数
const { error } = await supabase.rpc('delete_user_cascade', {
  user_id: userId
})

-- 清空表数据
TRUNCATE TABLE logs;

-- 重置自增序列
TRUNCATE TABLE logs RESTART IDENTITY;

-- 级联清空关联表
TRUNCATE TABLE posts CASCADE;

-- 清空多个表
TRUNCATE TABLE logs, sessions, cache;

// 使用RPC清空表
const { error } = await supabase.rpc('truncate_table', {
  table_name: 'logs'
})

CREATE OR REPLACE FUNCTION truncate_table(table_name TEXT)
RETURNS VOID AS $$
BEGIN
    EXECUTE format('TRUNCATE TABLE %I', table_name);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

async function safeDeletePost(postId: number) {
  // 检查是否有评论
  const { count } = await supabase
    .from('comments')
    .select('*', { count: 'exact', head: true })
    .eq('post_id', postId)

  if (count && count > 0) {
    throw new Error(`Cannot delete post with ${count} comments`)
  }

  // 安全删除
  const { data, error } = await supabase
    .from('posts')
    .delete()
    .eq('id', postId)
    .select()

  return { data, error }
}

-- 创建安全删除函数
CREATE OR REPLACE FUNCTION safe_delete_post(post_id BIGINT)
RETURNS JSON AS $$
DECLARE
    comment_count INTEGER;
BEGIN
    -- 检查评论数量
    SELECT COUNT(*) INTO comment_count
    FROM comments WHERE post_id = post_id;
    
    IF comment_count > 0 THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Post has comments',
            'comment_count', comment_count
        );
    END IF;
    
    -- 删除文章
    DELETE FROM posts WHERE id = post_id;
    
    RETURN json_build_object('success', true);
END;
$$ LANGUAGE plpgsql;

async function batchDelete(table: string, batchSize = 1000) {
  let deleted = 0

  while (true) {
    const { data, error } = await supabase
      .from(table)
      .delete()
      .lt('created_at', '2023-01-01')
      .limit(batchSize)
      .select('id')

    if (error) throw error
    if (!data?.length) break

    deleted += data.length
    console.log(`Deleted ${deleted} records`)
  }

  return deleted
}

-- 使用DO块分批删除
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    LOOP
        DELETE FROM logs
        WHERE created_at < '2023-01-01'
        LIMIT 10000;
        
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        EXIT WHEN deleted_count = 0;
        
        COMMIT;
    END LOOP;
END $$;

-- 创建触发器函数
CREATE OR REPLACE FUNCTION before_user_delete()
RETURNS TRIGGER AS $$
BEGIN
    -- 记录删除操作
    INSERT INTO user_deletion_log (user_id, email, deleted_at)
    VALUES (OLD.id, OLD.email, NOW());
    
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER on_user_delete
    BEFORE DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION before_user_delete();

-- 创建触发器函数
CREATE OR REPLACE FUNCTION after_post_delete()
RETURNS TRIGGER AS $$
BEGIN
    -- 更新用户文章计数
    UPDATE profiles
    SET post_count = post_count - 1
    WHERE id = OLD.author_id;
    
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_post_delete
    AFTER DELETE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION after_post_delete();

const { data, error } = await supabase
  .from('users')
  .delete()
  .eq('id', 1)

if (error) {
  switch (error.code) {
    case '23503':
      console.error('外键约束违反，存在关联数据')
      break
    case '42501':
      console.error('权限不足')
      break
    case 'PGRST116':
      console.error('未找到匹配记录')
      break
    default:
      console.error('删除失败:', error.message)
  }
}

async function deleteUserAccount(userId: string) {
  // 1. 删除用户数据
  const { error: profileError } = await supabase
    .from('profiles')
    .delete()
    .eq('id', userId)

  if (profileError) throw profileError

  // 2. 删除用户文章
  const { error: postsError } = await supabase
    .from('posts')
    .delete()
    .eq('author_id', userId)

  if (postsError) throw postsError

  // 3. 删除认证用户
  const { error: authError } = await supabaseAdmin.auth.admin.deleteUser(userId)

  if (authError) throw authError

  return { success: true }
}

async function cleanupExpiredData() {
  const thirtyDaysAgo = new Date()
  thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30)

  // 删除过期会话
  const { error: sessionsError } = await supabase
    .from('sessions')
    .delete()
    .lt('expires_at', new Date().toISOString())

  // 删除旧日志
  const { error: logsError } = await supabase
    .from('logs')
    .delete()
    .lt('created_at', thirtyDaysAgo.toISOString())

  return { success: !sessionsError && !logsError }
}

async function clearCart(userId: string) {
  const { data, error } = await supabase
    .from('cart_items')
    .delete()
    .eq('user_id', userId)
    .select()

  if (error) throw error
  return data
}

删除前检查
├── 确认删除条件正确
├── 检查外键约束
├── 考虑使用软删除
├── 备份重要数据
├── 考虑级联影响
└── 记录删除操作

删除策略
├── 软删除
│   ├── 可恢复
│   ├── 保留历史
│   └── 适合用户数据
│
├── 硬删除
│   ├── 节省空间
│   ├── 彻底清除
│   └── 适合临时数据
│
└── 归档
    ├── 数据迁移
    ├── 长期保存
    └── 适合历史数据