一、查看支持的數(shù)據(jù)庫(kù)引擎

• Engine：引擎名稱
• Support：表示該數(shù)據(jù)庫(kù)是否支持該引擎,DEFAULT為默認(rèn)使用的引擎
• Comment：引擎的功能描述
• Transactions：是否支持事務(wù)
• XA：是否支持事務(wù)回滾
• Savepoints：是否支持外鍵

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)

• 查看表使用的存儲(chǔ)引擎
• MySQL> show create table 庫(kù).表 \G

mysql> show create table  tarena.departments \G

二、創(chuàng)建存儲(chǔ)引擎

• 建表時(shí)指定表使用的存儲(chǔ)引擎

# 創(chuàng)建innodb引擎的表
mysql> create table db10.b(
  name char(10)
)engine = innodb;
# 創(chuàng)建memory引擎的表
mysql> create  table db10.c(
  addr char(10)
)engine = memory;

三、修改存儲(chǔ)引擎

• 修改數(shù)據(jù)庫(kù)服務(wù)默認(rèn)使用的存儲(chǔ)引擎

[root@host61 ~]#vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam  # 添加此行
:wq
            
[root@host61 ~]# systemctl restart mysqld
[root@host61 ~]# mysql  -uroot -p密碼 

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | YES     | 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             | DEFAULT | 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       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

• alter修改表的存儲(chǔ)引擎

# 將memory存儲(chǔ)引擎修改為myisam，
mysql> alter table db10.c engine=myisam;

四、補(bǔ)充說(shuō)明：

• 每種引擎，存儲(chǔ)的文件數(shù)量也是不同的

# myisam存儲(chǔ)引擎的表 每個(gè)表對(duì)應(yīng)3個(gè)表文件
mysql> system ls /var/lib/mysql/db10/a.*
/var/lib/mysql/db10/a.frm  
/var/lib/mysql/db10/a.MYD  
/var/lib/mysql/db10/a.MYI

# innodb存儲(chǔ)引擎的表 每個(gè)表對(duì)應(yīng)2個(gè)表文件
mysql> system ls /var/lib/mysql/db10/b.*
/var/lib/mysql/db10/b.frm  
/var/lib/mysql/db10/b.ibd

# memory存儲(chǔ)引擎的表 每個(gè)表對(duì)應(yīng)1個(gè)表文件
mysql> system ls /var/lib/mysql/db10/c.*
/var/lib/mysql/db10/c.frm

• 在表存儲(chǔ)存儲(chǔ)數(shù)據(jù)之前修改儲(chǔ)引擎，存儲(chǔ)數(shù)據(jù)的位置也會(huì)改變，存儲(chǔ)文件數(shù)量結(jié)構(gòu)也會(huì)發(fā)生改變

# 將memory存儲(chǔ)引擎修改為myisam，存儲(chǔ)文件數(shù)量結(jié)構(gòu)發(fā)生改變
mysql> alter table db10.c engine=myisam;
mysql> system ls /var/lib/mysql/db10/c.*
/var/lib/mysql/db10/c.frm  
/var/lib/mysql/db10/c.MYD  
/var/lib/mysql/db10/c.MYI