MySQL查看和修改表的存储引擎

1 查看系统支持的存储引擎show engines;

如：

mysql> show engines \G

*************************** 1. row ***************************

      Engine: MyISAM

     Support: YES

     Comment: Default engine as of MySQL 3.23 with great performance

Transactions: NO

          XA: NO

  Savepoints: NO

*************************** 2. row ***************************

      Engine: CSV

     Support: YES

     Comment: CSV storage engine

Transactions: NO

          XA: NO

  Savepoints: NO

*************************** 3. row ***************************

      Engine: MRG_MYISAM

     Support: YES

     Comment: Collection of identical MyISAM tables

Transactions: NO

          XA: NO

  Savepoints: NO

*************************** 4. row ***************************

      Engine: BLACKHOLE

     Support: YES

     Comment: /dev/null storage engine (anything you write to it disappears)

Transactions: NO

          XA: NO

  Savepoints: NO

*************************** 5. row ***************************

      Engine: FEDERATED

     Support: NO

     Comment: Federated MySQL storage engine

Transactions: NULL

          XA: NULL

  Savepoints: NULL

*************************** 6. row ***************************

      Engine: InnoDB

     Support: DEFAULT

     Comment: Supports transactions, row-level locking, and foreign keys

Transactions: YES

          XA: YES

  Savepoints: YES

*************************** 7. row ***************************

      Engine: ARCHIVE

     Support: YES

     Comment: Archive storage engine

Transactions: NO

          XA: NO

  Savepoints: NO

*************************** 8. row ***************************

      Engine: MEMORY

     Support: YES

     Comment: Hash based, stored in memory, useful for temporary tables

Transactions: NO

          XA: NO

  Savepoints: NO

8 rows in set (0.00 sec)

 

2 查看表使用的存储引擎

 两种方法：

 a、show table status from db_name where name='table_name';

 如：

mysql> show table status from test where name='user' \G

*************************** 1. row ***************************

           Name: user

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 3

 Avg_row_length: 5461

    Data_length: 16384

Max_data_length: 0

   Index_length: 0

      Data_free: 4194304

 Auto_increment: NULL

    Create_time: 2013-11-01 09:24:24

    Update_time: NULL

     Check_time: NULL

      Collation: gb2312_chinese_ci

       Checksum: NULL

 Create_options:

        Comment:

1 row in set (0.00 sec)

 

b、show create table table_name;

如：

mysql> show create table user \G

*************************** 1. row ***************************

       Table: user

Create Table: CREATE TABLE `user` (

  `id` int(11) NOT NULL,

  `name` varchar(50) NOT NULL,

  `age` int(11) NOT NULL,

  `sex` varchar(20) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=gb2312

1 row in set (0.00 sec)

 

如果显示的格式不好看，可以用\G代替行尾分号

 

有人说用第二种方法不准确，我试了下，关闭掉原先默认的Innodb引擎后根本无法执行show create table table_name指令，因为之前建的是Innodb表，关掉后默认用MyISAM引擎，导致Innodb表数据无法被正确读取。

 

3 修改表引擎方法

alter table table_name engine=innodb;

如：

mysql> alter table user engine=InnoDB;

Query OK, 3 rows affected (0.40 sec)

Records: 3  Duplicates: 0  Warnings: 0

 

mysql> show table status from test where name='user' \G

*************************** 1. row ***************************

           Name: user

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 3

 Avg_row_length: 5461

    Data_length: 16384

Max_data_length: 0

   Index_length: 0

      Data_free: 4194304

 Auto_increment: NULL

    Create_time: 2013-11-01 09:24:24

    Update_time: NULL

     Check_time: NULL

      Collation: gb2312_chinese_ci

       Checksum: NULL

 Create_options:

        Comment:

1 row in set (0.00 sec)

 

4 关闭Innodb引擎方法

关闭mysql服务： net stop mysql

找到mysql安装目录下的my.ini文件：

找到default-storage-engine=INNODB 改为default-storage-engine=MYISAM

找到#skip-innodb 改为skip-innodb

启动mysql服务：net start mysql

 

来源：http://www.linuxidc.com/Linux/2012-10/72884.htm