How To Reset Autoincrement value of Column in MySQL

(287 Views)


It is often the case that a column such as the ID column on a table will auto increment. This simply means that the next insert into the table will have an ID that is one more then the previous one and therefore all ID's will be unique. This is done by Auto_Increment keyword in MySQL.

What is Auto_Increment in MySQL?

Auto_Increment is a keyword in SQL that operates on Number data types. Auto_Increment is used to automatically generate sequential numeric values every time that a new record is inserted into a table for a field defined as Auto_Increment.

MySQL removes the complexity of generating unique Primary Key ID by the user programmatically and provides Auto_Increment keyword feature to generate primary keys. Auto_Increment keyword is used with the INT data type.

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record you enter in the table. To change the AUTO_INCREMENT sequence start value with some another value, you can use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=50;
However, if you delete a row from the table, the table will auto increment as if the row had not been deleted at all. The result is a gap in the sequence numbers. This is normally not that much of an issue, but you may want to reset the auto increment field.

OR

If someone Deletes Row in the middle and then again wants to Reset the Auto_Increment Column value to the next successive number or 1.
Follow any of these steps mentioned below to Reset AUTO_INCREMENT in MySQL :

  1. ALTER Table (Reset Autoincrement to any value directly)

    Alter table command resets the AUTO_INCREMENT value in the table to any value you specify in the syntax.

    ALTER TABLE table_name AUTO_INCREMENT = 1;

    Note:

    You cannot reset the counter to a value less than or equal to any that have already been used.

    1. For MyISAM SQL, if the value is equal to or less than the maximum value in the AUTO_INCREMENT column of the table, the value automatically gets reset to the current maximum value in the column plus one.
    2. For InnoDB SQL, if the value is less than the current maximum value in the column of MYSQL table, no error will occur and the current sequence value will not be changed.
  2. Truncate Table (If you want to delete all rows in the table use)

    Truncate table automatically reset the AUTO_INCREMENT values to 0.

    TRUNCATE TABLE table_name;
    When Truncation command is used, it will reset the AUTO_INCREMENT counter value to 0. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset to 0 by TRUNCATE TABLE command, regardless of whether there is a foreign key constraint in the table or not.

If you want to view the last Auto_increment ID that was generated by MySQL, you can use the LAST_INSERT_ID function in MySQL. Below is the Syntax for that

SELECT LAST_INSERT_ID();

Solution Worked 6 UpvotesUpvote

        

Solution Didn't Worked 2 DownvotesDownvote



Comments

1 comment
  • Amal

    Alter command is safer than Truncate



Search