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.