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

72 阅读 0 评论 48 点赞

我是靠谱客的博主苹果大船，最近开发中收集的这篇文章主要介绍MySQL学习笔记（六）：MySQL中查看和修改表的存储引擎，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

查看系统支持的存储引擎

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网找了一个截图方便对比。

存储引擎的修改

MySQL初始默认引擎

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

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)