MySQL moving primary key from varchar to int

Question

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).

Answer 1

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

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

1. 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
   ;
   

2. 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.

3. 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 !!

Answer 2

Step 1
Create tables with same column names but the datatype of primary key of Y should be in INT AUTO INCREMENT,Same goes for table Z.

Step 2
Use this query:

INSERT INTO table_name (column_names of present table except the primary key column_name) SELECT all the columns except the primary key column FROM Y/Z(which ever table you want from the data to be inserted).

Then you can drop the original table.
It's a painful process for such amount of data,but will do what you have wanted.