Is there a way to run this or similiar AUTO_INCREMENT command for all tables in a database?
ALTER TABLE tablename AUTO_INCREMENT = 1 
- 1I don't think you should edit your question if it is not directly related to the original question. Open a new question.Chris Stryczynski– Chris Stryczynski2017-06-08 08:33:53 +00:00Commented Jun 8, 2017 at 8:33
- @ChrisStryczynski ok I will... thanksPrimoz Rome– Primoz Rome2017-06-08 08:35:04 +00:00Commented Jun 8, 2017 at 8:35
Add a comment |
2 Answers
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; 5 Comments
Primoz Rome
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
Ok my mistake... I was watching wrong schema.
Roberto Sepúlveda Bravo
didn't work for me, are you assuming all tables have an id column?
Gag Baghdasaryan
Great, Thank you ^_^. BTW ``` WHERE a.table_schema = 'SCHEMA_NAME' ``` not
table name
evilReiko
@GagBaghdasaryan fixed the query, thank you, should work properly now
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;" answered Jun 8, 2017 at 8:23
Chris Stryczynski
34.8k60 gold badges210 silver badges334 bronze badges
4 Comments
fancyPants
You might want to add
and table_type = 'BASE TABLE'Primoz Rome
Thank, can you also check my updated question, if that is possible as well?
fancyPants
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 <temporary file> it again. There are multiple ways to do it.
RoMEoMusTDiE
reset meaning remove data and start from scratch? therefore do a bulk truncate