disabled_storage_engines参数实现表引擎自动转换
所有文档

          云数据库 RDS

          disabled_storage_engines参数实现表引擎自动转换

          背景

          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引擎:

          image.png

          原因分析

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

          解决方案

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

          1. 设置被禁用的存储引擎,例如:禁用MyISAM和MEMORY引擎:
          [mysqld]
          disabled_storage_engines="MyISAM,MEMORY"

          image.png

          1. sql_mode设置中不含NO_ENGINE_SUBSTITUTION:
          SET sql_mode='';

          image.png

          1. 设置默认存储引擎是InnoDB:
          SET GLOBAL default_storage_engine=InnoDB;

          image.png

          结论建议

          • 从稳定性和性能多方面考虑,针对MySQL数据库中的存储引擎,推荐使用InnoDB引擎
          • 建议设置disabled_storage_engines参数,把MyISAM、MEMORY等存储引擎禁用,并且能够建表时自动转换成InnoDB引擎
          • MySQL5.7.23之前的版本存在Bug,disabled_storage_engines参数设置后不会实现自动转换,Bug说明如下:
          Bug #27502530
          With the NO_ENGINE_SUBSTITUTION SQL mode disabled, an error (rather than substitution) occurred for 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
          上一篇
          sql_mode参数使用详解之NO_ENGINE_SUBSTITUTION
          下一篇
          MySQL之interactive_timeout和wait_timeout