删除数据DELETE - 元素码农

发布时间: 2025-04-02 22:54

↑

# 删除数据DELETE

本文将详细介绍如何在SQLite数据库中删除数据，包括基本删除操作、条件删除以及删除时的注意事项和最佳实践。

## 基本删除语法

### 单行删除

1. **基本语法**
   ```sql
   DELETE FROM table_name
   WHERE condition;
   ```

2. **示例**
   ```sql
   DELETE FROM users
   WHERE id = 1;
   ```

### 删除所有数据

1. **清空表**
   ```sql
   DELETE FROM table_name;
   ```

2. **使用TRUNCATE**
   ```sql
   -- SQLite中没有TRUNCATE语句，可以使用DELETE
   DELETE FROM table_name;
   -- 重置自增计数器
   DELETE FROM sqlite_sequence WHERE name = 'table_name';
   ```

## 条件删除

### WHERE子句

1. **比较条件**
   ```sql
   -- 删除过期记录
   DELETE FROM sessions
   WHERE expiry_date < CURRENT_TIMESTAMP;
   ```

2. **多条件组合**
   ```sql
   DELETE FROM users
   WHERE status = 'inactive'
   AND last_login < date('now', '-90 days');
   ```

### 子查询删除

1. **使用IN子句**
   ```sql
   DELETE FROM orders
   WHERE customer_id IN (
       SELECT id FROM customers
       WHERE status = 'blocked'
   );
   ```

2. **使用EXISTS**
   ```sql
   DELETE FROM products
   WHERE EXISTS (
       SELECT 1 FROM discontinued_items
       WHERE discontinued_items.product_id = products.id
   );
   ```

## 批量删除

### 事务处理

1. **使用事务**
   ```sql
   BEGIN TRANSACTION;
   
   -- 删除订单项
   DELETE FROM order_items
   WHERE order_id = 100;
   
   -- 删除订单
   DELETE FROM orders
   WHERE id = 100;
   
   COMMIT;
   ```

2. **错误处理**
   ```sql
   BEGIN TRANSACTION;
   
   DELETE FROM orders
   WHERE id IN (1, 2, 3);
   
   -- 如果出错则回滚
   ROLLBACK;
   ```

### 分批删除

1. **使用LIMIT**
   ```sql
   -- 每次删除1000条记录
   DELETE FROM logs
   WHERE created_at < date('now', '-30 days')
   LIMIT 1000;
   ```

2. **循环删除**
   ```sql
   -- 在应用代码中循环执行
   DELETE FROM logs
   WHERE id IN (
       SELECT id FROM logs
       WHERE created_at < date('now', '-30 days')
       LIMIT 1000
   );
   ```

## 级联删除

### 外键约束

1. **启用级联删除**
   ```sql
   CREATE TABLE orders (
       id INTEGER PRIMARY KEY,
       customer_id INTEGER,
       FOREIGN KEY (customer_id) 
           REFERENCES customers(id)
           ON DELETE CASCADE
   );
   ```

2. **手动级联**
   ```sql
   BEGIN TRANSACTION;
   
   -- 先删除子表数据
   DELETE FROM order_items
   WHERE order_id IN (SELECT id FROM orders WHERE customer_id = 1);
   
   -- 删除主表数据
   DELETE FROM orders
   WHERE customer_id = 1;
   
   -- 最后删除客户
   DELETE FROM customers
   WHERE id = 1;
   
   COMMIT;
   ```

## 性能优化

### 索引使用

1. **利用索引**
   ```sql
   -- 使用索引列作为删除条件
   DELETE FROM users
   WHERE email = 'user@example.com';
   ```

2. **避免全表扫描**
   ```sql
   -- 使用索引列进行范围删除
   DELETE FROM logs
   WHERE created_at < date('now', '-30 days')
   AND id > last_processed_id
   LIMIT 1000;
   ```

### 优化策略

1. **分批处理**
   ```sql
   -- 在应用代码中实现
   PRAGMA journal_mode = WAL;
   BEGIN TRANSACTION;
   
   -- 每批处理1000条
   DELETE FROM logs
   WHERE id IN (
       SELECT id FROM logs
       WHERE created_at < date('now', '-30 days')
       LIMIT 1000
   );
   
   COMMIT;
   ```

2. **使用临时表**
   ```sql
   -- 创建临时表存储要删除的ID
   CREATE TEMP TABLE tmp_delete AS
   SELECT id FROM logs
   WHERE created_at < date('now', '-30 days');
   
   -- 使用临时表删除
   DELETE FROM logs
   WHERE id IN (SELECT id FROM tmp_delete);
   ```

## 最佳实践

### 安全删除

1. **数据备份**
   - 重要操作前备份
   - 使用事务保护
   - 验证删除结果

2. **条件验证**
   - 仔细检查WHERE条件
   - 先用SELECT测试
   - 保留操作日志

### 性能考虑

1. **批量操作**
   - 合适的批次大小
   - 使用事务包装
   - 监控性能影响

2. **资源管理**
   - 控制锁定时间
   - 避免长事务
   - 定期VACUUM

## 总结

删除数据需要注意：

- 正确使用WHERE子句
- 注意级联删除影响
- 考虑性能优化
- 做好数据备份

建议：

- 使用事务保护
- 分批处理大量数据
- 验证删除结果
- 保持数据一致性