2.14 sql数据删除（DELETE、TRUNCATE）

2.14 数据删除（DELETE、TRUNCATE）

这一章我会带你彻底搞懂SQL中删除数据的两大利器：DELETE和TRUNCATE。学完之后，你能安全地清理无效订单、测试数据，并能区分什么时候用DELETE，什么时候用TRUNCATE。

学习前准备：

• 已完成MySQL安装（参考系列前几章）

• 已安装DBeaver或Navicat

• 准备一个练习数据库，比如delete_demo

学习前环境准备

步骤1：确保MySQL服务已启动。

步骤2：创建练习数据库和表。

CREATEDATABASEdelete_demoCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;USEdelete_demo;-- 订单表CREATETABLEorders(order_idVARCHAR(50)PRIMARYKEY,user_idINTNOTNULL,amountDECIMAL(10,2)NOTNULL,order_statusTINYINTNOTNULLDEFAULT1COMMENT'1待支付,2已支付,3已取消,4已完成',create_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP);-- 用户表CREATETABLEusers(user_idINTPRIMARYKEY,user_nameVARCHAR(50));-- 插入测试数据INSERTINTOusers(user_id,user_name)VALUES(1,'张三'),(2,'李四'),(3,'王五');INSERTINTOorders(order_id,user_id,amount,order_status,create_time)VALUES('ORD001',1,299.00,2,'2025-06-01 10:00:00'),('ORD002',2,189.00,1,'2025-06-01 11:00:00'),('ORD003',3,599.00,3,'2025-06-02 09:30:00'),('ORD004',1,399.00,2,'2025-06-03 14:20:00'),('ORD005',2,99.00,4,'2025-06-04 16:00:00');

DELETE与TRUNCATE的基础认知

3.1 核心定义

• DELETE：逐行删除数据，支持WHERE条件，可以回滚（在事务中）。删除速度慢，但更灵活。

• TRUNCATE：清空整张表，不支持WHERE条件，不能回滚（在MySQL中默认自动提交）。删除速度快，相当于DROP后重建表。

3.2 核心区别

3.3 电商场景下的适用选择

• 删除特定条件的行（如超时未支付订单）：用DELETE。

• 清空临时表或测试表：用TRUNCATE。

• 删除大批量数据且需要释放磁盘空间：用TRUNCATE（如果全表清空）或分批DELETE后OPTIMIZE TABLE。

我的踩坑经历：有一次我想清空一张临时表，用了DELETE FROM temp_table，没有加WHERE。虽然删光了数据，但表占用的磁盘空间没释放，而且自增ID还在增长。后来改用TRUNCATE temp_table，空间释放了，ID也重置了。清空整张表，用TRUNCATE比DELETE更合适。

带WHERE条件的精准删除

4.1 基础语法

DELETEFROM表名WHERE条件;

4.2 电商实操案例

案例一：删除超时未支付的订单

电商规则：超过30分钟未支付的订单自动取消，需要从订单表中删除（或标记为取消）。这里演示物理删除。

-- 删除30分钟前创建的待支付订单DELETEFROMordersWHEREorder_status=1ANDcreate_time<NOW()-INTERVAL30MINUTE;

案例二：删除指定用户的测试订单

-- 删除用户ID为999的测试订单DELETEFROMordersWHEREuser_id=999;

案例三：删除特定时间段内的已取消订单

-- 删除2024年之前已取消的订单DELETEFROMordersWHEREorder_status=3ANDcreate_time<'2024-01-01';

4.3 安全操作黄金法则（再次强调）

执行DELETE前，必须先用相同的WHERE条件执行SELECT，确认要删除的行数正确。

-- 第一步：查看要删除的数据SELECT*FROMordersWHEREorder_status=3ANDcreate_time<'2024-01-01';-- 第二步：执行删除DELETEFROMordersWHEREorder_status=3ANDcreate_time<'2024-01-01';-- 第三步：验证删除结果（可选）SELECTCOUNT(*)FROMordersWHEREorder_status=3ANDcreate_time<'2024-01-01';-- 应为0

4.4 限制删除行数（LIMIT）

MySQL支持DELETE语句中使用LIMIT，限制删除的行数，分批删除可避免锁表过久。

-- 每次只删除1000条超时订单DELETEFROMordersWHEREorder_status=1ANDcreate_time<NOW()-INTERVAL30MINUTELIMIT1000;

可以循环执行直到影响行数为0。

实操避坑提醒：DELETE中如果使用了LIMIT，ORDER BY不是必须的，但为了可预测性，建议加上ORDER BY。另外，LIMIT在删除时可能造成“跳过”数据，如果表在并发写入，最好配合主键排序。

全表数据删除：DELETE无WHERE 与 TRUNCATE

5.1 DELETE无WHERE（全表删除）

DELETEFROMorders;

• 特点：删除所有行，但表结构、索引、自增ID起始值保留（继续递增）。速度慢，支持事务回滚。

5.2 TRUNCATE（清空表）

TRUNCATETABLEorders;

• 特点：快速清空所有行，自增ID重置为1，释放磁盘空间，不支持回滚（在MySQL中默认提交）。

5.3 分步操作对比

使用DELETE清空测试表：

STARTTRANSACTION;DELETEFROMorders_test;-- 检查结果，如果正确则COMMIT，否则ROLLBACKCOMMIT;

使用TRUNCATE清空测试表：

TRUNCATETABLEorders_test;

5.4 电商场景实操：清空临时表

大促期间，每小时会创建临时表orders_hour存储实时数据，处理完后需要清空。

-- 推荐用TRUNCATETRUNCATETABLEorders_hour;

5.5 避坑提醒

• TRUNCATE不能回滚，执行前必须确认是测试环境或已备份。

• TRUNCATE会重置自增ID，如果业务依赖ID连续性，慎用。

• TRUNCATE不会触发DELETE触发器，如果表上有外键约束且子表有数据，可能无法执行（取决于外键设置）。

我的踩坑经历：有一次我准备清空一张生产环境的临时表，用了TRUNCATE，结果发现这张表有外键被其他表引用，执行失败。后来改用DELETE逐行删除才成功。TRUNCATE遇到外键约束时可能失败，需要先处理子表数据或临时禁用外键检查。

关联表删除

6.1 使用子查询删除

电商场景：删除所有没有关联用户的订单（孤儿订单）。

DELETEFROMordersWHEREuser_idNOTIN(SELECTuser_idFROMusers);

注意：MySQL中，DELETE的子查询不能直接引用被删除的表（某些版本会报错），可以用多表删除语法绕过。

6.2 使用多表JOIN删除（推荐）

语法：

DELETE别名1,别名2FROM表1别名1JOIN表2别名2ON条件WHERE筛选;

电商场景一：删除用户及其所有订单（级联删除）

-- 删除用户ID为1的用户及其所有订单DELETEu,oFROMusers uLEFTJOINorders oONu.user_id=o.user_idWHEREu.user_id=1;

电商场景二：删除没有订单的用户

DELETEuFROMusers uLEFTJOINorders oONu.user_id=o.user_idWHEREo.order_idISNULL;

电商场景三：删除已取消订单中的无效商品明细（假设有订单明细表）

先创建订单明细表示例：

CREATETABLEorder_items(item_idINTPRIMARYKEYAUTO_INCREMENT,order_idVARCHAR(50),product_nameVARCHAR(100),FOREIGNKEY(order_id)REFERENCESorders(order_id));INSERTINTOorder_items(order_id,product_name)VALUES('ORD003','测试商品'),('ORD003','另一个商品');

删除已取消订单对应的明细：

DELETEoiFROMorder_items oiJOINorders oONoi.order_id=o.order_idWHEREo.order_status=3;

6.3 分步操作

1. 先用SELECT验证关联结果。

2. 将SELECT改为DELETE，注意指定要删除的表别名。

3. 执行并验证。

-- 验证SELECToi.*FROMorder_items oiJOINorders oONoi.order_id=o.order_idWHEREo.order_status=3;-- 删除DELETEoiFROMorder_items oiJOINorders oONoi.order_id=o.order_idWHEREo.order_status=3;

实操避坑提醒：多表删除时，DELETE后面跟的是要删除的表的别名，不是*。如果要同时删除多表数据，用逗号分隔别名（如DELETE u, o FROM ...）。务必确认哪些表需要删除，避免误删。

综合实操案例：年度历史无效测试数据清理

7.1 案例背景

某服饰类目电商店铺需要进行数据清理，任务包括：

1. 删除所有2023年之前创建的、状态为“已取消”的订单。

2. 删除超过1年未登录的用户（假设有last_login_time字段，这里简化用user_id不在新订单中出现）。

3. 清空临时表temp_order_import中的数据（使用TRUNCATE）。

4. 删除没有关联订单的用户（孤儿用户）。

7.2 准备工作

添加必要的字段和测试数据。

-- 添加last_login_time字段（模拟）ALTERTABLEusersADDlast_login_timeDATETIMEDEFAULTNOW();UPDATEusersSETlast_login_time='2023-01-01'WHEREuser_id=3;-- 模拟老用户-- 创建临时表CREATETABLEtemp_order_importLIKEorders;INSERTINTOtemp_order_importSELECT*FROMordersWHERE1=0;-- 空表

7.3 分步操作

步骤1：删除2023年前的已取消订单

-- 先查看SELECT*FROMordersWHEREorder_status=3ANDcreate_time<'2023-01-01';-- 删除DELETEFROMordersWHEREorder_status=3ANDcreate_time<'2023-01-01';

步骤2：删除超过1年未登录的用户（假设条件：用户没有在最近一年的订单中出现）

-- 查看孤儿用户SELECTu.*FROMusers uLEFTJOINorders oONu.user_id=o.user_idWHEREo.order_idISNULL;-- 删除DELETEuFROMusers uLEFTJOINorders oONu.user_id=o.user_idWHEREo.order_idISNULL;

步骤3：清空临时表

TRUNCATETABLEtemp_order_import;

步骤4：验证清理结果

-- 检查订单表行数SELECTCOUNT(*)FROMorders;-- 检查用户表行数SELECTCOUNT(*)FROMusers;

7.4 合规提示

📌 电商数据合规红线：

• 删除用户数据前，必须确认符合数据保留政策。例如，用户注销后，根据《个人信息保护法》，数据保留不应超过必要期限。删除前应确认是否有未结订单或法律义务。
• 禁止物理删除生产核心表数据。推荐采用“软删除”（增加is_deleted字段标记），便于审计和恢复。
• 删除操作必须记录日志：包括操作人、时间、删除条件、影响行数。重要删除需审批。

本章踩坑清单与合规总结

8.1 新手常见踩坑

8.2 安全删除最佳实践

1. 永远先备份：CREATE TABLE orders_backup_20250401 AS SELECT * FROM orders WHERE 条件;

2. 开启事务：START TRANSACTION;→ 执行DELETE→SELECT验证 →COMMIT;或ROLLBACK;

3. 限制删除行数：大表用LIMIT分批删除，避免长事务。

4. 检查外键依赖：删除父表数据前，确认子表已处理或使用ON DELETE CASCADE。

5. 生产环境删除必须走审批。

8.3 电商数据合规提示

• 软删除优于硬删除：在订单表、用户表中增加is_deleted字段，默认0。查询时加上WHERE is_deleted = 0。这样数据可追溯，满足合规审计要求。

• 删除用户数据需满足“最小必要”原则：只删除不再需要的字段，而不是整条记录。

• 定期归档而非删除：历史数据可以移到归档库或冷存储，而不是直接物理删除。

结语

DELETE和TRUNCATE是数据生命周期管理的重要工具。掌握它们，你就能安全地清理无效数据、归档历史记录。但永远记住：删除操作不可逆，谨慎是唯一的安全带。

有问题的评论区留言，我看到会回复。