mysql - 如何在mysql现有表中添加自增列，从1000开始递增1

Question

我有一个已经有数据的表。我想在该表中添加新列作为主键，该列应从 1000 开始，并且也应该自动递增。

我尝试了以下sql命令

**

ALTER TABLE TABLE_NAME  AUTO_INCREMENT = 1000;
ALTER TABLE TABLE_NAME ADD COLUMN RowId  INT AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST;

**

但它仍然显示新列包含从 1 开始的值。我希望第一条记录应从 1000 开始并继续依此类推。

Answer 1

您可能只想在一个语句中执行此操作。

+-------+---------+--------+---------------------+
| subid | clickid | status | datetime            |
+-------+---------+--------+---------------------+
|     1 |     123 | low    | 2018-07-24 20:20:44 |
|     2 |     123 | act    | 2018-07-24 21:20:44 |
|     3 |     231 | act    | 2018-07-25 20:20:44 |
|     4 |     231 | low    | 2018-07-25 21:20:44 |
|     5 |     789 | low    | 2018-07-26 20:20:44 |
|     6 |     789 | act    | 2018-07-26 21:20:44 |
+-------+---------+--------+---------------------+
6 rows in set (0.00 sec)

ALTER TABLE T ADD COLUMN RowId  INT AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST,
AUTO_INCREMENT = 1000;

+-------+-------+---------+--------+---------------------+
| RowId | subid | clickid | status | datetime            |
+-------+-------+---------+--------+---------------------+
|  1000 |     1 |     123 | low    | 2018-07-24 20:20:44 |
|  1001 |     2 |     123 | act    | 2018-07-24 21:20:44 |
|  1002 |     3 |     231 | act    | 2018-07-25 20:20:44 |
|  1003 |     4 |     231 | low    | 2018-07-25 21:20:44 |
|  1004 |     5 |     789 | low    | 2018-07-26 20:20:44 |
|  1005 |     6 |     789 | act    | 2018-07-26 21:20:44 |
+-------+-------+---------+--------+---------------------+
6 rows in set (0.00 sec)

show create table t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `RowId` int(11) NOT NULL AUTO_INCREMENT,
  `subid` int(11) DEFAULT NULL,
  `clickid` int(11) DEFAULT NULL,
  `status` varchar(3) DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`RowId`)
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=latin1 |