BIOS,Windows, Linux, Software,Security, Domains, Web Hosting

How to reset AUTO_INCREMENT column in MySQL

If you delete a record which has AUTO_INCREMENT attribute in MySQL, the sequence ID will be reserved and never be reused again. but we can change this by ALTER command.

1. How to alter AUTO_INCREMENT to a new value

ALTER TABLE foo AUTO_INCREMENT = newvalue

"foo" is your tablename, "newvalue" should be repaced by your proper number(1,2,3,...).

For innoDB, the newvalue should not be smaller than the current index number. The maxmium sequence number depends on the data type of the current column.

2. How to cancel AUTO_INCREMENT attribute

Cancel AUTO_INCREMENT:

ALTER TABLE `foo` CHANGE `Id` `Id` INT(10) NOT NULL;

Set AUTO_INCREMENT:

ALTER TABLE `foo` CHANGE `Id` `Id` INT(10) NOT NULL AUTO_INCREMENT;

Reference:

http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html

Related Articles

Comments(0)  

Leave a Reply

Subscribe to BootBeta Comments RSS feed to receive notification of latest comments posted.

Subscriptions

  • Enter Email Address:

Recent Post

Copyright@ 2010-2014 Bootbeta All Rights Reserved.
HooBlog Ver 1.5 Build 20140530