How can the data table rearrange the primary key from 1 without affecting other contents?

After spending the entire morning conducting research, we finally found a solution.

One primary table and one secondary table – you want to rearrange the primary keys for both tables in a single operation.

Originally, we planned to use PHP for looping and processing the data, but it turned out to be ridiculously slow—so we simply switched to using SQL code for the processing!

SQLcode:

#创建同结构的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';

Code Detailed Explanation

This code is a database migration script designed to create new tables and migrate data from old tables to the new ones. The following explains each step:

1. Create an `item_instance_new` table with the same structure: ThroughCREATE TABLE item_instance_new LIKE item_instance;The statement creates a new table, item_instance_new, with the same structure as the item_instance table.

2. Set auto-increment ID: OKALTER TABLE item_instance_newCHANGEguid guid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;Set the `guid` field in the `item_instance_new` table to an auto-incrementing ID; this means that when data is inserted, this field will automatically generate a unique ID.

3. Create a backup field: OKALTER TABLE item_instance_newADDguid_bak_idINT(10) NOT NULL AFTERguid;The statement adds a field named `guid_bak_id` to the `item_instance_new` table, used to back up the `guid` field from the old table.

4. Move fields from the old table to the new table: OKINSERT 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;This statement inserts data from the `item_instance` table into the `item_instance_new` table, while simultaneously populating the `guid_bak_id` field with the value from the `guid` field.

5. Cancel auto-incrementing ID: OKALTER TABLE item_instance_newCHANGEguid guid INT(10) UNSIGNED NOT NULL DEFAULT '0';The statement removes the auto-increment attribute from the `guid` field in the `item_instance_new` table and sets its default value to 0.

6. Create a `character_inventory_new` table with the same structure: OKCREATE TABLE character_inventory_new LIKE character_inventory;The statement creates a new table named `character_inventory_new` with the same structure as the `character_inventory` table.

7. Set Auto-Increment: OKALTER TABLE character_inventory_newCHANGEitem item INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Item Global Unique Identifier';Set the `item` field in the `character_inventory_new` table to an auto-incrementing ID, and add an explanatory comment.

8. Perform migration after conditional judgment: PassINSERT 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;This statement migrates data from the `character_inventory` table that meets the specified conditions to the `character_inventory_new` table. The specific condition is that `t1.item` (the `item` field in the `character_inventory` table) equals `t3.guid_bak_id` (the `guid_bak_id` field in the `item_instance_new` table).

9. Disable auto-incrementing ID and restore settings: OKALTER TABLE character_inventory_newCHANGEitem item INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Item Global Unique Identifier';Remove the auto-increment attribute from the `item` field in the `character_inventory_new` table, set its default value to 0, and add an explanatory comment.

This code is designed to migrate data from the old tables `item_instance` and `character_inventory` to the new tables `item_instance_new` and `character_inventory_new`, and to configure certain fields in these new tables.