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

Question

Is there a way to run this or similiar AUTO_INCREMENT command for all tables in a database?

ALTER TABLE tablename AUTO_INCREMENT = 1

Answer 1

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;