有一张type表存储引擎为innodb
mysql> show create table type\G
*************************** 1. row ***************************
Table: type
Create Table: CREATE TABLE `type` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`var_name` char(12) NOT NULL DEFAULT '',
`img_width` int(5) DEFAULT NULL,
`img_height` int(5) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
有一张book表存储引擎为myisam
CREATE TABLE `book` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`book_name` varchar(255) DEFAULT NULL,
`book_desc` varchar(255) DEFAULT NULL,
`book_number` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
一:存储的文件形式不同
innodb
type.frm
type.ibd
.frm 文件 官方解释如下
MySQL stores its data dictionary information for tables in .frm files in database directories. Unlike other MySQL storage engines, InnoDB also encodes information about the table in its own internal data dictionary inside the tablespace. When MySQL drops a table or a database, it deletes one or more .frm files as well as the corresponding entries inside the InnoDB data dictionary. You cannot move InnoDB tables between databases simply by moving the .frm files.
.frm 文件存储的是结构信息
.ibd 文件存储的是数据信息
myisam
book.frm 表信息
book.MYD 数据文件(data)
book.MYI 索引文件(index)
二:innodb支持事务,myisam不支持事务。
- type表为innodb引擎
- book表为myisam引擎
建议看下 《InnoDB存储引擎第二版》