Mysql(8)_存储引擎之InnoDB
-- 查看当前数据库支持的存储引擎 SHOW ENGINES; -- VALUE为disable的记录表示支持该引擎,但是数据库启动时被禁用 show VARIABLES LIKE 'have%';
从mysql 5.5版本开始,innodb是mysql默认的存储引擎,下面详细介绍InnoDB存储引擎
1 自动增长序列
InnoDB的自动增长列可以手工插入,但是插入的值如果是空或者是0,则实际插入的将是自动增长后的值。下面定义新表,其中i使用自动增长序列,对表进行插入记录,然后查看自动增长列的处理情况
create table autoincre_demo( i SMALLINT NOT NULL auto_increment, NAME VARCHAR(10), PRIMARY KEY(i) )ENGINE=INNODB; INSERT INTO autoincre_demo VALUES(1,'1'),(0,'2'),(NULL,'3'); SELECT * FROM autoincre_demo;
t
可以使用last_insert_id()来返回当前线程最后插入记录使用的值,如果一次性插入多条数据,则返回插入的第一条记录所使用的字段增长值。可以使用alter table *** auto_increment=n 来强制设置自动增长列的初始值。默认从1开始,但是该强制的默认值是保留在内存中的,如果使用该值在使用之前数据库重新启动,这个强制的默认值就会丢失,需要在数据库启动后重新设置
对于InnoDB表,自动增长序列必须是索引。如果是组合索引,也必须是组合索引的第一列。对于MyISAM表,自动增长序列可以是组合索引的其他列
2 外键约束
Mysql支持外键约束的存储引擎只有InnoDB,在创建外键的时候,要求父表必须有对应的索引,子表在创建外键时也会自动创建对于的索引
use test01; CREATE TABLE country( country_id SMALLINT UNSIGNED NOT NULL auto_increment, country VARCHAR(50) NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (country_id) )ENGINE=INNODB DEFAULT charset=utf8; CREATE TABLE `city` ( `city_id` smallint(5) unsigned NOT NULL auto_increment, `city` varchar(50) NOT NULL, `country_id` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`city_id`), KEY `idx_fk_country_id` (`country_id`), CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into country(country_id, country) values(1, "china"); insert into city(city_id, city, country_id) values(200, "wenzhou", 1); SELECT * from country WHERE country_id = 1; SELECT * from city WHERE country_id =1; -- 执行删除语句报错。不能删除 DELETE FROM country where country_id=1; -- 可以update,执行这条语句时,字表city的country_id字段也被修改。 -- CASCADE 表示父表在更新或者删除时,子表对应记录也被更新或者删除 -- set null 表示如果父表进行更新或删除操作,子表对应字段被set NULL -- RESTRICT 和no action 相同,限制子表有关联记录情况下父表不能更新 update country set country_id =1 WHERE country_id =1000;
InnoDB 存储引擎具有提交、回滚和奔溃事回复能力的事物安全,但是对比MyISAM的存储引擎,InnoDB写的处理效率差些,并且会占用更多的磁盘空间以保留数据和索引。
InnoDB的存储方式有两种:一是使用共享表空间存储,这种方式创建的表的表结构报存在.frm文件中,数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中,可以是多个文件
二是使用多表空间存储,这种方式创建的表的表结构依然保存在.frm中,但每个表的数据和索引都单独保存在.idb中,这里就先介绍这么多。等以后用到再详细介绍!即使在多表空间的存储方式下,共享表空间依然是必须的,InnoDB把内部数据词典和在线重做日志放在这个文件中