Automatically set auto_increment for ALL tables in MySQL

Question

Given a database with hundreds or thousands of tables:

Considering that all tables were empty. Is there a way to automatically set auto_increment to 1 for all tables?

You'd need to loop through all the tables (not possible to do this en masse directly in MySQL, AFAIK), and use something like this: https://stackoverflow.com/questions/970597/change-auto-increment-starting-number — Piskvor left the building, Mar 19 '15 at 16:45
You can do this by running a command in the shell. You can try something like: https://stackoverflow.com/questions/1912813/truncate-all-tables-in-a-mysql-database-in-one-command . What's more, if all the tables are really empty, you can just truncate all them as the answer from the link without any changing the command. — Frederick Zhang, Mar 19 '15 at 17:00

score 1 · Answer 1 · answered Mar 19 '15 at 16:53

Could try give it a go in PHP? Would have to know all of the table names.

$tableName = array('somename', 'someOtherName', ...);

foreach ($tableName as $key) {
//Do the query
ALTER TABLE $key MODIFY COLUMN id INT auto_increment
}

Pseudo code.. but you get the idea right?

score 1 · Answer 2 · answered Mar 19 '15 at 21:04

a database with hundreds or thousands of tables

That's likely to be a big mistake.

To do the task, write a Stored Procedure that creates a Cursor walking through information_schema.TABLES and building the SQL needed for each table. Then prepare() and execute() it.

If the SQL is not allowed in a Stored Procedure, then simply SELECT the SQL to show it. Then manually copy and past it into the mysql commandline tool.

Automatically set auto_increment for ALL tables in MySQL

2 Answers2