MySQL学习笔记（六）：MySQL中查看和修改表的存储引擎

浅浅的花香味﹌ 2022-06-06 04:52 222阅读 0赞

### 查看系统支持的存储引擎 ###

> **`SHOW ENGINES;`**

mysql> SHOW ENGINES;
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
    | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
    | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
    | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
    | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
    | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
    | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
    | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
    | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    9 rows in set (0.00 sec)

--------------------

### 查看数据表所使用的存储引擎 ###

>  *  **`SHOW CREATE TABLE 表名`**
>  *  **`SHOW TABLE STATUS FROM 数据库名 WHERE name = '表名';`**

mysql> SHOW CREATE TABLE user_info;
    +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table     | Create Table                                                                                                                                                                                                                                                                                                     |
    +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | user_info | CREATE TABLE `user_info` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `username` varchar(40) NOT NULL,
      `password` varchar(100) NOT NULL,
      `age` tinyint(3) unsigned DEFAULT NULL,
      `sex` tinyint(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |
    +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SHOW TABLE STATUS FROM user WHERE name =  'user_info';
    +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
    | Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
    +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
    | user_info | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |               0 |            0 |         0 |              9 | 2017-10-30 12:18:28 | 2017-10-30 22:10:26 | NULL       | utf8_general_ci |     NULL |                |         |
    +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
    1 row in set (0.00 sec)

--------------------

### 主要存储引擎的对比 ###

我看网上有很多博客文章对各存储引擎都有了一个比较详细的阐述，但是多数是文字，各个储存引擎之间的对比不够明显。所以我上IMOOC网找了一个截图方便对比。  
![image][]

--------------------

### 存储引擎的修改 ###

#### MySQL初始默认引擎 ####

`MySQL`初始默认引擎为`InnoDB`。  
关于`InnoDB`的介绍从这篇文章中摘选了一些介绍【[原文链接][Link 1]】：

>  *  `InnoDB`是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。我的电脑上安装的`MySQL5.6.13`版，`InnoDB`就是作为默认的存储引擎。`InnoDB`还引入了行级锁定和外键约束，在以下场合下，使用`InnoDB`是最理想的选择：  
>     
>     
>      *  **更新密集的表**：`InnoDB`存储引擎特别适合处理多重并发的更新请求。
>      *  **事务**：`InnoDB`存储引擎是支持事务的标准`MySQL`存储引擎。
>      *  **自动灾难恢复**：与其它存储引擎不同，`InnoDB`表能够自动从灾难中恢复。
>      *  **外键约束**：`MySQL`支持外键的存储引擎只有`InnoDB`。
>      *  **支持自动增加列`AUTO_INCREMENT`属性**： 一般来说，如果需要事务支持，并且有较高的并发读取频率，`InnoDB`是不错的选择。

#### ① 通过修改MySQL配置文件 ####

> **`default-storage-engine = engine_name`**

#### ② 通过创建数据表命令 ####

> **`CREATE TABLE new_tb( ... )ENGINE = engine_name;`**

mysql> CREATE TABLE new_tb(
        -> id INT PRIMARY KEY
        -> )ENGINE = MyISAM;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> SHOW CREATE TABLE new_tb;
    +--------+------------------------------------------------------------------------------------------------------------+
    | Table  | Create Table                                                                                               |
    +--------+------------------------------------------------------------------------------------------------------------+
    | new_tb | CREATE TABLE `new_tb` (
      `id` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
    +--------+------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

#### ③ 通过修改数据表命令 ####

> **`ALTER TABLE table_name ENGINE [=] engine_name`**

mysql> SHOW CREATE TABLE new_tb;
    +--------+------------------------------------------------------------------------------------------------------------+
    | Table  | Create Table                                                                                               |
    +--------+------------------------------------------------------------------------------------------------------------+
    | new_tb | CREATE TABLE `new_tb` (
      `id` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
    +--------+------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> ALTER TABLE new_tb ENGINE = InnoDB;
    Query OK, 0 rows affected (0.07 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> SHOW CREATE TABLE new_tb;
    +--------+------------------------------------------------------------------------------------------------------------+
    | Table  | Create Table                                                                                               |
    +--------+------------------------------------------------------------------------------------------------------------+
    | new_tb | CREATE TABLE `new_tb` (
      `id` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +--------+------------------------------------------------------------------------------------------------------------+
    1 row in set (0.01 sec)

--------------------

[image]: https://img-my.csdn.net/uploads/201710/30/1509375143_7578.png
[Link 1]: http://www.jb51.net/article/55849.htm