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

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

I don't think you should edit your question if it is not directly related to the original question. Open a new question. — Chris Stryczynski, Jun 08 '17 at 08:33

evilReiko · Accepted Answer · 2020-03-12T09:48:31.413

4

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;

edited Mar 12 '20 at 09:48

answered Jun 08 '17 at 08:32

evilReiko

19,501
24
86
102

I think you have an error somewhere in the query as it returns `MySQL returned an empty result set (i.e. zero rows).` – Primoz Rome Jun 08 '17 at 08:55
Ok my mistake... I was watching wrong schema. – Primoz Rome Jun 08 '17 at 08:57
didn't work for me, are you assuming all tables have an id column? – Roberto Sepúlveda Bravo May 27 '18 at 11:28
1

Great, Thank you ^_^. BTW ``` WHERE a.table_schema = 'SCHEMA_NAME' ``` not ```table name``` – Gag Baghdasaryan Mar 06 '20 at 12:08
1

@GagBaghdasaryan fixed the query, thank you, should work properly now – evilReiko Mar 12 '20 at 09:38

Chris Stryczynski · Answer 2 · 2017-06-08T08:32:23.267

1

You can use xargs (replace DB_NAME):

mysql -Nsr -e "SELECT t.table_name FROM INFORMATION_SCHEMA.TABLES t WHERE t.table_schema = 'DB_NAME' AND table_type = 'BASE TABLE'" | xargs -I {} mysql DB_NAME -e "ALTER TABLE {} AUTO_INCREMENT = 1;"

edited Jun 08 '17 at 08:32

answered Jun 08 '17 at 08:23

Chris Stryczynski

30,145
48
175
286

1

You might want to add `and table_type = 'BASE TABLE'` – fancyPants Jun 08 '17 at 08:26
Thank, can you also check my updated question, if that is possible as well? – Primoz Rome Jun 08 '17 at 08:29
Also MySQL does support doing this directly. You can for example do `select concat("alter table ", table_name...`, write this with `into outfile` in a temporary file and `source ` it again. There are multiple ways to do it. – fancyPants Jun 08 '17 at 08:30
reset meaning remove data and start from scratch? therefore do a bulk truncate – RoMEoMusTDiE Jun 08 '17 at 08:38

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

2 Answers2