快速刪除指定使用者在討論區的所有文章

kiang

目前使用的是 cbb 3.08 ，因為有人手動連貼了 15 篇廣告，所以就找了一下。只要找到會員編號，透過資料庫執行下面語法即可：
DELETE FROM xoops_bb_topics WHERE topic_poster = 17059;
DELETE FROM xoops_bb_posts WHERE uid = 17059;
DELETE FROM xoops_bb_reads_forum WHERE uid = 17059;
DELETE FROM xoops_bb_reads_topic WHERE uid = 17059;

17059 就是使用者註冊的編號，執行完成後看是要停用該使用者還是刪除。

原始討論： http://twpug.net/x/modules/newbb/viewtopic.php?topic_id=5649

kiang

加碼刪除了這些人：

DELETE FROM xoops_groups_users_link WHERE uid IN (
SELECT uid FROM xoops_users WHERE user_sig LIKE '%[url%' AND bio LIKE '%[url%'
);

DELETE FROM xoops_profile_profile WHERE profile_id IN (
SELECT uid FROM xoops_users WHERE user_sig LIKE '%[url%' AND bio LIKE '%[url%'
);

DELETE FROM xoops_users WHERE user_sig LIKE '%[url%' AND bio LIKE '%[url%';

DELETE FROM xoops_groups_users_link WHERE uid IN (
SELECT uid FROM xoops_users WHERE user_sig LIKE '%[url%' AND posts = 0
);

DELETE FROM xoops_profile_profile WHERE profile_id IN (
SELECT uid FROM xoops_users WHERE user_sig LIKE '%[url%' AND posts = 0
);

DELETE FROM xoops_users WHERE user_sig LIKE '%[url%' AND posts = 0;

DELETE FROM xoops_groups_users_link WHERE uid IN (
SELECT uid FROM xoops_users WHERE bio LIKE '%http%' AND posts = 0
);

DELETE FROM xoops_profile_profile WHERE profile_id IN (
SELECT uid FROM xoops_users WHERE bio LIKE '%http%' AND posts = 0
);

DELETE FROM xoops_users WHERE bio LIKE '%http%' AND posts = 0;

kiang

後來發現這些發送廣告的會用線上訊息發，另外是討論區的資料表關聯會因為直接刪除而斷掉，所以改進版本

DELETE FROM xoops_bb_topics WHERE topic_poster = 18507;
DELETE FROM xoops_bb_posts WHERE uid = 18507;
DELETE FROM xoops_bb_reads_forum WHERE uid = 18507;
DELETE FROM xoops_bb_reads_topic WHERE uid = 18507; 
DELETE FROM xoops_priv_msgs WHERE from_userid = 18507;

CREATE TABLE xoops_bb_topics_tmp (`id` int(10) NOT NULL, PRIMARY KEY (`id`));
INSERT INTO xoops_bb_topics_tmp (SELECT T.topic_id
FROM xoops_bb_topics AS T
LEFT JOIN xoops_bb_posts AS P ON P.post_id = T.topic_last_post_id
WHERE P.post_id IS NULL);
UPDATE xoops_bb_topics AS T SET T.topic_last_post_id = (SELECT post_id FROM xoops_bb_posts WHERE topic_id = T.topic_id ORDER BY post_id DESC LIMIT 1)
WHERE T.topic_id IN (SELECT id FROM xoops_bb_topics_tmp);
DELETE FROM xoops_bb_topics WHERE topic_last_post_id = 0;
DROP TABLE xoops_bb_topics_tmp;