MySQL 表的存储引擎

---

简介：在本教程中，您将学习如何使用表所使用的存储引擎以及如何将表的存储引擎更改为其他存储引擎。 MySQL支持多种提供不同功能和特性的存储引擎。例如，InnoDB表支持事务，而MyISAM则不支持。

---

查询表的当前存储引擎

有几种方法可以获取表的当前存储引擎。 检查tables表的当前存储引擎的第一种方法是从information_schema数据库中的表中查询数据。 例如，要offices在 mysqldemo 示例数据库中获取表的当前存储引擎，请使用以下查询：

SELECT 
    engine
FROM
    information_schema.tables
WHERE
    table_schema = 'mysqldemo'
        AND table_name = 'offices';

+--------+
| engine |
+--------+
| InnoDB |
+--------+
1 row in set (0.00 sec)

查询表的存储引擎的第二种方法是使用SHOW TABLE STATUS如下语句：

SHOW TABLE STATUS LIKE 'offices';

+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| 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 |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| offices | InnoDB |      10 | Dynamic    |    7 |           2340 |       16384 |               0 |        16384 |         0 |           NULL | 2019-08-27 15:36:37 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

获取表的存储引擎的第三种方法是使用SHOW CREATE TABLE语句。

SHOW CREATE TABLE offices;

mysql> SHOW CREATE TABLE offices\G;
*************************** 1. row ***************************
       Table: offices
Create Table: CREATE TABLE `offices` (
  `officeCode` varchar(10) NOT NULL,
  `city` varchar(50) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `addressLine1` varchar(50) NOT NULL,
  `addressLine2` varchar(50) DEFAULT NULL,
  `state` varchar(50) DEFAULT NULL,
  `country` varchar(50) NOT NULL,
  `postalCode` varchar(15) NOT NULL,
  `territory` varchar(10) NOT NULL,
  PRIMARY KEY (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

MySQL显示offices表使用了InnoDB存储引擎。

---

MySQL改变存储引擎

获得表的存储引擎信息后，可以使用ALTER TABLE语句更改它，如下所示：

ALTER TABLE table_name ENGINE engine_name;

要检查MySQL服务器当前支持的存储引擎，请使用SHOW ENGINES语句，如下所示：

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         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

例如，要将offices表的存储引擎从InnoDB更改为MyISAM，请使用以下语句：

ALTER TABLE offices ENGINE = 'MYISAM';

如果出现以下报错：

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

在本教程中，我们向您展示了如何查询表的当前存储引擎以及如何使用ALTER TABLE语句将其更改为其他存储引擎。

---