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%'

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.

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

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.

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

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.