Batch modify MySQL database table MyISAM to InnoDB

There is a way to do this online:

1. View the commands for the current data engine:

Show engines;

2.

Use ' [DBNAME] `;

SELECT Group_concat (CONCAT (' ALTER TABLE ', table_name, ' engine=innodb; ') SEPARATOR ")

From INFORMATION_SCHEMA. TABLES as T

WHERE table_schema = ' [DBNAME] ' and table_type = ' BASE TABLE ';

Replace the red part with the database you are using

After executing the query command is actually generated, and then copy the production command, execute SQL can:

ALTER TABLE info_aaaa Engine=innodb; ALTER TABLE info_data_bbb Engine=innodb; ALTER TABLE INFO_DATA_CCC Engine=innodb; ALTER TABLE info_data_ddd Engine=innodb; ALTER TABLE info_data_eee Engine=innodb; ALTER TABLE Info_data_fffengine=innodb;

The data engine that viewed the data table again has changed: Show table status from [DBNAME];

However, there are only a few of the commands that are generated, and there may be too many tables.

Then use the following sentence, in the query, paste the following command, will generate

SELECT concat (' ALTER TABLE ', table_name, ' engine=innodb; ') From INFORMATION_SCHEMA. TABLES WHERE table_schema = ' Jingzong ' and ENGINE = ' MYISAM ' and table_type = ' BASE TABLE '

The generated statements are:

CREATE TABLE my_table (
' Concat (' ALTER TABLE ', table_name, ' engine=innodb; ') ' VARCHAR
);
INSERT into My_table (' concat (' ALTER TABLE ', table_name, ' engine=innodb; ') ') VALUES (' ALTER TABLE jz_admin engine=innodb; ');
INSERT into My_table (' concat (' ALTER TABLE ', table_name, ' engine=innodb; ') ') VALUES (' ALTER TABLE jz_admin_panel engine=innodb; ');
INSERT into My_table (' concat (' ALTER TABLE ', table_name, ' engine=innodb; ') ') VALUES (' ALTER TABLE jz_admin_role engine=innodb; ');
INSERT into My_table (' concat (' ALTER TABLE ', table_name, ' engine=innodb; ') ') VALUES (' ALTER TABLE jz_admin_role_priv engine=innodb; ');
INSERT into My_table (' concat (' ALTER TABLE ', table_name, ' engine=innodb; ') ') VALUES (' ALTER TABLE jz_announce engine=innodb; ');
INSERT into My_table (' concat (' ALTER TABLE ', table_name, ' engine=innodb; ') ') VALUES (' ALTER TABLE jz_attachment engine=innodb; ');
INSERT into My_table (' concat (' ALTER TABLE ', table_name, ' engine=innodb; ') ') VALUES (' ALTER TABLE jz_attachment_index engine=innodb; ');
INSERT into My_table (' concat (' ALTER TABLE ', table_name, ' engine=innodb; ') ') VALUES (' ALTER TABLE jz_badword engine=innodb; ');

All statements are there, but this paragraph, bad use,

Manually build a file, remove the unused, batch query ... Replace with "',

Left: ALTER TABLE jz_admin Engine=innodb;

Like that.

And then put it in the query, execute it again.

----------  recruit the future great God -----------------------

If you have altruistic heart, willing to help others, willing to share
If you encounter PHP problem, Baidu and asked the other groups still did not get answers

Welcome to join, PHP technical question and answer group, QQ Group: 292626152

Educational developments Help others, you will also get promotion!

In order to cherish everyone's valuable time, please do not chat!

May we help each other and make progress together!

Add message code, PHP,AJAX,THINKPHP,YII ...

Batch modify MySQL database table MyISAM to InnoDB