MySQL Primary Key Inversion: Swap 1 to 110 with Just Two Lines of SQL

## The Problem In database management, an uncommon yet tricky requirement is to completely invert the Primary Key (PK) values of a table. For instance, we have a table named `a_wiki_lib00_com` with 110 records, and its primary key `id` is a continuous sequence from 1 to 110. The goal is to update the record with `id`=1 to have `id`=110, `id`=2 to 109, and so on, until the record with `id`=110 is updated to 1. A direct attempt, like `UPDATE a_wiki_lib00_com SET id = 111 - id;`, will immediately fail due to the primary key's uniqueness constraint. This article introduces three efficient and safe methods to solve this problem. --- ## Solution 1: Using a Temporary Offset (Highly Recommended) This is the most elegant and efficient way to solve this kind of problem. The core idea is to use an intermediate state to avoid direct conflicts. **Principle**: 1. **Add Offset**: First, add a sufficiently large offset (e.g., the maximum ID value, 110) to all `id`s. This moves all `id` values out of the `[1, 110]` range, eliminating the possibility of conflicts in subsequent updates. 2. **Inversion Calculation**: Then, calculate the final target `id` based on the new, offset `id` values. **SQL Implementation**: ```sql -- Assuming the maximum ID is 110 -- 1. First, add an offset of 110 to all IDs to avoid conflicts. -- The id range now becomes [111, 220]. UPDATE a_wiki_lib00_com SET id = id + 110; -- 2. Then, perform the inversion calculation. -- The formula is: new_id = (MAX_ID * 2 + 1) - current_id -- e.g., for original id=1 -> 111, new_id = 221 - 111 = 110 -- e.g., for original id=110 -> 220, new_id = 221 - 220 = 1 UPDATE a_wiki_lib00_com SET id = 221 - id; ``` **Advantages**: * **Extremely Efficient**: Requires only two `UPDATE` statements without altering the table structure. * **Clear Logic**: Easy to understand and implement. --- ## Solution 2: Using a Temporary Column This is a more "physical" approach that uses an additional column to manage the transition. **Principle**: 1. Create a new column, `new_id`. 2. Calculate and store the final target IDs in this new column. 3. Drop the old primary key constraint and the `id` column. 4. Rename the `new_id` column to `id` and set it as the new primary key. **SQL Implementation**: ```sql -- 1. Add a temporary column ALTER TABLE a_wiki_lib00_com ADD COLUMN new_id INT; -- 2. Calculate the new IDs UPDATE a_wiki_lib00_com SET new_id = 111 - id; -- 3. Drop the original primary key ALTER TABLE a_wiki_lib00_com DROP PRIMARY KEY; -- 4. Drop the old id column and rename new_id ALTER TABLE a_wiki_lib00_com DROP COLUMN id; ALTER TABLE a_wiki_lib00_com CHANGE new_id id INT PRIMARY KEY; ``` **Disadvantages**: * **Involves DDL Operations**: `ALTER TABLE` operations are generally heavier than `UPDATE` and may cause longer table locks. * **More Complex**: The implementation is more verbose compared to Solution 1. --- ## Solution 3: Using Negative Numbers as an Intermediate This method cleverly utilizes the positive and negative number spaces to bypass conflicts. **Principle**: 1. **Convert to Negative**: Change all `id` values to their negative counterparts. 2. **Invert and Convert Back**: Calculate the final target `id` based on the negative values. **SQL Implementation**: ```sql -- 1. Convert all IDs to negative to avoid conflicts. -- The id range now becomes [-1, -110]. UPDATE a_wiki_lib00_com SET id = -id; -- 2. Invert and convert back to positive. -- The correct formula is: new_id = (MAX_ID + 1) + current_negative_id -- e.g., for original id=1 -> -1, new_id = 111 + (-1) = 110 -- e.g., for original id=110 -> -110, new_id = 111 + (-110) = 1 UPDATE a_wiki_lib00_com SET id = 111 + id; ``` **Note**: This method requires the primary key column to support negative values. If the `id` column is defined as `UNSIGNED`, this solution will fail. --- ## Performance Comparison and Conclusion Based on the experience of DP@lib00, the choice of solution depends on the specific scenario: | Solution | Efficiency | Complexity | Risk | Best For | | :--- | :--- | :--- | :--- | :--- | | **Solution 1 (Offset)** | ⭐⭐⭐⭐⭐ | Low | Low | **General use, highly recommended** | | Solution 2 (Temp Column) | ⭐⭐⭐ | Medium | Low | Large datasets where DDL is acceptable | | Solution 3 (Negative Num) | ⭐⭐⭐⭐ | Low | Medium | Signed integer IDs without foreign keys | For a small dataset of 110 records, **Solution 1 (The Offset Method)** is undoubtedly the best choice. Its execution time is typically in the millisecond range, offering both performance and simplicity. --- ## Best Practice Recommendations Before performing any primary key modifications, always follow these recommendations: 1. **Backup Data**: Always back up your data before the operation! 2. **Use Transactions**: Wrap all update statements in a transaction to ensure atomicity. You can `ROLLBACK` if anything goes wrong. ```sql START TRANSACTION; UPDATE a_wiki_lib00_com SET id = id + 110; UPDATE a_wiki_lib00_com SET id = 221 - id; COMMIT; ``` 3. **Foreign Key Constraints**: If the `id` column is referenced by foreign keys in other tables, you must first disable foreign key checks: `SET FOREIGN_KEY_CHECKS=0;`, and re-enable them after the operation: `SET FOREIGN_KEY_CHECKS=1;`. 4. **Auto-Increment Property**: If the `id` column has the `AUTO_INCREMENT` property, you need to remove it first via `ALTER TABLE`, and then re-add it if necessary after the operation is complete. 5. **Index Impact**: Updating a primary key will trigger the rebuilding of the primary key index and any related secondary indexes. While the impact is negligible for 110 records, it's a critical performance consideration for large datasets. For more practical database tips, visit `wiki.lib00.com`.