如何更新主键列，为所有ID值添加固定前缀并保留原内容？

Hey there! Let's work through this problem together. You want to add a fixed prefix like IN to your existing numeric primary keys (1-4 digit IDs) while keeping the original number intact—turning 100 into IN100, for example. Here's how to do this across common databases, plus some critical pre-work notes:

First, Critical Prep Steps #

• Backup your data first! Modifying primary keys is a sensitive operation—you don't want to lose or corrupt data if something goes wrong.
• Check for foreign key references: If this primary key is used as a foreign key in other tables, you’ll need to either temporarily disable those foreign key constraints or update the foreign key values to match the new format after you modify the primary key. Otherwise, you’ll hit constraint errors.

Step-by-Step for Common Databases #

MySQL/MariaDB #

1. Change the primary key column type from a numeric type (like INT) to a string type that can hold the prefix plus your longest ID (e.g., VARCHAR(6) for IN + 4 digits):

ALTER TABLE your_table_name MODIFY COLUMN id VARCHAR(6) PRIMARY KEY;

2. Update all existing records to prepend the prefix:

UPDATE your_table_name SET id = CONCAT('IN', id);

SQL Server #

1. Modify the column type (ensure it’s set to NOT NULL since primary keys require non-null values):

ALTER TABLE your_table_name ALTER COLUMN id VARCHAR(6) NOT NULL;
-- Reapply the primary key constraint if it gets dropped during the type change
ALTER TABLE your_table_name ADD CONSTRAINT PK_your_table_id PRIMARY KEY (id);

2. Update records by converting the numeric ID to a string and concatenating the prefix:

UPDATE your_table_name SET id = 'IN' + CAST(id AS VARCHAR(4));

PostgreSQL #

1. Change the column data type to a string:

ALTER TABLE your_table_name ALTER COLUMN id TYPE VARCHAR(6);

2. Update all rows using PostgreSQL’s string concatenation operator ||:

UPDATE your_table_name SET id = 'IN' || id::VARCHAR;

Bonus: Automate Prefixes for New Records #

If you want future primary keys to automatically get the IN prefix, you can use a trigger. Here’s a quick example for MySQL:

DELIMITER //
CREATE TRIGGER before_insert_your_table
BEFORE INSERT ON your_table_name
FOR EACH ROW
BEGIN
    -- Get the highest existing numeric suffix, add 1, then prepend IN
    SET NEW.id = CONCAT('IN', (SELECT COALESCE(MAX(CAST(SUBSTRING(id, 3) AS UNSIGNED)), 0) + 1 FROM your_table_name));
END //
DELIMITER ;

Just remember to test all of this in a non-production environment first to iron out any kinks!

内容的提问来源于stack exchange，提问作者BradenA8