[SOLVED] MySQL moving primary key from varchar to int

MySQL moving primary key from varchar to int

I have three tables in MySQL (innodb) (X, Y and Z). X is a table with more than 10 million rows, and has primary key of Y as foreign key. Similarly, Z is table with more than 30 million rows and has primary key of Y as foreign key.

Now the problem is that primary key of Y is VARCHAR (something like a md5 hash or GUID). I want to move this key to INT (AUTO_INCREMENT). What is a way to achieve this in mysql, without writing a script in any other language?

Additionally, primary key of table Z is also a VARCHAR (md5/GUID). I would like to change that to integer as well. (It's not a foreign key in any table).

Solution

(This may or may not be any better than Ritobroto's suggestion.)

Assuming X links to Y. (Adapt as needed for all the FKs.)

Do something like this for each table.

ALTER TABLE X
DROP FOREIGN KEY ...,    -- We'll add it back later
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,  -- Replacement PK
DROP PRIMARY KEY,        -- Assuming it was `guid`
ADD PRIMARY KEY(id),
ADD INDEX(X_guid),       -- Lost the FK; still (for now) need an index
ADD COLUMN Y_id INT UNSIGNED NOT NULL  -- future FK to Y
;

Get the new ids linked up (to replace the guids). For each link:
```
UPDATE X JOIN Y ON X.Y_guid = Y.guid
SET x.y_id = y.id;
```
(This will take a long time.

Re-establish the FKs. For each table:

ALTER TABLE ...
ADD FOREIGN KEY ...,  -- to tie `id` instead of `guid`
DROP INDEX(X_guid);   -- unless you need it for something else

Practice it on a test machine !!