· 5 years ago · Apr 04, 2020, 05:04 PM
1-- Очищаем мусор из mail_items для отсутствующих писем
2DELETE FROM `mail_items` WHERE `mail_id` NOT IN (SELECT `id` FROM `mail`);
3
4-- Удаляем лишний мусор для отсутствующих предметов
5DELETE FROM `auctionhouse` WHERE `itemguid` NOT IN (SELECT `guid` FROM `item_instance`);
6DELETE FROM `character_aura` WHERE `item_guid` != 0 AND `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
7DELETE FROM `character_gifts` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
8DELETE FROM `character_inventory` WHERE `item` NOT IN (SELECT `guid` FROM `item_instance`);
9DELETE FROM `custom_transmogrification` WHERE `GUID` NOT IN (SELECT `guid` FROM `item_instance`);
10DELETE FROM `item_loot_items` WHERE `container_id` NOT IN (SELECT `guid` FROM `item_instance`);
11DELETE FROM `item_loot_money` WHERE `container_id` NOT IN (SELECT `guid` FROM `item_instance`);
12DELETE FROM `item_refund_instance` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
13DELETE FROM `item_soulbound_trade_data` WHERE `itemGuid` NOT IN (SELECT `guid` FROM `item_instance`);
14DELETE FROM `guild_bank_item` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
15DELETE FROM `mail_items` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
16DELETE FROM `petition` WHERE `petitionguid` NOT IN (SELECT `guid` FROM `item_instance`);
17DELETE FROM `petition_sign` WHERE `petitionguid` NOT IN (SELECT `petitionguid` FROM `petition`);
18
19-- Создаем временную структуру
20SET @START_GUID_ITEMS := 1;
21
22DROP TABLE IF EXISTS `tmp_item_instance_table`;
23CREATE TABLE `tmp_item_instance_table` (
24 `guid_new` INT(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
25 `guid` INT(10) unsigned NOT NULL,
26 KEY `idx_guid` (`guid`)
27) ENGINE=INNODB DEFAULT CHARSET=utf8;
28
29SET @s = CONCAT('ALTER TABLE `tmp_item_instance_table` AUTO_INCREMENT = ', @START_GUID_ITEMS);
30PREPARE stmt FROM @s;
31EXECUTE stmt;
32DEALLOCATE PREPARE stmt;
33
34-- Записываем в структуру данные
35INSERT INTO `tmp_item_instance_table` (`guid`) SELECT `guid` FROM `item_instance` ORDER BY `guid` ASC;
36
37-- Перестраиваем гуиды
38UPDATE `auctionhouse` JOIN `tmp_item_instance_table` ON `auctionhouse`.`itemguid` = `tmp_item_instance_table`.`guid` SET `auctionhouse`.`itemguid` = `tmp_item_instance_table`.`guid_new`;
39UPDATE `character_aura` JOIN `tmp_item_instance_table` ON `character_aura`.`item_guid` = `tmp_item_instance_table`.`guid` SET `character_aura`.`item_guid` = `tmp_item_instance_table`.`guid_new`;
40UPDATE `character_gifts` JOIN `tmp_item_instance_table` ON `character_gifts`.`item_guid` = `tmp_item_instance_table`.`guid` SET `character_gifts`.`item_guid` = `tmp_item_instance_table`.`guid_new`;
41UPDATE `character_inventory` JOIN `tmp_item_instance_table` ON `character_inventory`.`item` = `tmp_item_instance_table`.`guid` SET `character_inventory`.`item` = `tmp_item_instance_table`.`guid_new`;
42UPDATE `character_inventory` JOIN `tmp_item_instance_table` ON `character_inventory`.`bag` = `tmp_item_instance_table`.`guid` SET `character_inventory`.`bag` = `tmp_item_instance_table`.`guid_new`;
43UPDATE `custom_transmogrification` JOIN `tmp_item_instance_table` ON `custom_transmogrification`.`GUID` = `tmp_item_instance_table`.`guid` SET `custom_transmogrification`.`GUID` = `tmp_item_instance_table`.`guid_new`;
44UPDATE `item_instance` JOIN `tmp_item_instance_table` ON `item_instance`.`guid` = `tmp_item_instance_table`.`guid` SET `item_instance`.`guid` = `tmp_item_instance_table`.`guid_new`;
45UPDATE `item_loot_items` JOIN `tmp_item_instance_table` ON `item_loot_items`.`container_id` = `tmp_item_instance_table`.`guid` SET `item_loot_items`.`container_id` = `tmp_item_instance_table`.`guid_new`;
46UPDATE `item_loot_money` JOIN `tmp_item_instance_table` ON `item_loot_money`.`container_id` = `tmp_item_instance_table`.`guid` SET `item_loot_money`.`container_id` = `tmp_item_instance_table`.`guid_new`;
47UPDATE `item_refund_instance` JOIN `tmp_item_instance_table` ON `item_refund_instance`.`item_guid` = `tmp_item_instance_table`.`guid` SET `item_refund_instance`.`item_guid` = `tmp_item_instance_table`.`guid_new`;
48UPDATE `item_soulbound_trade_data` JOIN `tmp_item_instance_table` ON `item_soulbound_trade_data`.`itemGuid` = `tmp_item_instance_table`.`guid` SET `item_soulbound_trade_data`.`itemGuid` = `tmp_item_instance_table`.`guid_new`;
49UPDATE `guild_bank_item` JOIN `tmp_item_instance_table` ON `guild_bank_item`.`item_guid` = `tmp_item_instance_table`.`guid` SET `guild_bank_item`.`item_guid` = `tmp_item_instance_table`.`guid_new`;
50UPDATE `mail_items` JOIN `tmp_item_instance_table` ON `mail_items`.`item_guid` = `tmp_item_instance_table`.`guid` SET `mail_items`.`item_guid` = `tmp_item_instance_table`.`guid_new`;
51UPDATE `petition` JOIN `tmp_item_instance_table` ON `petition`.`petitionguid` = `tmp_item_instance_table`.`guid` SET `petition`.`petitionguid` = `tmp_item_instance_table`.`guid_new`;
52UPDATE `petition_sign` JOIN `tmp_item_instance_table` ON `petition_sign`.`petitionguid` = `tmp_item_instance_table`.`guid` SET `petition_sign`.`petitionguid` = `tmp_item_instance_table`.`guid_new`;
53
54-- Перестраиваем гуиды Эквипмента
55UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item0` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item0` = `tmp_item_instance_table`.`guid_new`;
56UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item1` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item1` = `tmp_item_instance_table`.`guid_new`;
57UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item2` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item2` = `tmp_item_instance_table`.`guid_new`;
58UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item3` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item3` = `tmp_item_instance_table`.`guid_new`;
59UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item4` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item4` = `tmp_item_instance_table`.`guid_new`;
60UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item5` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item5` = `tmp_item_instance_table`.`guid_new`;
61UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item6` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item6` = `tmp_item_instance_table`.`guid_new`;
62UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item7` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item7` = `tmp_item_instance_table`.`guid_new`;
63UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item8` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item8` = `tmp_item_instance_table`.`guid_new`;
64UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item9` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item9` = `tmp_item_instance_table`.`guid_new`;
65UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item10` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item10` = `tmp_item_instance_table`.`guid_new`;
66UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item11` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item11` = `tmp_item_instance_table`.`guid_new`;
67UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item12` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item12` = `tmp_item_instance_table`.`guid_new`;
68UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item13` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item13` = `tmp_item_instance_table`.`guid_new`;
69UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item14` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item14` = `tmp_item_instance_table`.`guid_new`;
70UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item15` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item15` = `tmp_item_instance_table`.`guid_new`;
71UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item16` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item16` = `tmp_item_instance_table`.`guid_new`;
72UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item17` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item17` = `tmp_item_instance_table`.`guid_new`;
73UPDATE `character_equipmentsets` JOIN `tmp_item_instance_table` ON `character_equipmentsets`.`item18` = `tmp_item_instance_table`.`guid` SET `character_equipmentsets`.`item18` = `tmp_item_instance_table`.`guid_new`;