MySQL系列文章：修改表的存储引擎为innodb

MySQL系列文章目录

修改表的存储引擎为innodb

前言

修改表的存储引擎为innodb

提示：以下是本篇文章正文内容，下面案例可供参考

一、查询所有非InnoDB表

mysql> select table_schema,table_name ,engine
from information_schema.tables 
where
table_schema not in ('sys','mysql','information_schema','performance_schema')
and engine !='innodb';

二、备份所有非InnoDB表

select concat("mysqldump -uroot -proot ",table_schema," ",table_name,"
>/tmp/",table_schema,"_",table_name,".sql")
from information_schema.tables 
where
table_schema not in ('sys','mysql','information_schema','performance_schema')
and engine !='innodb';

三、修改存储引擎

mysql> select concat("alter table ",table_schema,".",table_name,"
engine=innodb;")  from information_schema.tables   where  table_schema not
in ('sys','mysql','information_schema','performance_schema')  and engine
!='innodb' into outfile '/tmp/a.sql';

mysql> source /tmp/a.sql

总结

修改mysql表的存储引擎为innodb，一定要先备份！！！