一、查看支持的数据库引擎
-
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
网友评论