MySQL View and modify the table's storage engine

1. View the currently supported storage engines

Mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+| engine              | Support | Comment                                                           | transactions | xa   |  savepoints |+--------------------+---------+------------------------------------------------------- ---------+--------------+------+------------+| mrg_myisam          | yes     | collection of identical myisam tables                            | no           | no    | no         | |  csv                |  YES     | CSV storage engine                                                | NO           | NO    | no         | |  MyISAM             | YES     | MyISAM 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          | |  PERFORMANCE_SCHEMA | YES     | Performance Schema                                                | NO            | no   | no         | |  ARCHIVE            | YES      | Archive storage engine                                           | NO            | NO   | NO          | |  FEDERATED          | NO       | Federated MySQL storage engine                                   | NULL          | null | null       | |  InnoDB             | DEFAULT | Supports transactions,  row-level locking, and foreign keys     | yes           | YES  | YES         |+--------------------+---------+----------------------------------------------------------- -----+--------------+------+------------+

2. View the storage engine used by the table

mysql> show table status from  database  where name= ' table name '  \G  # Method One *************************** 1. row ***************************            Name:  Table name          engine:  InnoDB   #存储引擎类型         Version: 10      Row_format: Compact            Rows: 2 avg_row_length: 8192    data_length: 16384max_data_length:  0   index_length: 0      data_free: 0 auto_ increment: 3    create_time: 2015-07-21 10:51:48     update_time: null     check_time: null       Collation: utf8_general_ci       checksum: null create_options:          Comment:         mysql>  show create table test \G     #方法二 ***************************  1. row ***************************       Table:  testcreate table: create table  ' Test '   (   ' tid '  int (one)  default  NULL,   ' Tname '  char ( default null)  ENGINE=MyISAM DEFAULT  Charset=utf8

3. Modifying the Table engine method

mysql> ALTER TABLE Test ENGINE=INNODB;

4. Setting the default engine

1) Modify the [mysqld] option in the configuration file my.cnf to add Default-storage-engine=innodb #设置innodb为默认引擎

2) Restart the MySQL server:

#mysqladmin-U root-p shutdown

Or

#service mysqld Restart

This article is from the "Home_tang" blog, make sure to keep this source http://yagetang.blog.51cto.com/1780698/1676706

MySQL View and modify the table's storage engine