How to reset Mysql auto-increment?

Asked

Viewed 9,183 times

2

Guys, how do I reset the auto increment of Phpmyadmin? I tried already 

ALTER TABLE `table_name` AUTO_INCREMENT=1

But it didn’t work

php mysql

  • Any mistakes? what happens?

  • Nothing happens, just don’t reset keeps adding from where you left off

3 answers

5

Remember to delete all records from the table before using the command you tried.

ALTER TABLE tabela AUTO_INCREMENT = 1

You can use the panel PHPMyAdmin, select the table and then the menu Operações, just change in the settings Opções da tabela > AUTO_INCREMENT

  • If you have data in the database it does not reset the auto increment?

  • I need this in order to make php save new data in the id’s that have already been deleted

  • If you have records it will not reset, what you can do is change to the next ID, it will never go back to the ID before the last record

3

Your problem (and you NAY is alone) is a simple TOC due to deleted records. The recommendation is always not to worry about the gaps that Mysql ends up leaving, because the id column was not made to be humanly readable.

In the timeline of a system, when a primary key is deleted, you can cause referential accesses to be automatically broken (The system tells you that record 237 no longer exists). If you fill this space with a different record, you may end up having more disorders than solutions.

The command you are running does exactly the following

SELECT (MAX(id)+1) INTO @var FROM table;
ALTER TABLE `table` AUTO_INCREMENT = @var;

That is, if your database has record 1 and then record 15, the command that searches for max(id) + 1 will return 16, setting its auto increment to 16.

-1

If you have an id field with order so:

1

2

4

use this will not solve as the clutter is in the middle. You can reorder everything with:

ALTER TABLE `bd`.`table` DROP `id`

ALTER TABLE `bd`.`table` ADD `id` INT(255) NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`)

This will remove the id field and make a new one, so the order will return to:

1

2

3

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.