How to reset AUTO INCREMENT in MySQL

Question

How can I reset the AUTO INCREMENT of a field  I want it to start counting from 1 again

User · Answer

The highest rated answers to this question all recommend "ALTER yourtable AUTO_INCREMENT= value". However, this only works when value in the alter is greater than the current max value of the autoincrement column. According to the MySQL 8 documentation:

You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.

In essence, you can only alter AUTO_INCREMENT to increase the value of the autoincrement column, not reset it to 1, as the OP asks in the second part of the question. For options that actually allow you set the AUTO_INCREMENT downward from its current max, take a look at Reorder / reset auto increment primary key.

User · Answer

I googled and found this question  but the answer I am really looking for fulfils two criteria    using purely MySQL queries reset an existing table auto-increment to max id    1   Since I couldn t find exactly what I want here  I have cobbled the answer from various answers and sharing it here   Few things to note    the table in question is InnoDB the table uses the field id with type as int as primary key the only way to do this purely in MySQL is to use stored procedure my images below are using SequelPro as the GUI  You should be able to adapt it based on your preferred MySQL editor I have tested this on MySQL Ver 14 14 Distrib 5 5 61  for debian-linux-gnu   Step 1  Create Stored Procedure  create a stored procedure like this   DELIMITER    CREATE PROCEDURE reset autoincrement IN tablename varchar 200   BEGIN        SET  get next inc   CONCAT  SELECT  next inc    max id    1 FROM   tablename             PREPARE stmt FROM  get next inc         EXECUTE stmt         SELECT  next inc AS result        DEALLOCATE PREPARE stmt          set  alter statement   concat  ALTER TABLE    tablename    AUTO INCREMENT       next inc              PREPARE stmt FROM  alter statement        EXECUTE stmt        DEALLOCATE PREPARE stmt  END    DELIMITER     Then run it   Before run  it looks like this when you look under Stored Procedures in your database     When I run  I simply select the stored procedure and press Run Selection    Note  the delimiters part are crucial  Hence if you copy and paste from the top selected answers in this question  they tend not to work for this reason   After I run  I should see the stored procedure    If you need to change the stored procedure  you need to delete the stored procedure  then select to run again   Step 2  Call the stored procedure  This time you can simply use normal MySQL queries   call reset autoincrement  products      Originally from my own SQL queries notes in https   simkimsia com library sql-queries  mysql-reset-autoinc and adapted for StackOverflow

User · Answer

ALTER TABLE tablename AUTO INCREMENT   1

User · Answer

You can simply truncate the table to reset the sequence  TRUNCATE TABLE TABLE NAME

User · Answer

SET   num    0   UPDATE your table SET id    num      num 1    ALTER TABLE your table AUTO INCREMENT  1    I think this will do it

User · Answer

You need to follow the advice from Miles M comment and here is a little code that fixes the range in mysql  Also u need to open up my ini Mysql  and change max execution time 60 to max execution time 6000  for large databases  Dont use  quot ALTER TABLE tablename AUTO INCREMENT   1 quot  it will delete everything in your database   con mysqli connect  dbhost   dbuser   dbpass   database    res mysqli query  con   quot select   FROM data WHERE id LIKE id ORDER BY id ASC quot     count   0    while   row   mysqli fetch array  res              count                 mysqli query  con   quot UPDATE data SET id   quot   count  quot   WHERE id   quot   row  id    quot   quot                             echo  Done reseting id            mysqli close  con

User · Answer

The auto increment counter for a table can be  re set in two ways    By executing a query  like others already explained    ALTER TABLE  lt table name gt  AUTO INCREMENT  lt table id gt   Using Workbench or other visual database design tool  I am gonna show in Workbench how it is done - but it shouldn t be much different in other tool as well  By right click over the desired table and choosing Alter table from the context menu  On the bottom you can see all the available options for altering a table  Choose Options and you will get this form    Then  just set the desired value in the field Auto increment as shown in the image   This will basically execute the query shown in the first option

User · Answer

ALTER TABLE news feed DROP id  ALTER TABLE news feed ADD  id BIGINT  200   NOT NULL AUTO INCREMENT FIRST  ADD PRIMARY KEY  id    I used this in some of my scripts   the id field is droped and then added back with previous settings   all the existent fields within the database table are filled in with new auto increment values   this should also work with InnoDB     Note that all the fields within the table will be recounted and will have other ids

User · Answer

Try Run This Query    ALTER TABLE tablename AUTO INCREMENT   value    Or Try This Query For The Reset Auto Increment   ALTER TABLE  tablename  CHANGE  id   id  INT 10  UNSIGNED NOT NULL    And Set Auto Increment Then Run This Query     ALTER TABLE  tablename  CHANGE  id   id  INT 10  UNSIGNED NOT NULL AUTO INCREMENT

User · Answer

Adding an update because the functionality changed in MySQL 5 6  As of MySQL 5 6 you CAN use the simple ALTER TABLE with InnoDB   ALTER TABLE tablename AUTO INCREMENT   1    The docs are updated to reflect this   http   dev mysql com doc refman 5 6 en alter-table html  My testing also shows that the table is NOT copied  the value is simply changed

User · Answer

Simply like this   ALTER TABLE tablename AUTO INCREMENT   value    reference  http   dev mysql com doc refman 5 1 en alter-table html

User · Answer

it is for empty table   ALTER TABLE  table name  AUTO INCREMENT   1    if you have data but you want to tidy up it  i recommend use this    ALTER TABLE  table name  DROP  auto colmn   ALTER TABLE  table name  ADD   auto colmn  INT   many you want    NOT NULL AUTO INCREMENT FIRST  ADD PRIMARY KEY   auto colmn

User · Answer

I tried to alter the table and set auto increment to 1 but it did not work  I resolved to delete the column name I was incrementing  then create a new column with your preferred name and set that new column to increment from the onset

User · Answer

As of MySQL 5 6 the approach below works faster due to online DDL  note algorithm inplace    alter table tablename auto increment 1  algorithm inplace

User · Answer

To update latest plus one id   ALTER TABLE table name AUTO INCREMENT      SELECT  id 1  id FROM table name order by id desc limit 1

User · Answer

There is a very easy way with phpmyadmin under the  operations  tab  you can set  in the table options  autoincrement to the number you want

User · Answer

There are good options given in How To Reset MySQL Autoincrement Column  Note that ALTER TABLE tablename AUTO INCREMENT   value  does not work for InnoDB

User · Answer

I suggest you to go to Query Browser and do the following   Go to schemata and find the table you want to alter   Right click and select copy create statement   Open a result tab and paste the create statement their   Go to the last line of the create statement and look for the Auto Increment N   Where N is a current number for auto increment field    Replace N with 1   Press ctrl enter    Auto increment should reset to one once you enter new row int the table  I don t know what will happen if you try to add a row where an auto increment field value already exist

User · Answer

Best way is remove the field with AI and add it again with AI  works for all tables

User · Answer

Here is my solution  but I will not advise to do this if your column has constraints or is connected as foreign key to other tables as it would have bad effects or will not even work     First   drop the column  ALTER TABLE tbl name DROP COLUMN column id     Second   recreate the column and set it as FIRST if you want it as the first column I assume   ALTER TABLE tbl access ADD COLUMN  access id  int 10  NOT NULL PRIMARY KEY AUTO INCREMENT FIRST   This works well

User · Answer

You can also use the syntax TRUNCATE table like this   TRUNCATE TABLE table name  BEWARE   TRUNCATE TABLE your table will delete everything in your your table

User · Answer

SET  num    0  UPDATE your table SET id    num      num 1   ALTER TABLE your table AUTO INCREMENT  1

User · Answer

You can reset the counter with   ALTER TABLE tablename AUTO INCREMENT   1   For InnoDB you cannot set the auto increment value lower or equal to the highest current index   quote from ViralPatel       Note that you cannot reset the counter to a value less than or equal   to any that have already been used  For MyISAM  if the value is less   than or equal to the maximum value currently in the AUTO INCREMENT   column  the value is reset to the current maximum plus one  For   InnoDB  if the value is less than the current maximum value in the   column  no error occurs and the current sequence value is not changed    See How to Reset an MySQL AutoIncrement using a MAX value from another table  on how to dynamically get an acceptable value

User · Answer

The best solution that worked for me   ALTER TABLE my table MODIFY COLUMN ID INT 10  UNSIGNED  COMMIT  ALTER TABLE my table MODIFY COLUMN ID INT 10  UNSIGNED AUTO INCREMENT  COMMIT    Its fast  works with innoDB  and I don t need to know the current maximum value  This way the auto increment counter will reset and it will start automatically from the maximum value exists

[mysql] How to reset AUTO_INCREMENT in MySQL?

Examples related to mysql

Examples related to sql

Examples related to auto-increment