现在有一个mysql数据库表,需要给主键加上自增。很容易可以找到命令类似于下面这样

alter table `table_name` 
change column `id` 
`id` int(10) unsigned auto_increment not null ;

但是,一直没问题的代码这次失效了,报错如下:

ERROR 1062: ALTER TABLE causes auto_increment resequencing, 
resulting in duplicate entry '1' for key 'PRIMARY'

stackoverflow的这个问答的第一表示它也不知道为啥子,只知道可以workaround掉简单说就是放弃原来的id,重新用auto_increment做个 -- 但是这有毛用,关联数据库的PK都换掉了还整个蛋蛋

问答第二的说:

This will happen if the table contains an existing record with an id of 0 (or negative). Updating all existing records to use positive values will auto_increment to be set on that column.

所以,只要寻找id为<=0的,修改即可

Categories: Code

Yu

Ideals are like the stars: we never reach them, but like the mariners of the sea, we chart our course by them.

2 Comments

vfhky · November 23, 2013 at 12:19

Google Chrome 30.0.1599.101 Google Chrome 30.0.1599.101 Windows 7 x64 Edition Windows 7 x64 Edition

id还能弄个负数……

    yu · November 24, 2013 at 20:04

    Google Chrome 31.0.1650.57 Google Chrome 31.0.1650.57 Windows 8 x64 Edition Windows 8 x64 Edition

    没有规定不行就是可以.
    如果设置id为 int 类型而非unsigned int,虽然看起来很奇怪,但值设置为负数也没有违反什么规则啊

Leave a Reply

Your email address will not be published.