數據表如何讓主鍵從1開始重新排列而不影響其他內容

研究了一上午，最後找到了解決辦法。

一個主表一個副表，想要一次性將兩個表的主鍵給重新排列。

本來說靠PHP來遍歷處理，結果離譜，太慢了，於是直接上SQL代碼處理！

SQL代碼：

#創建同結構的item_instance_new
CREATE TABLE item_instance_new LIKE item_instance;
#設置自增ID：
ALTER TABLE `item_instance_new` CHANGE `guid` `guid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
#創建一個備份字段
ALTER TABLE `item_instance_new` ADD `guid_bak_id` INT(10) NOT NULL AFTER `guid`;
#將舊錶字段移動到新表
INSERT INTO item_instance_new (guid_bak_id, itemEntry, owner_guid, creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, reforgeID, transmogrifyId, upgradeID, durability, playedTime, text, pet_species, pet_breed, pet_quality, pet_level, isbot, money, aid)
SELECT guid, itemEntry, owner_guid, creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, reforgeID, transmogrifyId, upgradeID, durability, playedTime, text, pet_species, pet_breed, pet_quality, pet_level, isbot, money, aid
FROM item_instance;
#取消自增ID
ALTER TABLE `item_instance_new` CHANGE `guid` `guid` INT(10) UNSIGNED NOT NULL DEFAULT '0';

#創建同結構的 character_inventory_new
CREATE TABLE character_inventory_new LIKE character_inventory;
#設置自增
ALTER TABLE `character_inventory_new` CHANGE `item` `item` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Item Global Unique Identifier';
#創建一個備份字段
ALTER TABLE `character_inventory_new` ADD `item_bak_id` INT(10) NOT NULL AFTER `item`;
#條件判斷後進行遷移
INSERT INTO character_inventory_new (bag, slot, guid, item, item_bak_id)
SELECT t1.bag, t1.slot, t1.guid, t3.guid, t1.item
FROM character_inventory t1
JOIN item_instance_new t3 ON t1.item = t3.guid_bak_id
WHERE t1.item = t3.guid_bak_id;
#查找多餘的ID數據
SELECT * FROM character_inventory
WHERE item NOT IN (SELECT item_bak_id FROM character_inventory_new);
#多餘是數據進行重新排列
INSERT INTO character_inventory_new (bag, slot, guid, item_bak_id)
SELECT bag, slot, guid, item FROM character_inventory WHERE item NOT IN (SELECT item_bak_id FROM character_inventory_new);
#取消自增ID並還原設置
ALTER TABLE `character_inventory_new` CHANGE `item` `item` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Item Global Unique Identifier';

代碼詳解

這段代碼是一個數據庫遷移腳本，用於創建新的表並將舊錶中的數據遷移到新表中。下面對每個步驟進行解釋：

1. 創建同結構的item_instance_new表：通過CREATE TABLE item_instance_new LIKE item_instance;語句創建一個與item_instance表結構相同的新表item_instance_new。

2. 設置自增ID：通過ALTER TABLE item_instance_newCHANGEguid guid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;語句設置item_instance_new表的guid字段爲自增ID，具體意思是在插入數據時，該字段會自動遞增生成唯一的ID。

3. 創建一個備份字段：通過ALTER TABLE item_instance_newADDguid_bak_idINT(10) NOT NULL AFTERguid;語句在item_instance_new表中添加一個名爲guid_bak_id的字段，用於備份舊錶的guid字段。

4. 將舊錶字段移動到新表：通過INSERT INTO item_instance_new (guid_bak_id, itemEntry, owner_guid, creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, reforgeID, transmogrifyId, upgradeID, durability, playedTime, text, pet_species, pet_breed, pet_quality, pet_level, isbot, money, aid) SELECT guid, itemEntry, owner_guid, creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, reforgeID, transmogrifyId, upgradeID, durability, playedTime, text, pet_species, pet_breed, pet_quality, pet_level, isbot, money, aid FROM item_instance;語句將item_instance表中的數據插入到item_instance_new表中，同時使用guid字段的值填充guid_bak_id字段。

5. 取消自增ID：通過ALTER TABLE item_instance_newCHANGEguid guid INT(10) UNSIGNED NOT NULL DEFAULT '0';語句取消item_instance_new表的guid字段的自增屬性，並將默認值設置爲0。

6. 創建同結構的character_inventory_new表：通過CREATE TABLE character_inventory_new LIKE character_inventory;語句創建一個與character_inventory表結構相同的新表character_inventory_new。

7. 設置自增：通過ALTER TABLE character_inventory_newCHANGEitem item INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Item Global Unique Identifier';語句設置character_inventory_new表的item字段爲自增ID，並添加註釋說明。

8. 條件判斷後進行遷移：通過INSERT INTO character_inventory_new (bag, slot, guid, item) SELECT t1.bag, t1.slot, t1.guid, t3.guid FROM character_inventory t1 JOIN item_instance_new t3 ON t1.item = t3.guid_bak_id WHERE t1.item = t3.guid_bak_id;語句將character_inventory表中滿足條件的數據遷移到character_inventory_new表中。具體條件是t1.item（character_inventory表中的item字段）等於t3.guid_bak_id（item_instance_new表中的guid_bak_id字段）。

9. 取消自增ID並還原設置：通過ALTER TABLE character_inventory_newCHANGEitem item INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Item Global Unique Identifier';語句取消character_inventory_new表的item字段的自增屬性，並將默認值設置爲0，並添加註釋說明。

這段代碼的作用是將舊錶item_instance和character_inventory的數據遷移到新表item_instance_new和character_inventory_new中，並對新表的某些字段進行設置。