我有一个已经有数据的表。我想在表中添加新的列作为主键，主键的值应该从1000开始，并且应该是自动递增的。我试着遵循sql命令
**
ALTER TABLE TABLE_NAME AUTO_INCREMENT = 1000;ALTER TABLE TABLE_NAME ADD COLUMN RowId INT AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST;
ALTER TABLE TABLE_NAME AUTO_INCREMENT = 1000;
ALTER TABLE TABLE_NAME ADD COLUMN RowId INT AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST;
但它仍然显示新列由1的值组成。我希望第一张唱片应该从1000开始，然后继续。
3b6akqbq1#
根据手册(https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html)只需插入1000的值，就可以更改自动递增的数字在自动递增列中插入任何其他值时，该列将被设置为该值，并且序列将被重置，以便下一个自动生成的值从最大的列值开始依次出现
fnx2tebb2#
你可能只想用一句话来表达。
+-------+---------+--------+---------------------+| 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 |
+-------+---------+--------+---------------------+
| 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 |
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 |
2条答案
按热度按时间3b6akqbq1#
根据手册(https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html)只需插入1000的值，就可以更改自动递增的数字
在自动递增列中插入任何其他值时，该列将被设置为该值，并且序列将被重置，以便下一个自动生成的值从最大的列值开始依次出现
fnx2tebb2#
你可能只想用一句话来表达。