MySQL Modify table's storage engine (MYISAM<=>INNODB)

View the supported database engines for the current database and the default database engine

Mysql> Show engines;+--------------------+---------+--------------------------------------------------------- -------+--------------+------+------------+| Engine | Support | Comment | Transactions | XA | savepoints |+--------------------+---------+----------------------------------------------------------------+--- -----------+------+------------+| Performance_schema | YES | Performance Schema | NO | NO | NO | | Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO | | CSV | YES | CSV Storage Engine | NO | NO | NO | | Blackhole | YES | /dev/null Storage Engine (anything you write to it disappears) | NO | NO | NO | | MEMORY | YES | Hash based, stored In memory, useful for temporary tables | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, Row-level locking, and foreign keys | YES | YES | YES | | ARCHIVE | YES | Archive Storage Engine | NO | NO | NO | | MyISAM | YES | MyISAM Storage Engine | NO | NO | NO | | Federated | NO | Federated MySQL Storage Engine | NULL | NULL | NULL |+--------------------+---------+----------------------------------------------------------------+--------- -----+------+------------+9 rows in Set (0.00 sec)

  View the storage engine for a table

Mysql> Show tables;+----------------------------------------------+| Tables_in_performance_schema |+----------------------------------------------+| cond_instances | | events_waits_current | | Events_waits_history | | Events_waits_history_long | | events_waits_summary_by_instance | | Events_waits_summary_by_thread_by_event_name | | Events_waits_summary_global_by_event_name | | file_instances | | File_summary_by_event_name | | file_summary_by_instance | | mutex_instances | | Performance_timers | | rwlock_instances | | Setup_consumers | | setup_instruments | | Setup_timers | | Threads |+----------------------------------------------+17 rows in Set (0.00 sec) mysql> Show create table threads;+---------+----------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------+| Table |                                                                                                                                                                      Create Table |+---------+------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- --------------+| Threads | CREATE TABLE ' Threads ' (' thread_id ' int (one) not null, ' processlist_id ' int (one) DEFAULT NULL, ' NAME ' varchar ' N ' ULL) Engine=performance_schema DEFAULT Charset=utf8 |+---------+-------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------+1 Row in Set (0.00 sec) 

  Change the storage engine directly

Mysql> Show CREATE TABLE wholesale;+-------------------+------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------+| Table |                                                                                                                                                                                                                                        Create Table |+-------------------+---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------+| Jindong_wholesale | CREATE TABLE ' wholesale ' (' act_id ' mediumint (8) unsigned not NULL auto_increment, ' goods_id ' mediumint (8) unsigned not NULL, ' goods_name ' varchar (255) is not NULL, ' rank_ids ' varchar (255) is not NULL, ' prices ' text is not NULL, ' enabled ' tinyint ( 3) unsigned not NULL, PRIMARY key (' act_id '), key ' goods_id ' (' goods_id ') USING BTREE) Engine=myisam auto_increment=2 DE FAULT Charset=utf8 |+-------------------+----------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------+1 Row in Set (0.00 sec)

As the following command:

mysql> ALTER TABLE wholesale ENGINE=INNODB; Query OK, 1 row affected (0.32 sec) records:1  duplicates:0  warnings:0

Mysql> Show CREATE TABLE wholesale;+-------------------+------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------+| Table |                                                                                                                                                                                                                                        Create Table |+-------------------+---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------+| Jindong_wholesale | CREATE TABLE ' wholesale ' (' act_id ' mediumint (8) unsigned not NULL auto_increment, ' goods_id ' mediumint (8) unsigned not NULL, ' goods_name ' varchar (255) is not NULL, ' rank_ids ' varchar (255) is not NULL, ' prices ' text is not NULL, ' enabled ' tinyint ( 3) unsigned not NULL, PRIMARY key (' act_id '), key ' goods_id ' (' goods_id ') USING BTREE) Engine=innodb auto_increment=2 DE FAULT Charset=utf8 |+-------------------+----------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------+1 Row in Set (0.00 sec)

Multi-table Change engine:

# mysqldump-uroot-p123456 SX >sx_bak.sqlsed-i "s/myisam/innodb/g" Sx_bak.sql mysql-uroot-p123456 SX <sx_bak.sql

MySQL Modify table's storage engine (MYISAM<=>INNODB)