批量删除dz错误帖子的所有关联的表数据都同时删除

admin

批量删除dz错误帖子的所有关联的表数据都同时删除

请了你的数据安全 请备份了 在操作哦
批量删除主帖及关联数据

1. START TRANSACTION;
   
2. 
   
3. -- 1. 提取待删除的tid列表（创建内存临时表）
   
4. CREATE TEMPORARY TABLE tmp_delete_tid
   
5. ENGINE=MEMORY AS
   
6. SELECT DISTINCT t.tid
   
7. FROM pre_forum_post p
   
8. INNER JOIN pre_forum_thread t ON p.tid = t.tid
   
9. WHERE p.message LIKE '%需要查询的内容%';
   
10. 
    
11. -- 2. 删除关联附件
    
12. DELETE a
    
13. FROM pre_forum_attachment a
    
14. INNER JOIN pre_forum_post p ON a.pid = p.pid
    
15. WHERE p.message LIKE '%需要查询的内容%';
    
16. 
    
17. -- 3. 删除帖子
    
18. DELETE FROM pre_forum_post
    
19. WHERE message LIKE '%需要查询的内容%';
    
20. 
    
21. -- 4. 删除主题（级联删除首帖）
    
22. DELETE t
    
23. FROM pre_forum_thread t
    
24. INNER JOIN tmp_delete_tid tmp ON t.tid = tmp.tid;
    
25. 
    
26. -- 5. 更新统计（版块帖子数）
    
27. UPDATE pre_forum_forum f
    
28. SET posts = posts - (
    
29.     SELECT COUNT(*)
    
30.     FROM pre_forum_post p
    
31.     WHERE p.fid = f.fid
    
32.     AND p.message LIKE '%需要查询的内容%'
    
33. );
    
34. 
    
35. -- 6. 清理临时表
    
36. DROP TEMPORARY TABLE tmp_delete_tid;
    
37. 
    
38. COMMIT;

复制代码

需同步清理的表（根据实际需求选择）：

表名

操作类型

SQL示例

pre_forum_thread

DELETEDELETE FROM pre_forum_thread WHERE tid IN (SELECT tid FROM tmp_delete_tid)

pre_forum_attachment

DELETEDELETE FROM ... WHERE pid IN (被删帖子的PID列表)

pre_forum_postcache

DELETEDELETE FROM ... WHERE pid IN (...)

pre_forum_threadmod

DELETEDELETE FROM ... WHERE tid IN (...)

pre_forum_threadhidelog

DELETEDELETE FROM ... WHERE tid IN (...)

pre_forum_rsscache

UPDATEUPDATE ... SET updatetime = ... WHERE tid IN (...)