CODEWITHSUNDEEP
mysqlalter-table
Haim Evgi
Haim Evgi

Reputation: 125466

search simple way to alter multi tables in one time in mysql

i have a lot of tables that start with some prefix ,

and i want to alter this tables

what is the simple way to do this (instead run over all tables)

i mean something like :

ALTER TABLE  LIKE tablenameprefix% ADD INDEX `NewIndex1` (`field`);

how can i do this ?

thanks

EDIT :

can i do a kind of loop not in stored procedure ? by select the names of tables from

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'tableprefix%'

Upvotes: 4

Views: 3992

Answers (5)

Zhang Buzz
Zhang Buzz

Reputation: 11068

I made a stupid mistake of putting all Wordpress tables with my product tables, fortunately all Wordpress tables start with a wp_ prefix and all my other product tables have no this wp_ prefix.

I created another database named wordpress, now I want to move all tables start with wp_ to that database.

Here is what I did:

SELECT CONCAT('ALTER TABLE olddb.', table_name, ' RENAME wordpress.', table_name, ';')
  FROM information_schema.tables
  WHERE table_schema='olddb' AND table_name LIKE 'wp%'
  INTO OUTFILE '/tmp/move_to_wordpress';
SOURCE /tmp/move_to_wordpress;

That's it.

Upvotes: 0

gnupa
gnupa

Reputation: 97

When I wanted to change multiple table's engine from MyISAM to InnoDB, instead of writing a loop I just made a full DB dump and opened it in a text editor. In the text editor I just simply changed all MyISAM words to InnoDB.

I know that this ain't proper solution but for me it was easier then writing a routine for this.

Upvotes: 2

Anax
Anax

Reputation: 9372

Given that ALTER TABLE syntax doesn't allow multiple table names, you cannot do this. You need to go through all tables in turn:

ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification]

Link: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

Upvotes: 2

Dennis Haarbrink
Dennis Haarbrink

Reputation: 3760

You would have to write a loop, according to the documentation you just specify the table name.

Upvotes: 1

Adrian Grigore
Adrian Grigore

Reputation: 33318

You can't. What you could do though is write a stored procedure that enumerates all tables looking for your prefix and performs the necessary changes.

Upvotes: 3

Related Questions