我有一张有很多行的表格,但没有id列。我想要做以下操作:
- 添加一个带有
AUTO_INCREMENT
属性的PRIMARY KEY
作为ID列。 - 更重要的是,回溯地为所有现有的行添加一个ID,从最旧到最新(有一个'updatetime'列)。
有什么建议吗?
我有一张有很多行的表格,但没有id列。我想要做以下操作:
AUTO_INCREMENT
属性的PRIMARY KEY
作为ID列。有什么建议吗?
让我们考虑以下例子:
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)
我不确定是否有更简单的方法来解决这个问题,但这种方法似乎能够完成工作。