|
   |
| Модератор форума: Dimitro |
| Форум » TrinityCore » Патчи / Моды / Фиксы для Trinity » [SQL] Чистка базы characters |
| [SQL] Чистка базы characters |
Некоторым серверам уже по 3-4 года, без вайпов.
Вот у меня бд весит 40 гигов, решил почистить. Итак чистка работает по следующей выборке SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0 name='' > удаленные персонажи. Не знаю как ну других серверах но они у меня удалются не полностью, чтобы их можно было восстановить. logout_time<1370044800 > персонажи не заходившие с 1 июня 2013го года. totaltime=0 = игровое время равно нулю, а точней создали персонажа и не разу на него не заходили. Можете подставлять свои условия. Код TRUNCATE TABLE corpse; TRUNCATE TABLE group_member; TRUNCATE TABLE group_instance; TRUNCATE TABLE groups; DELETE FROM `item_instance` where owner_guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM `character_achievement` where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM `character_achievement_progress` where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM guild_bank_item where item_guid NOT IN (select guid from item_instance); DELETE FROM character_arena_stats where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_aura where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_banned where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_battleground_data where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_declinedname where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_equipmentsets where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_gifts where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_glyphs where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_homebind where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_instance where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_inventory where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_lifes where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_pet where owner IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_pet_declinedname where owner IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_queststatus where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_queststatus_rewarded where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_queststatus_seasonal where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_queststatus_weekly where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_reputation where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_skills where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_social where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_spell where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_spell_cooldown where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_talent where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM guild_member where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM item_refund_instance where player_guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM lag_reports where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM arena_team where captainGuid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM arena_team_member where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM gm_tickets where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_action where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_account_data where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_queststatus_daily where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_queststatus_monthly where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM `characters` where name='' or logout_time<1370044800 or totaltime=0; |
OZOLIN, Unix time.
|
Сообщение # 4 написано 14.03.2014 в 12:36
|
У себя в бд я удалил 219к чаров, потом в некоторых таблицах было удалено по 15-25 лям записей)
В общем эгегей бд Добавлено (14.03.2014, 12:46) Код [SQL] DELETE FROM `item_instance` where owner_guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); Affected rows: 8052624 Time: 218.917ms [SQL] DELETE FROM `character_achievement` where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); Affected rows: 15444826 Time: 140.307ms [SQL] DELETE FROM character_reputation where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); Affected rows: 19731439 Time: 808.112ms Урурур))
Сообщение # 6 написано 14.03.2014 в 12:46
|
Для себя еще несколько запросов дописал:
Код DELETE FROM arena_team where captainGuid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); -- на случай если персонаж был удалён через базу DELETE FROM arena_team_member where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM gm_tickets where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_action where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_account_data where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_queststatus_daily where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); DELETE FROM character_queststatus_monthly where guid IN (SELECT guid FROM `characters` where name='' or logout_time<1370044800 or totaltime=0); |
| |||
| |||