How can I reset the AUTO_INCREMENT of a field? I want it to start counting from 1 again.

  • 8
    You may get a better response on the site for DBAs at dba.stackexchange.com – Rowland Shaw Jan 19 '12 at 8:40
  • 69
    You'll probably want to empty the table too: TRUNCATE TABLE yourTableName; – Konerak Jan 19 '12 at 8:41
  • 3
    possible duplicate of MySQL: Reorder/Reset auto increment primary key? – outis Jan 20 '12 at 7:09
  • 2
    yes, truncate table is best in this case. it also reset auto increment start to 1. – raidsan Jun 3 '13 at 10:07
  • 2
    Sometimes you don't have permission to TRUNCATE as that requires DROP permissions. – Luke Cousins Nov 14 '14 at 10:23

23 Answers 23


You can reset the counter with:


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.

  • 8
    This can take forever for a filled up table. Be careful with this: stackoverflow.com/questions/2681869/… – B T Aug 3 '12 at 21:11
  • 52
    this is a circular reference – Besnik Feb 21 '13 at 11:00
  • 4
    What do you mean by curcular reference? – Niels Feb 21 '13 at 11:25
  • 67
    Bear in mind that MySql will create a new table, with the same structure and new auto_increment value and copy all records from the original table, drop the original and rename the new one. This could have a considerable performance impact (and disk space) on production environments if the table is large. – Rostol Sep 9 '13 at 20:18
  • 5
    Does this highly voted answer really work if you already have an AUTOINCREMENT column? According to the MySQL 5.1 docs: "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." Sounds like nothing will happen here if the autoincrement is greater than 1. – lreeder Dec 9 '13 at 18:31
  • 5
    May I know what is the difference between your answer and the previous answer – Praveen Srinivasan Apr 6 '15 at 4:41
  • 24
    @PraveenSrinivasan no difference, we added them at the same time – boobiq Apr 6 '15 at 10:31
  • 54
    Personally, I think it is a beautiful thing that these two answers for an AUTO_INCREMENT question had a concurrency collision :-) – Mark Goldfain Mar 12 '17 at 0:28
  • @MarkGoldfain yes it is! – ncomputers Jun 9 '17 at 19:08
  • 3
    one vote for the underdawg!!!! – agfa555 Nov 9 '17 at 3:27
SET  @num := 0;

UPDATE your_table SET id = @num := (@num+1);


I think this will do it

  • 2
    This question already has an appropriate answer. Further, your UPDATE will update all values in id column. – Kermit May 4 '14 at 16:24
  • 1
    The OP doesn't state they are re-ordering existing rows. – Kermit May 4 '14 at 20:13
  • Complete and perfect answer. But i have a doubt that when we inserting a new value what will be the PK of that value. Whether it is 1 or the next PK? – Prifulnath May 4 '17 at 11:22
  • yeah this is the best solution, but you might want to lock tables if there is data being writen eg production tables – wavvves May 9 '18 at 14:58
  • very helpfull, i had the same requirment where i had to reset the auto-increment to start with 1, only first two commands helped. – LOKENDRA Jun 22 '18 at 4:17

Simply like this:


reference: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

  • 4
    reset means to set it to the default value ... so you can replace "value" by 1 – Besnik Feb 21 '13 at 11:02

enter image description hereThere is a very easy way with phpmyadmin under the "operations" tab, you can set, in the table options, autoincrement to the number you want.

  • You need to restart the mysql service / server right after that. – Cyber Jul 5 '17 at 9:33
  • 1
    I don't think you need to restart your server after that. PhpAdmin will just run the command to change the increment start point. – Kareem Aug 29 '17 at 12:16
  • 2
    This doesnt work for innodb engine – Karan Ahuja Jul 13 '18 at 11:28
  • You don't need to restart, it takes effect from the next insert. – IlludiumPu36 Apr 26 at 8:41

The best solution that worked for me:


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.

  • 1
    it will set NULL auto-inc value on information_schema; check SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'my_table' AND table_schema = DATABASE( ) ; – K-Gun Dec 5 '15 at 0:42
  • 1
    I tested on version 5.6.x and this worked perfectly, and the query by Kerem Güneş returned the max value plus one, NOT null as stated. (maybe it is set to null after the first query, but it is not after the second query) I believe this is the best solution for InnoDB. – Frank Forte Jan 4 '16 at 22:30
  • 1
    works great on innodb.- thank you! – Gerfried Jan 26 '16 at 9:06
  • Same as set explicit value: "create a new table, with the same structure and new auto_increment value and copy all records from the original table, drop the original and rename the new one. This could have a considerable performance impact (and disk space) on production environments if the table is large." but much worse because need full copy table two times. – Enyby Aug 12 '16 at 18:15

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

  • 8
    Do you how to do this with InnoDB? – EmptyArsenal Dec 13 '13 at 23:49
  • 5
    It does work on InnoDB, at least on MySQL 5.1.73. – Eduardo Sep 26 '14 at 14:21
  • 4
    Works fine for me using MySQL v 5.6.2 – Eddie B Sep 27 '14 at 20:32
  • I had a similar issue where an autoincrement field wasn't getting reset after I deleted records from a table. TRUNCATE appears to have worked. I think the solutions below for removing autoincrement and then re-applying may help alleviate this. – Craig Maloney Jan 7 '15 at 21:38
  • @CraigMaloney - DELETE (or ROLLBACK) will not recover ids. One exception: After a restart (or crash), the next id is computed to be MAX(id)+1, thereby effectively recovering deleted ids on the end. Exception to the exception: MySQL 8.0 leaves those ids unused. – Rick James May 1 at 17:51

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.


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:


The docs are updated to reflect this:


My testing also shows that the table is NOT copied, the value is simply changed.

ALTER TABLE news_feed DROP 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 !!!.

  • 2
    I'd like to have something like this! But as an extra warning; this would obviously completely break your foreign keys. – NoobishPro Jul 14 '16 at 23:31

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!!

  • 11
    New programmers, be aware that TRUNCATE will also delete ALL of your rows in specified table! – Zanshin13 Aug 5 '15 at 8:50
  • 1
    The above comment should be inserted in the answer, or at least an explanation about the actual use of truncate. – Lucas Kauz Aug 13 '15 at 2:48
  • TRUNCATE will reset the auto_increment fields too? My use case is to clear out the old data in table and start the IDs from 1 again for new data (imagine, clearing table for proper use after testing is complete). I thought I'd have to DROP the whole table and CREATE it again. – ADTC Nov 24 '16 at 9:31
  • Yes, TRUNCATE works like a reset on the table. That's not entirely true if a table has been ALTERED since creating it then those changes will not be reset. I suppose its closer to exporting the table structure and and using the export to create a new table than an actual reset. – Chris Jun 19 '17 at 7:31

it is for empty table:


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`);

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.


This works well!


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.

  • could you explain how you made that happen? – MZaragoza Dec 22 '17 at 0:17
  • @MZaragoza Simply drop the old column that has no AI then create a new one that has AI enabled. – andromeda Dec 23 '17 at 8:55
  • Can you update your answer to show this. I would really appreciate it – MZaragoza Dec 23 '17 at 13:25

You can simply truncate the table to reset the sequence

  • 1
    better show by code how to truncate the table . – Rohit Poudel Jul 20 '17 at 5:27

The auto increment counter for a table can be (re)set in two ways:

  1. By executing a query, like others already explained:

    ALTER TABLE <table_name> AUTO_INCREMENT=<table_id>;

  2. 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: enter image description here

    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.


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;


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


To update latest plus one id

 (SELECT (id+1) id FROM table_name order by id desc limit 1);



I googled and found this question, but the answer I am really looking for fulfils two criteria:

  1. using purely MySQL queries
  2. 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:

  1. the table in question is InnoDB
  2. the table uses the field id with type as int as primary key
  3. the only way to do this purely in MySQL is to use stored procedure
  4. my images below are using SequelPro as the GUI. You should be able to adapt it based on your preferred MySQL editor
  5. 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:

CREATE PROCEDURE reset_autoincrement(IN tablename varchar(200))

      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;

      set @alter_statement = concat('ALTER TABLE ', tablename, ' AUTO_INCREMENT = ', @next_inc, ';');
      PREPARE stmt FROM @alter_statement;
      EXECUTE stmt;
END //

Then run it.

Before run, it looks like this when you look under Stored Procedures in your database.

enter image description here

When I run, I simply select the stored procedure and press Run Selection

enter image description here

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

enter image description here

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


I suggest you to go to Query Browser and do the following:

  1. Go to schemata and find the table you want to alter.
  2. Right click and select copy create statement.
  3. Open a result tab and paste the create statement their.
  4. 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.)
  5. Replace N with 1.
  6. 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.

Hope this help!


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

  • not the best way at all, dropping a column will lose the column data – Disha Goyal Apr 9 at 7:43

protected by Kermit May 4 '14 at 16:23

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.