分享 XenForo 批量修改用户id和用户名还有更新发帖量

Xiao.T

Xiao.T

管理员
技术员
2008-05-19
6,008
6,531
帖子表和发帖表批量修改用户id.
代码:
UPDATE xf_post
SET user_id = 新id
WHERE username = '要改id的用户名';
UPDATE xf_thread
SET user_id = new_user_id
WHERE username = '要改id的用户名';

代码:
UPDATE xf_post
SET user_id = new_user_id, username = 'new username'
WHERE username = 'old username';
UPDATE xf_thread
SET user_id = new_user_id, username = 'new username'
WHERE username = 'old username';


更新所有用户发帖量
This will recalculate and update the post counts for all users at once. This is a large query that may take a while to finish.
代码:
UPDATE xf_user AS user
SET message_count = ( SELECT COUNT(*) FROM xf_post AS post LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id) WHERE post.user_id = user.user_id AND post.message_state = 'visible' AND thread.discussion_state = 'visible' GROUP BY post.user_id
);
If you have forums you wish to exclude from the count then use this query, where 1,2,3 is a comma-list of node_ids that are excluded from the count:
代码:
UPDATE xf_user AS user
SET message_count = ( SELECT COUNT(*) FROM xf_post AS post LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id) WHERE post.user_id = user.user_id AND thread.node_id NOT IN (1,2,3) AND post.message_state = 'visible' AND thread.discussion_state = 'visible' GROUP BY post.user_id
);
Backup first.

Because this query is so large you may encounter errors relating to these MySQL settings:

wait_timeout
innodb_lock_wait_timeout

You need to ask your host or server person to increase these settings if you encounter such errors.
 

ZIY 会员微信群