MySQL 数据库多种数据删除方式详解
作为MySQL DBA，我将为您详细介绍不同类型的数据库删除操作方式，包括它们的适用场景、语法示例以及注意事项。
1. 基础删除操作
1.1 DELETE 语句（逐行删除）
特点：
逐行删除，产生binlog可回滚触发触发器
-- 基本语法
DELETE FROM table_name WHERE condition;
-- 示例：删除特定记录
DELETE FROM employees WHERE department = 'HR' AND hire_date < '2020-01-01';
-- 删除所有记录（危险！）
DELETE FROM temp_logs;
注意事项：
不加WHERE条件会删除全表数据大表删除可能导致性能问题会留下碎片需要后续optimize table
1.2 TRUNCATE TABLE
特点：
整表删除，速度快不产生单行binlog不可回滚不触发触发器重置自增值
-- 语法
TRUNCATE TABLE table_name;
-- 示例
TRUNCATE TABLE session_logs;
注意事项：
不能带WHERE条件需要DROP权限比DELETE快很多，特别是大表
2. 高级删除技术
2.1 联表删除
-- 删除满足多表关联条件的记录
DELETE t1 FROM table1 t1
JOIN table2 t2 ON t1.id = t2.table1_id
WHERE t2.status = 'expired';
-- 同时删除多个表的记录
DELETE t1, t2 FROM table1 t1
JOIN table2 t2 ON t1.id = t2.table1_id
WHERE t1.create_date < '2022-01-01';
2.2 分区表删除
-- 删除整个分区（DDL操作，非常快）
ALTER TABLE sales DROP PARTITION p2020;
-- 删除分区内数据（保留分区结构）
ALTER TABLE sales TRUNCATE PARTITION p2021;
-- 按条件删除分区数据
DELETE FROM sales PARTITION(p2022) WHERE amount < 100;
2.3 使用子查询删除
-- 使用子查询确定删除范围
DELETE FROM products
WHERE category_id IN (
SELECT category_id FROM categories
WHERE discontinued = 1
);
-- MySQL 8.0+ 可使用CTE
WITH inactive_users AS (
SELECT user_id FROM users WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)
)
DELETE FROM user_sessions
WHERE user_id IN (SELECT user_id FROM inactive_users);
3. 批量删除策略
3.1 分批删除
-- 使用存储过程实现分批删除
DELIMITER //
CREATE PROCEDURE batch_delete(IN batch_size INT)
BEGIN
DECLARE deleted_rows INT DEFAULT 1;
WHILE deleted_rows > 0 DO
DELETE FROM large_table
WHERE condition
LIMIT batch_size;
SET deleted_rows = ROW_COUNT();
COMMIT;
DO SLEEP(1); -- 减轻服务器负载
END WHILE;
END //
DELIMITER ;
-- 调用
CALL batch_delete(1000);
3.2 创建新表替换
-- 对大表高效"删除"方法（保留需要的数据）
CREATE TABLE new_table AS
SELECT * FROM old_table WHERE keep_condition;
-- 原子性替换
RENAME TABLE old_table TO old_table_backup, new_table TO old_table;
-- 重建索引和约束
ALTER TABLE old_table ADD PRIMARY KEY (id);
4. 伪删除（逻辑删除）
4.1 标记删除法
-- 添加删除标记字段
ALTER TABLE customers ADD COLUMN is_deleted TINYINT DEFAULT 0;
ALTER TABLE customers ADD COLUMN deleted_at DATETIME;
-- 更新标记代替删除
UPDATE customers
SET is_deleted = 1, deleted_at = NOW()
WHERE customer_id = 12345;
-- 查询时排除已删除数据
SELECT * FROM customers WHERE is_deleted = 0;
4.2 历史表归档法
-- 将删除数据移到历史表
INSERT INTO customers_history
SELECT *, NOW() AS deleted_at FROM customers
WHERE customer_id = 12345;
-- 然后从主表删除
DELETE FROM customers WHERE customer_id = 12345;
5. 数据销毁（安全删除）
5.1 安全覆盖删除
-- 对于敏感数据，先覆盖再删除
UPDATE sensitive_data
SET ssn = CONCAT('DELETED-', UUID()), name = 'REDACTED'
WHERE condition;
-- 然后执行删除
DELETE FROM sensitive_data WHERE condition;
5.2 物理文件销毁
-- 1. 导出表结构
mysqldump -d database_name > schema.sql
-- 2. 删除整个数据库
DROP DATABASE database_name;
-- 3. 使用安全删除工具擦除磁盘空间
-- (在操作系统层面执行，如Linux下的shred)
6. 级联删除
6.1 外键约束级联删除
-- 创建表时设置级联删除
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE
);
-- 删除主表记录时，关联记录自动删除
DELETE FROM customers WHERE customer_id = 1001;
6.2 手动级联删除
-- 多表级联删除
BEGIN;
DELETE FROM order_items WHERE order_id IN (
SELECT order_id FROM orders WHERE customer_id = 1001
);
DELETE FROM orders WHERE customer_id = 1001;
DELETE FROM customers WHERE customer_id = 1001;
COMMIT;
7. 基于时间的删除策略
7.1 定时自动删除
-- 创建事件定期清理
DELIMITER //
CREATE EVENT purge_old_data
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
DELETE FROM system_logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
END //
DELIMITER ;
-- 启用事件调度器
SET GLOBAL event_scheduler = ON;
7.2 分区表按时间删除
-- 按时间范围分区
CREATE TABLE logs (
id INT,
log_time DATETIME,
message TEXT
) PARTITION BY RANGE (TO_DAYS(log_time)) (
PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- 定期删除最老分区
ALTER TABLE logs DROP PARTITION p202301;
关键注意事项
备份优先：执行任何删除前确保有可靠备份权限控制：限制开发人员的删除权限性能考虑：
大表删除在低峰期进行考虑使用pt-archiver等工具 事务使用：重要删除操作放在事务中监控影响：删除后监控数据库性能合规要求：确保删除操作符合数据保留政策
每种删除方法都有其适用场景，选择时应考虑：
数据量大小业务重要性性能影响恢复需求合规要求