MySQL删除数据

概述

DELETE语句从表中删除行。本章介绍如何删除特定行、使用条件以及实现软删除模式。

DELETE语法

基本删除

删除特定行

-- Delete single row

-- Delete multiple rows

-- Delete with multiple conditions

删除所有行

-- Delete all rows

-- Delete all rows (DANGEROUS!)

-- Better: Use TRUNCATE for better performance

带条件的删除

WHERE子句

-- Delete based on ID

-- Delete with pattern

-- Delete with subquery

-- Delete with EXISTS

ORDER BY和LIMIT

-- Delete with ORDER BY

-- Delete oldest 100 records

-- Delete with ORDER BY and LIMIT

DELETE中的JOIN

-- Delete with INNER JOIN

-- Delete with LEFT JOIN

-- Delete with multiple tables

软删除

添加删除列

-- Add soft delete column

-- Create index for performance

实现软删除

-- Instead of DELETE, use UPDATE

-- Query with soft delete filter

-- Include deleted records

-- Include deleted with filter

软删除好处

-- Data preserved for audit

-- Recovery possible

-- No foreign key issues
-- Related records remain intact

批量删除操作

分块删除

-- Delete in chunks to avoid locking
-- Repeat until all deleted

-- Use stored procedure

计划清理

#!/bin/bash
# cleanup.sh - Delete old logs in chunks

DELETE与TRUNCATE比较

方面	DELETE	TRUNCATE
语法	DELETE FROM table	TRUNCATE TABLE table
WHERE子句	Supported / 支持	Not supported / 不支持
性能	Slower	Faster
自增	Preserved	Reset
触发器	Fires	Doesn't fire
事务	Can rollback	Cannot rollback
回滚	Supported	Not supported

安全删除实践

始终使用WHERE

-- DANGEROUS: Deletes all rows

-- SAFE: With WHERE clause

删除前备份

-- Create backup

-- Or use transaction
-- Verify
-- COMMIT; or ROLLBACK;

先用SELECT测试

-- First, verify what will be deleted

-- Then delete

常见删除模式

删除旧记录

-- Delete records older than 1 year

-- Delete records older than specific date

-- Delete duplicate records

基于关联表删除

-- Delete orphaned records

-- Delete with cascade simulation

-- Delete old cart items

故障排除

常见错误

-- Foreign key constraint error
-- Error: Cannot delete or update a parent row
-- Solution: Delete child records first or use ON DELETE CASCADE

-- Lock wait timeout
-- Error: Lock wait timeout exceeded
-- Solution: Delete in smaller batches

-- Table is read only
-- Error: Table is read only
-- Solution: Check table engine and permissions

调试删除

-- Check affected rows

-- Show tables using row

-- Check locks

小结

DELETE语句包括：

基本语法：使用WHERE删除行 WHERE子句：过滤要删除的行 JOIN删除：从多个表删除 软删除：使用deleted_at保留数据 批量操作：分块删除 安全：始终使用WHERE、先备份 TRUNCATE：清空表的更快选择

---

上一个：UPDATE

下一个：LIKE子句