MyISAM与INNODB引擎区别

--排错篇

导读

今天在导入数据库文件中碰到了一些问题,无论是命令行source *.sql 还是通过Navicat、SQLyog工具去操作导入。

爆出了:

Error occured at:2019-03-07 11:44:34

Line no.:37

Error Code: 1616 - DELAYED option not supported for table '**'

直接搜索报错的信息,

InnoDB不支持Insert delayed语法

引自官网: INSERT DELAYED works only with MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables. For engines that do not support DELAYED, an error occurs.
比如出现错误:

ERROR 1616 (HY000): DELAYED option not supported for table 'test'

MySQL内置的存储引擎,MySQL存储表格式为两类,一是MyISAM,二是InnoDB。下面将要记录的是两类存储引擎的的介绍和区别。

MyISAM与InnoDB介绍

资料来源于官网:

MyISAM:

MyISAM是MySQL的默认存储引擎(当然,可以在配置文件中修改),但有很多有用的扩展。(注意MySQL5.1不支持ISAM)。每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD(MYData)。索引文件的扩展名是.MYI(MYIndex)。

以下是test数据库中存储文件内容:

InnoDB:

InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要,因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中,你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来,甚至在同一个查询中也可以混合。

InnoDB是为处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。InnoDB被用来在众多需要高性能的大型数据库站点上产生。著名的Internet新闻站点Slashdot.org运行在InnoDB上。Mytrix Inc.在InnoDB上存储超过1TB的数据,还有一些其它站点在InnoDB上处理平均每秒800次插入/更新的负荷。

巧了,今天我处理的就是超过上G的SQL文件,从这一点来看,知道了BC站采用的数据库原始存储方式,到时候不至于我非得用notepad++打开上G的SQL文件,判断它存储格式,这个和之前所接触的完全不一样,哎见识少了。

1、如何设置MySQL的默认存储引擎:

-->打开mysql配置文件mysql.ini ,修改内容如下所示。

2、如何修改单个表设定引擎:

SQL命令行:mysql> ALTER TABLE 表名称 ENGINE =MyISAM|InnoDB

本地测试一下,选定表proclog,mysql> show create table proclog;

修改前:

修改后:

mysql> ALTER TABLE proclog ENGINE=MyISAM;

问题复现

记录下我导出的SQL文件,重新导入到本地环境中报错的缘故:

本地导出了一个小库的文件,查看sql原文件,发现从源站导出的是以InnoDB为引擎的存储格式。

而我本地导入时却爆出了:

Error occured at:2019-03-07 11:44:34

Line no.:37

Error Code: 1616 - DELAYED option not supported for table '**'

很明显,原因是insert语句后面跟着delayed语句,而InnoDB不支持Insert delayed语句,那么问什么导出备份的时候会跟着一起输出了。

从源站导出SQL是用的SQLyog,那从头开始查,最后发现自己当时勾选了DML选项全部,而其中的一个选项是生成INSERT DELAYED语句。。

重新取消勾选后导出,SQLyog及Navicat可以正常导入,SQL文件如下所示:

问题解决。

MyISAM转为InnoDB需要注意的事项

一、 AUTO_INCREMENT列不在多列索引的首位的MyISAM表不能转换

AUTO_INCREMENT列的InnoDB表, innodb 表中只能设置1个auto 属性的列, 且 auto 列必须定义为 key, 可以是单 key, 也可以是组合 key, 如果是组合key, auto 列必须在最左边; 比如 MyISAM 支持 idx_name_id(`name`, `auto_id`) 的组合索引, 但是 InnoDB 不支持 idx_name_id(`name`, `auto_id`), 却可以支持 auto 列在最左边的情况: idx_id_name(`auto_id`, `name`);

常见报错格式:

since an InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column.

二、FULLTEXT 全文索引

5.6版本以下的MySQL基于InnoDB表还不支持FULLTEXT索引, 此前的版本仅MyISAM支持FULLTEXT索引, 所以存在FULLTEXT索引的MyISAM表不能转换。比如转换时出现以下错误: ERROR 1214 (HY000) at line 1: The used table type doesn’t support FULLTEXT indexes. 开发人员可以采用sphix, lucence等第三方的工具实现全文索引,避免在DB端的操作。

三、行记录过大不能转换

类似如下错误:

ERROR 1118 (42000) at line 1: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

四、InnoDB不支持Insert delayed语法

常见错误:

ERROR 1616 (HY000): DELAYED option not supported for table 'test'

Innodb表在并发和锁方面不像MyISAM,Memory表存在诸多限制, INSERT DELAYED没必要使用到InnoDB表中,诸如delayed的语句可以在应用中改为标准的INSERT语句。

参考来源

https://highdb.com/myisam%E8%A1%A8%E8%BD%AC%E4%B8%BAinnodb%E8%A1%A8%E6%B3%A8%E6%84%8F%E4%BA%8B%E9%A1%B9/

https://www.cnblogs.com/jins-note/p/9513187.html

发表评论

电子邮件地址不会被公开。 必填项已用*标注