批量删除dz错误帖子的所有关联的表数据都同时删除
批量删除dz错误帖子的所有关联的表数据都同时删除请了你的数据安全 请备份了 在操作哦
批量删除主帖及关联数据
START TRANSACTION;
-- 1. 提取待删除的tid列表（创建内存临时表）
CREATE TEMPORARY TABLE tmp_delete_tid
ENGINE=MEMORY AS
SELECT DISTINCT t.tid
FROM pre_forum_post p
INNER JOIN pre_forum_thread t ON p.tid = t.tid
WHERE p.message LIKE '%需要查询的内容%';
-- 2. 删除关联附件
DELETE a
FROM pre_forum_attachment a
INNER JOIN pre_forum_post p ON a.pid = p.pid
WHERE p.message LIKE '%需要查询的内容%';
-- 3. 删除帖子
DELETE FROM pre_forum_post
WHERE message LIKE '%需要查询的内容%';
-- 4. 删除主题（级联删除首帖）
DELETE t
FROM pre_forum_thread t
INNER JOIN tmp_delete_tid tmp ON t.tid = tmp.tid;
-- 5. 更新统计（版块帖子数）
UPDATE pre_forum_forum f
SET posts = posts - (
SELECT COUNT(*)
FROM pre_forum_post p
WHERE p.fid = f.fid
AND p.message LIKE '%需要查询的内容%'
);
-- 6. 清理临时表
DROP TEMPORARY TABLE tmp_delete_tid;
COMMIT;需同步清理的表（根据实际需求选择）：
表名操作类型SQL示例
pre_forum_threadDELETEDELETE FROM pre_forum_thread WHERE tid IN (SELECT tid FROM tmp_delete_tid)
pre_forum_attachmentDELETEDELETE FROM ... WHERE pid IN (被删帖子的PID列表)
pre_forum_postcacheDELETEDELETE FROM ... WHERE pid IN (...)
pre_forum_threadmodDELETEDELETE FROM ... WHERE tid IN (...)
pre_forum_threadhidelogDELETEDELETE FROM ... WHERE tid IN (...)
pre_forum_rsscacheUPDATEUPDATE ... SET updatetime = ... WHERE tid IN (...)
页:
[1]