一、查看支持的数据库引擎
- Engine：引擎名称
- Support：表示该数据库是否支持该引擎,DEFAULT为默认使用的引擎
- Comment：引擎的功能描述
- Transactions：是否支持事务
- XA：是否支持事务回滚
- 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)
- 查看表使用的存储引擎
- MySQL> show create table 库.表 \G
mysql> show create table tarena.departments \G二、创建存储引擎
# 创建innodb引擎的表mysql> create table db10.b( name char(10))engine = innodb;# 创建memory引擎的表mysql> create table db10.c( addr char(10))engine = memory;三、修改存储引擎
[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 |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
# 将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 |
