数据库建表时实现存储引擎自动转换

disabled_storage_engines参数是5.7.8版本新引入的参数,它用来设置在CREATE TABLE时被禁用的存储引擎。举例:创建新表时禁用MyISAM和MEMORY引擎

[mysqld]disabled_storage_engines="MyISAM,MEMORY"
问题现象

借助disabled_storage_engines参数,可以在建表时实现存储引擎自动转换。例如:执行如下建表SQL

CREATE TABLE tb_01 (id int(11) NOT NULL AUTO_INCREMENT,age int(11) NOT NULL DEFAULT '0',name varchar(64) NOT NULL DEFAULT '',PRIMARY KEY (id)) ENGINE=MyISAM;

创建完成后有warnings返回,并且表的存储引擎自动转换成为InnoDB引擎:

原因分析

当在sql_mode中不设置NO_ENGINE_SUBSTITUTION,且CREATE TABLE中的ENGINE子句指定的存储引擎被参数disabled_storage_engines禁用,MySQL会把新建表的引擎改为默认存储引擎。

解决方案

按照下面三个步骤设置参数,新建表时可以把指定的存储引擎转换成InnoDB引擎:

  1. 设置被禁用的存储引擎,例如:禁用MyISAM和MEMORY引擎:

[mysqld]disabled_storage_engines="MyISAM,MEMORY"

  1. sql_mode设置中不含NO_ENGINE_SUBSTITUTION:

SET sql_mode='';

  1. 设置默认存储引擎是InnoDB:

SET GLOBAL default_storage_engine=InnoDB;
结论建议

  • 从稳定性和性能多方面考虑,针对MySQL数据库中的存储引擎,推荐使用InnoDB引擎
  • 建议设置disabled_storage_engines参数,把MyISAM、MEMORY等存储引擎禁用,并且能够建表时自动转换成InnoDB引擎
  • MySQL5.7.23之前的版本存在Bug,disabled_storage_engines参数设置后不会实现自动转换,Bug说明如下:

Bug #27502530With the NO_ENGINE_SUBSTITUTION SQL mode disabled, an error (rather than substitution) occurred CREATE TABLE and ALTER TABLE if the desired engine was disabled using the disabled_storage_engines system variable.

  • 分区表无法实现自动引擎转换功能,创建被禁用引擎的分区表会报错,原因是MySQL只能对分区表的主表进行引擎转换,但不能对分区表的子表进行转换,导致报错:

ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL