mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | 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 |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.01 sec)
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
1 row in set (0.00 sec)
# 临时修改
SET DEFAULT_STORAGE_ENGINE=MyISAM;
# 永久生效:或者修改 my.cnf 文件:
default-storage-engine=MyISAM
# 重启服务
systemctl restart mysqld.service
# 1. 创建表时指定存储引擎
CREATE TABLE 表名(
建表语句;
) ENGINE = 存储引擎名称;
# 2. 修改表的存储引擎
ALTER TABLE 表名 ENGINE = 存储引擎名称;
①. archive是归档的意思,仅仅支持插入和查询两个功能(行被插入后不能修改)
②. 在MYSQL5.5以后支持索引功能
③. 拥有很好的压缩机制,使用zlib压缩库,在记录请求的时候实时进行压缩,经常被用来作为仓库使用
④. acchive适合日志和数据采集(归档)类应用,适合存储大量的独立的作为历史记录的数据,拥有很高的插入速度,但是对查询的支持较差
①. Blackhole引擎没有实现任何存储机制,它会丢弃所有插入的数据,不做任何保存
②. 服务器会记录Blackhole表的日志,所以可以用来复制数据到备库,或者简单地记录到日志。但这种应用会碰到很多问题,因此并不推荐
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+---+------------+
| i | c |
+---+------------+
| 1 | record one |
| 2 | record two |
+---+------------+
2 rows in set (0.00 sec)
"1","record one"
"2","record two"
①. Memory采用的逻辑介质是内存,响应速度很快,但是当mysqld守护进程崩溃的时候数据会丢失。另外,要求存储的数据是数据长度不变的格式,比如,Blob和Text类型的数据不可用(长度不固定的)
②. 主要特征