How to Reset Auto_Increment in MySQL

How to reset AUTO_INCREMENT in MySQL

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 can I reset an MySQL AutoIncrement using a MAX value from another table? on how to dynamically get an acceptable value.

How to reset AUTO_INCREMENT in MySQL for all tables within a DB?

Try this:

SELECT GROUP_CONCAT(`t`.`query` SEPARATOR '; ')
FROM
    (
        SELECT 
            CONCAT('ALTER TABLE `', `a`.`table_name`, '` AUTO_INCREMENT = 1') AS `query`,
            '1' AS `id`
        FROM `information_schema`.`tables` AS `a`
        WHERE `a`.`TABLE_SCHEMA` = 'my_database' # <<< change this to your target database
          #AND `a`.`TABLE_TYPE` = 'BASE TABLE' << optional
          AND `a`.`AUTO_INCREMENT` IS NOT NULL
    ) AS `t`
GROUP BY `t`.`id`

This will generate a query for each table, but will not run the queries, so you will have to run the generated queries yourself.

Result will look like this:

ALTER TABLE `tablename1` AUTO_INCREMENT = 1; ALTER TABLE `tablename2` AUTO_INCREMENT = 1; ALTER TABLE `tablename3` AUTO_INCREMENT = 1;

reset autoincrement primary key sql, mysql

First of all I would leave gap as it is. If you really want to change it you could use:

 ALTER TABLE post AUTO_INCREMENT = 300;
 -- around 700 insterts
 ALTER TABLE post AUTO_INCREMENT = ?; -- where ? is max value of id_post

What is the purpose of re-setting AUTO_INCREMENT in MySQL?

Resetting auto-inc is an uncommon operation. Under normal day to day work, just let it keep incrementing.

I've done reset of auto-inc in MySQL instances used for automated testing. A given set of tables is loaded with data over and over, and deletes its test data afterwards. Resetting the auto-inc may be the best way to make tests repeatable, if they're looking for specific values in the results.

Another scenario is when creating archive tables. Suppose you have a huge table, and you want to empty out the data efficiently (not using DELETE), but you want to archive the data, and you want new data to use id values higher than your old data.

CREATE TABLE mytable_new LIKE mytable;

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME='mytable';

ALTER TABLE mytable_new AUTO_INCREMENT = /* value + 10000 */;

RENAME TABLE mytable TO mytable_archive, mytable_new TO mytable;

The above series of statements allow you to shuffle a new empty table into place atomically, so your app can continue writing to the table by the name it's used to. The auto-inc value you reset in the new table should be a value higher than the max id value in the old table, plus some comfortable gap to avoid overlap during the moments between the statements.

---

Related Topics