在不影响当前记录值的情况下将自动增量添加到现有列 [英] Add auto increment to existing column without affecting current record values

问题描述

我想将现有的MyISAM1数据库表中的列类型更改为AUTO_INCREMENT.该数据库当前有成千上万的记录，其中列值非常重要.至关重要的是，当前值不受影响.我想从最新的最高值开始递增.

I would like to change the column type to AUTO_INCREMENT within an existing MyISAM1 database table. The databse currently has thousands of records where the column value is very important. It is crucial that the current value is not affected. I want to increment from the latest higheste value.

我在phpmyadmin(由phpmyadmin生成的sql)中尝试了此操作，但出现了错误.

I tried this within phpmyadmin (sql generated by phpmyadmin) and got an error.

ALTER TABLE `myTable` CHANGE `myCol1` `myCol1` INT(11) NOT NULL AUTO_INCREMENT;

我得到的错误是:

 ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '2197' for key 'PRIMARY'

我做了一些重新研究，发现通过添加偏移量可以解决我的问题.我试过了，但是遇到语法错误.

I did some reaserch and found that by adding an offset it should resolve my issue. I tried this but was greeted with a syntax error.

ALTER TABLE `myTable` CHANGE `myCol1` INT(11) NOT NULL AUTO_INCREMENT = 2500

错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT(11) NOT NULL AUTO_INCREMENT = 2500' at line 1 

我也尝试了上面的sql而不声明INT

I have also tried the above sql without declaring INT

简而言之，我需要保留现有记录，但要为第myCol1列添加自动递增功能，其起始编号为2500(当前的最后一项是2498).此列也是主键.

Simply put I need to keep the existing records as they are but add auto increment ability to column myCol1 starting from number 2500 (as currently the last item is 2498). This column is also the Primary Key.

这不是重复的问题，因为我为其他答案找到的解决方案无法解决我的问题.

This is not a duplicate question as the solutions I have found for other answers do not resolve my issue.

推荐答案

您使用了错误的语法，请尝试以下命令-

You are using wrong syntax, try below command-

ALTER TABLE `myTable` CHANGE `myCol1` `myCol1` INT(11) NOT NULL AUTO_INCREMENT, auto_increment=2500;

这篇关于在不影响当前记录值的情况下将自动增量添加到现有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！