(1)什么是存储引擎??
存储引擎是MYSQL中特有的一个术语,是一个表存储/组织数据的方式。(不同的存储引擎,表存储数据的方式不同)
(2)如何给表添加/指定“存储引擎”呢?
mysql> show create table t_student;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_student | CREATE TABLE `t_student` (
`no` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`cno` int DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `cno` (`cno`),
CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可以在建表的小括号后使用:
ENGINE:指定存储引擎·。
CHAREST:指定这张表的字符编码方式。
conclusion:
mysql的默认存储引擎是:InnoDB
mysql默认的·字符编码方式四:UTF-8
(3)如何查看mysql支持哪些存储引擎呢?
mysql> show engine \G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> show engine;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> show engines \G
*************************** 1. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 5. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 8. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.00 sec)
#查看版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.29 |
+-----------+
1 row in set (0.00 sec)
mysql常见的存储引擎介绍:
(1)MyISAM存储引擎
特征如下:
使用三个文件表示每个表
格式文件----存储表结构的定义(mytable.frm)
数据文件----存储表行的内容(mytable.MYD)
索引文件----存储表上索引(mytable.MYI)<可以被转换为压缩、只读表来节省空间>
说明:
I.在一张表中,如果是主键或加有unique约束的字段上会自动创建索引。
II.优点: 可被转换为压缩、只读表来节省空间
III.不支持事务机制,安全性低
(2) InnoDB存储引擎
mysql默认存储引擎,支持事务,数据库崩溃后自动恢复机制(特点:security)
主要特征如下:
I. InnoDB表在数据库目录中以 .frm格式文件表示。
II. InnoDB表空间 tablespace 被用于存储表的内容。
III. 提供一组用来记录事务性活动的日志文件。
IV. 在MYSQL服务器崩溃后提供自动恢复,支持外键及引用的完整性(包括级联删除和更新)
V.安全性好,但是效率不高,不能够被压缩 或 转化为只读来节省空间
(3)MEMORY存储引擎?
表存储在内存中,且行的长度固定,从而造就了其查询的高速率。
主要特征如下:
I. 数据库目录中,每个表均是以 .frm 格式的文件表示。
II. 表数据及其索引被存储在内存里(目的:查询快)
III.表级锁机制
IV.不能包含 TEXT 或 BLOB 字段
advantage: 插叙效率高。
disadvantage: 不安全,关机之后数据消失。(原因:数据和索引都是存储在内存中)