mysql myisam 创建_Mysql 8.0版本开始，不允许创建 MyISAM 分区表

从MySQL 8.0版本开始，就不允许创建 MyISAM 分区表了，只允许创建已经实现了本地分区策略的引擎。

到目前为止，只有InnoDB和NDB这两个引擎支持本地分区策略。

【1】实际测试

(1)数据库版本号

# 获取版本号

SELECT VERSION(); # 8.0.12

(2)建表(InnoDB引擎)

CREATE TABLE `t_innodb` (

`ftime` DATETIME NOT NULL,

`c` INT(11) DEFAULT NULL,

KEY (`ftime`)

) ENGINE=INNODB DEFAULT CHARSET=latin1

PARTITION BY RANGE (YEAR(ftime))

(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = INNODB,

PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = INNODB,

PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = INNODB,

PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = INNODB);

创建成功。

(3)建表(MyiSAM引擎)

CREATE TABLE `t_myisam` (

`ftime` DATETIME NOT NULL,

`c` INT(11) DEFAULT NULL,

KEY (`ftime`)

) ENGINE=INNODB DEFAULT CHARSET=latin1

PARTITION BY RANGE (YEAR(ftime))

(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = MYISAM,

PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = MYISAM,

PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = MYISAM,

PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = MYISAM);

创建失败：

错误代码：1497

The mix of handlers in the partitions is not allowed in this version of MySQL

(4)建表(不支持本地分区策略)

CREATE TABLE `t_partition_by_hash`

(

`id` INT AUTO_INCREMENT PRIMARY KEY,

`sName` VARCHAR(10) NOT NULL,

`sAge` INT(2) UNSIGNED ZEROFILL NOT NULL,

`sAddr` VARCHAR(20) DEFAULT NULL,

`sGrade` INT(2) NOT NULL,

`sStuId` INT(8) DEFAULT NULL,

`sSex` INT(1) UNSIGNED DEFAULT NULL

) ENGINE=MYISAM PARTITION BY HASH(id) PARTITIONS 4;

创建失败：

错误代码：1178

The storage engine for the table doesn't support native partitioning

来源：oschina

链接：https://my.oschina.net/u/4265622/blog/4302918