php - 将所有 id 更改为 uuid

Question

我用 id 作为主键设计了我的 mysql 表。我的表已经填充了数据。现在，我想将我的 id 列更改为 uuid 并将所有填充数据的 id 字段更改为 uuid .我正在考虑用 php 来做这件事。有没有其他选择？

Answer 1

看看下面的演示，我有一个具有以下结构的表 City，我需要在其中添加一个 UUID 列

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是表中的Primary Key，查看数据

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)

为 UUID 值添加另一列

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

检查数据，uuid_id列显示NULL

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)

为 UUID() 值更新您的表

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

再次检查数据，表现在包含列 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)

现在您可以删除 ID 列并将 uuid_id 重命名为 id