change all id to uuid

Question

I designed my mysql table with id as primary key. My table is already populated with data. Now, I would like to change my id column to uuid and change all the populated data's id fields to uuid. I'm thinking of doing this with php. Are there any alternatives?

Yes there are alternatives, but the way this question is asked is [opinion based](http://stackoverflow.com/help/dont-ask), and thus likely to get a satisfactory answer. — Andy, Mar 11 '14 at 12:40
@Andy In this cases what programmers really need is opinions. When you have no idea where to continue from you would really like some suggestions. I dislike this tendency of SO to only fix code mistakes. — Itay Grudev, Mar 11 '14 at 12:44
Your question is too broad, but [here's](http://stackoverflow.com/questions/2040240/php-function-to-generate-v4-uuid/15875555#15875555) a way to generate uuids :) — Ja͢ck, Mar 11 '14 at 13:12

Abdul Manaf · Accepted Answer · 2014-03-11T12:49:03.713

Have a look at the below demo, I have a table City with following structure , I need to add a UUID column in it

mysql> show create table City\G
*************************** 1. row ***************************
       Table: City
Create Table: CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ID is Primary Key in the table, Check the data

mysql> SELECT * FROM City LIMIT 10;
+----+----------------+-------------+---------------+------------+
| ID | Name           | CountryCode | District      | Population |
+----+----------------+-------------+---------------+------------+
|  1 | Kabul          | AFG         | Kabol         |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar      |     237500 |
|  3 | Herat          | AFG         | Herat         |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 |
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 |
|  6 | Rotterdam      | NLD         | Zuid-Holland  |     593321 |
|  7 | Haag           | NLD         | Zuid-Holland  |     440900 |
|  8 | Utrecht        | NLD         | Utrecht       |     234323 |
|  9 | Eindhoven      | NLD         | Noord-Brabant |     201843 |
| 10 | Tilburg        | NLD         | Noord-Brabant |     193238 |
+----+----------------+-------------+---------------+------------+
10 rows in set (0.00 sec)

Add a another column for UUID values

mysql> ALTER TABLE City ADD COLUMN uuid_id CHAR(36);
Query OK, 4079 rows affected (1.70 sec)
Records: 4079  Duplicates: 0  Warnings: 0

Check data , which shows NULL in uuid_id column

mysql> SELECT * FROM City LIMIT 10;
+----+----------------+-------------+---------------+------------+---------+
| ID | Name           | CountryCode | District      | Population | uuid_id |
+----+----------------+-------------+---------------+------------+---------+
|  1 | Kabul          | AFG         | Kabol         |    1780000 | NULL    |
|  2 | Qandahar       | AFG         | Qandahar      |     237500 | NULL    |
|  3 | Herat          | AFG         | Herat         |     186800 | NULL    |
|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 | NULL    |
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 | NULL    |
|  6 | Rotterdam      | NLD         | Zuid-Holland  |     593321 | NULL    |
|  7 | Haag           | NLD         | Zuid-Holland  |     440900 | NULL    |
|  8 | Utrecht        | NLD         | Utrecht       |     234323 | NULL    |
|  9 | Eindhoven      | NLD         | Noord-Brabant |     201843 | NULL    |
| 10 | Tilburg        | NLD         | Noord-Brabant |     193238 | NULL    |
+----+----------------+-------------+---------------+------------+---------+
10 rows in set (0.00 sec)

Update Your table for UUID() values

mysql> UPDATE City SET uuid_id = UUID();
Query OK, 4079 rows affected (1.34 sec)
Rows matched: 4079  Changed: 4079  Warnings: 0

Check data Again, Table now contains values for column uuid_id

mysql> SELECT * FROM City LIMIT 10;
+----+----------------+-------------+---------------+------------+--------------------------------------+
| ID | Name           | CountryCode | District      | Population | uuid_id                              |
+----+----------------+-------------+---------------+------------+--------------------------------------+
|  1 | Kabul          | AFG         | Kabol         |    1780000 | 91301a65-a91a-11e3-b0c9-001cc0e52f34 |
|  2 | Qandahar       | AFG         | Qandahar      |     237500 | 9131afaf-a91a-11e3-b0c9-001cc0e52f34 |
|  3 | Herat          | AFG         | Herat         |     186800 | 9131b1f8-a91a-11e3-b0c9-001cc0e52f34 |
|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 | 9131b37b-a91a-11e3-b0c9-001cc0e52f34 |
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 | 9131b4f8-a91a-11e3-b0c9-001cc0e52f34 |
|  6 | Rotterdam      | NLD         | Zuid-Holland  |     593321 | 9131b65f-a91a-11e3-b0c9-001cc0e52f34 |
|  7 | Haag           | NLD         | Zuid-Holland  |     440900 | 9131b7cb-a91a-11e3-b0c9-001cc0e52f34 |
|  8 | Utrecht        | NLD         | Utrecht       |     234323 | 9131b92d-a91a-11e3-b0c9-001cc0e52f34 |
|  9 | Eindhoven      | NLD         | Noord-Brabant |     201843 | 9131ba88-a91a-11e3-b0c9-001cc0e52f34 |
| 10 | Tilburg        | NLD         | Noord-Brabant |     193238 | 9131bfdd-a91a-11e3-b0c9-001cc0e52f34 |
+----+----------------+-------------+---------------+------------+--------------------------------------+
10 rows in set (0.00 sec)

Now you can drop ID column and rename uuid_id to id

FWIW: Your post is difficult to read ~ perhaps outline all of the steps in the very beginning. This also doesn't take into consideration foreign keys / relationships — AgRizzo, Mar 11 '14 at 12:45
I got it. Thank you manaf. I would like to vote up to your answer but it says o need at least 15 reputation. — Fazal Rasel, Mar 11 '14 at 12:52
Well @AbdulManaf bro, Now I have plenty of reputation to up vote your answer and I did... — Fazal Rasel, Apr 23 '14 at 08:48
This answer is 100% INCOMPLETE. You have to handle primary key setting, foreign key relations, constraints, possibly composite keys (in some cases). Migrating a DB to use UUID can be a big task depending on the tables, and you generally need to add all the UUID Columns across the DB first, then go back and do Foreign keys in dependency tree order. here is an example: https://www.kostolansky.sk/posts/how-to-migrate-to-uuid/ — Decoded, Aug 04 '23 at 15:09

change all id to uuid

1 Answers1