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?

Answer 1

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