Finding an easy way to change multiple tables at once in mysql

Question

I have many tables that start with some prefix,

and I want to change these tables

What is an easy way to do this (go through all the tables instead)

I mean something like:

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

How can i do this?

thank

EDIT:

Is it possible to make some kind of loop not in a stored procedure? select table names from

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

Answer 1

You can not. However, you can write a stored procedure that lists all the tables that search for your prefix and makes the necessary changes.

Answer 2

, ALTER TABLE , . :

ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification]

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

Answer 3

MyISAM InnoDB, DB . MyISAM InnoDB.

, , .

Answer 4

, .

Answer 5

, Wordpress , , Wordpress wp_, wp_.

I created another database called wordpress, now I want to move all the tables with wp_ to this 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;

What is it.