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)
查看表使用的存储引擎
MySQL> show create table 库.表 \G
mysql> show create table tarena.departments \G二、创建存储引擎
[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修改表的存储引擎
# 将memory存储引擎修改为myisam，mysql> alter table db10.c engine=myisam;四、补充说明：
每种引擎，存储的文件数量也是不同的
# myisam存储引擎的表 每个表对应3个表文件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存储引擎的表 每个表对应2个表文件mysql> system ls /var/lib/mysql/db10/b.*/var/lib/mysql/db10/b.frm /var/lib/mysql/db10/b.ibd# memory存储引擎的表 每个表对应1个表文件mysql> system ls /var/lib/mysql/db10/c.*/var/lib/mysql/db10/c.frm
在表存储存储数据之前修改储引擎，存储数据的位置也会改变，存储文件数量结构也会发生改变
# 将memory存储引擎修改为myisam，存储文件数量结构发生改变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
