MySQL - 回顾性地添加自动递增ID

Question

我有一张有很多行的表格，但没有id列。我想要做以下操作：

1. 添加一个带有AUTO_INCREMENT属性的PRIMARY KEY作为ID列。
2. 更重要的是，回溯地为所有现有的行添加一个ID，从最旧到最新（有一个'updatetime'列）。

有什么建议吗？

- markelshark

---

只是出于好奇，为什么ID顺序必须与更新时间列匹配？我假设当记录被更新时，更新时间会发生变化。您是否打算在将来更新其内容时也更改记录的ID？ - Alex Jasmin

我只需要按时间顺序排列的ID。 - markelshark

Answer 1

让我们考虑以下例子：

CREATE TABLE your_table (some_value int, updatetime datetime);

INSERT INTO your_table VALUES (100, '2010-08-11 12:09:00');
INSERT INTO your_table VALUES (300, '2010-08-11 12:08:00');
INSERT INTO your_table VALUES (200, '2010-08-11 12:07:00');
INSERT INTO your_table VALUES (400, '2010-08-11 12:06:00');
INSERT INTO your_table VALUES (600, '2010-08-11 12:05:00');
INSERT INTO your_table VALUES (500, '2010-08-11 12:04:00');
INSERT INTO your_table VALUES (800, '2010-08-11 12:03:00');

首先，我们可以添加id列：

ALTER TABLE your_table ADD id int unsigned;

现在表格的样子是这样的：

SELECT * FROM your_table;
+------------+---------------------+------+
| some_value | updatetime          | id   |
+------------+---------------------+------+
|        100 | 2010-08-11 12:09:00 | NULL |
|        300 | 2010-08-11 12:08:00 | NULL |
|        200 | 2010-08-11 12:07:00 | NULL |
|        400 | 2010-08-11 12:06:00 | NULL |
|        600 | 2010-08-11 12:05:00 | NULL |
|        500 | 2010-08-11 12:04:00 | NULL |
|        800 | 2010-08-11 12:03:00 | NULL |
+------------+---------------------+------+
7 rows in set (0.00 sec)

当结果集按照 updatetime 列排序后，我们可以使用 UPDATE 命令将 id 列更新为行号：

SET @row_number := 0;

UPDATE    your_table
SET       your_table.id = (@row_number := @row_number + 1)
ORDER BY  your_table.updatetime;

现在表格看起来像这样：

SELECT * FROM your_table ORDER BY id;
+------------+---------------------+----+
| some_value | updatetime          | id |
+------------+---------------------+----+
|        800 | 2010-08-11 12:03:00 |  1 |
|        500 | 2010-08-11 12:04:00 |  2 |
|        600 | 2010-08-11 12:05:00 |  3 |
|        400 | 2010-08-11 12:06:00 |  4 |
|        200 | 2010-08-11 12:07:00 |  5 |
|        300 | 2010-08-11 12:08:00 |  6 |
|        100 | 2010-08-11 12:09:00 |  7 |
+------------+---------------------+----+

然后我们可以将 id 列设置为主键，并使其 NOT NULL 和 AUTO_INCREMENT：

ALTER TABLE your_table 
MODIFY id int unsigned NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id);

这是表格的新描述：

DESCRIBE your_table;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| some_value | int(11)          | YES  |     | NULL    |                |
| updatetime | datetime         | YES  |     | NULL    |                |
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+------------+------------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

我们现在可以尝试在表中INSERT一行新记录，以确认AUTO_INCREMENT是否按预期工作：

INSERT INTO your_table (some_value, updatetime)
VALUES (900, '2010-08-11 12:10:00');

SELECT * FROM your_table ORDER BY id;
+------------+---------------------+----+
| some_value | updatetime          | id |
+------------+---------------------+----+
|        800 | 2010-08-11 12:03:00 |  1 |
|        500 | 2010-08-11 12:04:00 |  2 |
|        600 | 2010-08-11 12:05:00 |  3 |
|        400 | 2010-08-11 12:06:00 |  4 |
|        200 | 2010-08-11 12:07:00 |  5 |
|        300 | 2010-08-11 12:08:00 |  6 |
|        100 | 2010-08-11 12:09:00 |  7 |
|        900 | 2010-08-11 12:10:00 |  8 |
+------------+---------------------+----+
8 rows in set (0.00 sec)

我不确定是否有更简单的方法来解决这个问题，但这种方法似乎能够完成工作。

- Daniel Vassallo

---

感谢您提供详细的指导。我还没有尝试过，但会告诉您进展情况。谢谢。 - markelshark

运作完美，Daniel，谢谢你。 - markelshark