Centos6.8 查看、修改MySQL数据库引擎

通过MySQL -uroot -p登录到数据库。

1.查看当前的引擎

mysql> show engines /G;mysql> show engines /G;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id:    4Current database: *** NONE ****************************** 1. row ***************************      Engine: PERFORMANCE_SCHEMA     Support: YES     Comment: Performance SchemaTransactions: NO          XA: NO  Savepoints: NO*************************** 2. row ***************************      Engine: MRG_MYISAM     Support: YES     Comment: Collection of identical MyISAM tablesTransactions: NO          XA: NO  Savepoints: NO*************************** 3. row ***************************      Engine: CSV     Support: YES     Comment: CSV storage engineTransactions: 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: InnoDB     Support: YES     Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES          XA: YES  Savepoints: YES*************************** 6. row ***************************      Engine: MEMORY     Support: YES     Comment: Hash based, stored in memory, useful for temporary tablesTransactions: NO          XA: NO  Savepoints: NO*************************** 7. row ***************************      Engine: ARCHIVE     Support: YES     Comment: Archive storage engineTransactions: NO          XA: NO  Savepoints: NO*************************** 8. row ***************************      Engine: MyISAM     Support: DEFAULT     Comment: MyISAM storage engineTransactions: NO          XA: NO  Savepoints: NO*************************** 9. row ***************************      Engine: FEDERATED     Support: NO     Comment: Federated MySQL storage engineTransactions: NULL          XA: NULL  Savepoints: NULL9 rows in set (0.00 sec)

2. 查看默认引擎

mysql> show variables like '%storage_engine%';

mysql> show variables like '%storage_engine%';+----------------------------------+--------+| Variable_name                    | Value  |+----------------------------------+--------+| default_storage_engine           | InnoDB || default_tmp_storage_engine       | InnoDB || disabled_storage_engines         |        || internal_tmp_disk_storage_engine | InnoDB |+----------------------------------+--------+

通过上面我们可以发现，默认的数据库引擎是InnoDB

3.修改引擎

#vi /etc/my.cnf

在my.cnf中[mysqld] 部分添加代码： default-storage-engine=MyISAM

重启mysqld：service mysqld restart再次show engines 确认。 数据库引擎默认就更改为MyISAM。

mysql> show variables like '%storage_engine%';+----------------------------------+--------+| Variable_name                    | Value  |+----------------------------------+--------+| default_storage_engine           | MyISAM || default_tmp_storage_engine       | InnoDB || disabled_storage_engines         |        || internal_tmp_disk_storage_engine | InnoDB |+----------------------------------+--------+

4.设置表的存储引擎

语法：

CREATE TABLE 表名(    #省略代码)ENGINE=存储引擎;

示例：

CREATE TABLE `myisam` (   id  INT(4))ENGINE=MyISAM;

在windows环境中，数据库默认引擎为InnoDB，也可以通过修改my.ini 来定义默认引擎。方法一样