跳到主要内容

DELETE 删除

DELETE 语句用于从表中删除数据。本章将详细介绍 DELETE 的各种用法。

基本 DELETE​

删除特定行​

DELETE FROM users WHERE id = 1;

删除所有行​

-- 危险！删除所有数据
DELETE FROM users;

-- 表仍然存在，只是数据被清空
警告

不带 WHERE 子句的 DELETE 会删除所有行！在生产环境中要特别小心。

删除多行​

-- 删除符合条件的所有行
DELETE FROM users WHERE city = '北京';

-- 删除多个条件
DELETE FROM orders 
WHERE status = 'cancelled' 
  AND created_at < '2024-01-01';

WHERE 条件​

使用各种条件​

-- 使用 IN
DELETE FROM users WHERE id IN (1, 2, 3);

-- 使用 BETWEEN
DELETE FROM orders WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

-- 使用 LIKE
DELETE FROM users WHERE email LIKE '%@temp.com';

-- 使用子查询
DELETE FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE status = 'deleted');

使用 LIMIT 限制​

-- MySQL: 只删除前 100 条
DELETE FROM logs 
WHERE created_at < '2024-01-01' 
LIMIT 100;

-- PostgreSQL: 使用子查询
DELETE FROM logs 
WHERE id IN (
    SELECT id FROM logs 
    WHERE created_at < '2024-01-01' 
    LIMIT 100
);

多表删除​

MySQL: 多表 DELETE​

-- 删除订单及其订单项
DELETE o, oi
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'cancelled';

-- 只删除订单项
DELETE oi
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'cancelled';

PostgreSQL: USING 子句​

DELETE FROM order_items
USING orders
WHERE order_items.order_id = orders.id
AND orders.status = 'cancelled';

SQL Server: FROM 子句​

DELETE oi
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'cancelled';

返回删除结果​

PostgreSQL: RETURNING​

-- 返回删除的数据
DELETE FROM users WHERE id = 1 RETURNING *;

-- 返回特定列
DELETE FROM logs 
WHERE created_at < '2024-01-01' 
RETURNING id, message;

MySQL: 获取影响行数​

DELETE FROM users WHERE status = 'inactive';
SELECT ROW_COUNT();  -- 返回删除的行数

TRUNCATE vs DELETE​

TRUNCATE​

快速清空表：

TRUNCATE TABLE users;

区别对比​

特性DELETETRUNCATE
速度较慢很快
WHERE 条件支持不支持
触发器触发不触发
回滚可以不能（大多数数据库）
自增 ID 重置不重置重置
事务日志记录每行只记录页释放
-- DELETE: 可以回滚
START TRANSACTION;
DELETE FROM users;
ROLLBACK;  -- 数据恢复

-- TRUNCATE: 通常不能回滚
TRUNCATE TABLE users;  -- 数据永久删除

使用场景​

-- 使用 DELETE：需要条件删除或触发器
DELETE FROM users WHERE status = 'inactive';

-- 使用 TRUNCATE：快速清空整表
TRUNCATE TABLE logs;
TRUNCATE TABLE temp_data;

安全删除​

使用事务​

START TRANSACTION;

-- 先查询确认
SELECT * FROM users WHERE status = 'inactive';

-- 执行删除
DELETE FROM users WHERE status = 'inactive';

-- 确认无误后提交
COMMIT;

-- 如果有问题，可以回滚
-- ROLLBACK;

先查询再删除​

-- 好的做法：先查询确认
SELECT COUNT(*) FROM users WHERE created_at < '2023-01-01';
-- 确认数量合理后再删除
DELETE FROM users WHERE created_at < '2023-01-01';

软删除​

不真正删除，而是标记为已删除：

-- 添加删除标记列
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;

-- 软删除
UPDATE users SET deleted_at = NOW() WHERE id = 1;

-- 查询时排除已删除
SELECT * FROM users WHERE deleted_at IS NULL;

-- 真正删除（清理时）
DELETE FROM users WHERE deleted_at IS NOT NULL AND deleted_at < '2023-01-01';

级联删除​

外键级联​

-- 创建表时设置级联删除
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 删除用户时自动删除其订单
DELETE FROM users WHERE id = 1;  -- 相关订单也会被删除

级联选项​

选项说明
CASCADE删除关联记录
SET NULL设置关联列为 NULL
SET DEFAULT设置关联列为默认值
RESTRICT阻止删除（默认）
NO ACTION同 RESTRICT
-- 设置为 NULL
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- 阻止删除
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
);

性能优化​

分批删除​

-- 大批量删除分批进行
-- MySQL
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 1000;
-- 重复执行直到影响行数为 0

-- PostgreSQL
DELETE FROM logs 
WHERE id IN (
    SELECT id FROM logs WHERE created_at < '2023-01-01' LIMIT 1000
);

使用索引​

-- 确保条件列有索引
CREATE INDEX idx_created_at ON logs(created_at);

DELETE FROM logs WHERE created_at < '2023-01-01';

使用 TRUNCATE 清空大表​

-- 快速清空大表
TRUNCATE TABLE logs;

-- 如果需要保留表结构但重置自增 ID
TRUNCATE TABLE users;

常见错误​

忘记 WHERE 子句​

-- 错误：删除所有数据！
DELETE FROM users;

-- 正确：指定条件
DELETE FROM users WHERE id = 1;

违反外键约束​

-- 尝试删除有订单的用户
DELETE FROM users WHERE id = 1;
-- Error: Cannot delete or update a parent row: a foreign key constraint fails

-- 解决方案1：先删除关联数据
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE id = 1;

-- 解决方案2：使用级联删除

删除不存在的记录​

-- 不会报错，但影响行数为 0
DELETE FROM users WHERE id = 99999;

-- 检查影响行数
SELECT ROW_COUNT();  -- MySQL

小结​

本章我们学习了：

  1. 基本 DELETE 语法
  2. WHERE 条件删除
  3. 多表删除
  4. 返回删除结果
  5. TRUNCATE vs DELETE
  6. 安全删除实践
  7. 级联删除
  8. 性能优化

练习​

  1. 删除 id 为 5 的用户
  2. 删除 2023 年之前的所有日志记录
  3. 删除状态为"已取消"的订单
  4. 使用事务安全地执行批量删除
  5. 实现软删除功能，将用户标记为已删除而不是真正删除