truncate table在存储过程中危险，因其是ddl操作，会隐式提交事务、无法回滚、受外键约束阻断、重置自增id且持有sch_m锁影响并发。
直接用 TRUNCATE TABLE 在存储过程中是危险的，尤其对大表——它不走事务、无法回滚、会锁整个表结构、且受外键约束硬性阻断。 真实生产中，你得根据表是否被引用、是否允许中断、是否需保留自增起点等条件，动态选策略，而不是写死一条 TRUNCATE。
为什么不能在存储过程里无脑写 TRUNCATE TABLE
MySQL 存储过程默认在 AUTOCOMMIT=1 下执行，TRUNCATE TABLE 是 DDL 操作，会隐式提交当前事务（哪怕你套了 BEGIN...END），导致前面所有 DML 无法回滚。更麻烦的是：
- 若该表被其他表外键引用（比如
orders被order_items引用），TRUNCATE直接报错：ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint - 它会重置
AUTO_INCREMENT，如果下游业务依赖 ID 连续性或做幂等校验，可能引发逻辑异常 - 虽不锁数据行，但会获取
SCH_M（schema modification）锁，阻塞所有 DDL 和大部分 DML，对高并发表影响明显
安全清理大表的三类存储过程写法
核心原则：能分批就分批，能绕开外键就绕开，能预检就预检。以下三种写法按风险从低到高排列：
-
场景：表独立、无外键、可接受 ID 重置 → 先关外键检查，再
TRUNCATE，最后恢复：SET FOREIGN_KEY_CHECKS = 0;<br>TRUNCATE TABLE target_table;<br>SET FOREIGN_KEY_CHECKS = 1;
注意：必须确保调用者没开启事务，否则TRUNCATE会提前提交，破坏原子性 -
场景：表有外键、但允许清空全部数据 → 改用
DELETE+ 分批次 + 显式事务控制：START TRANSACTION;<br>DELETE FROM target_table ORDER BY id LIMIT 10000;<br>-- 循环执行直到影响行为 0<br>COMMIT;
关键点：加ORDER BY id避免索引扫描跳过记录；每次LIMIT不宜超过 5 万，否则日志暴涨；建议在存储过程中用REPEAT...UNTIL控制循环 -
场景：超大表（亿级）、不能停写、需最小化锁时长 → 用“重建表”模式（即
CREATE TABLE ... SELECT+RENAME）：CREATE TABLE target_table_new LIKE target_table;<br>INSERT INTO target_table_new SELECT * FROM target_table WHERE 1=0; -- 空表结构<br>RENAME TABLE target_table TO target_table_old, target_table_new TO target_table;<br>DROP TABLE target_table_old;
这个操作本身很快（RENAME是原子的），但要注意：原表target_table_old必须后续手动清理，且触发器、分区定义等不会自动迁移，得额外处理
容易被忽略的坑：事务隔离与长事务残留
即使你用了 DELETE 分批，在 MySQL 5.7 中仍可能卡住：如果存在未提交的长事务（比如应用端开了连接但忘了 COMMIT），InnoDB 的 purge 线程无法清理已删除行的 undo 记录，会导致 ibdata1 或 undo 表空间持续膨胀。所以存储过程里最好加一道检查：
SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_state = 'RUNNING' AND TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 600;
如果结果 > 0，说明有超 10 分钟的活跃事务，此时强行删大表可能让 purge 压力雪上加霜——建议先告警或抛出自定义错误，而不是硬删。
真正棘手的不是语法怎么写，而是你删完之后，undo 文件会不会涨成 20GB 占满磁盘。那不是 SQL 写错了，是事务生命周期管理漏了。
