mysql getrange_mysql-复合分区(range+hash)示例

复合分区示例

本次复合分区使用Range+hash做分区

1.按照时间做range分区，本次模拟通过一个小时做间隔。

2.对省份编码做hash分区，分了三个。

3.此次分区没有对分区做磁盘上对划分。

SQL代码

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for test_yd_table_range

-- ----------------------------

DROP TABLE IF EXISTS `test_yd_table`;

CREATE TABLE `test_yd_table` (

`mail_no` char(13) COLLATE utf8_bin NOT NULL,

`send_province_code` int(6) NOT NULL,

`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`receive_province_code` int(6) NOT NULL,

PRIMARY KEY (`mail_no`,`send_province_code`,`create_time`,`receive_province_code`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

/*!50500 PARTITION BY RANGE ( UNIX_TIMESTAMP(create_time))

subpartition by HASH(`send_province_code`)

SUBPARTITIONS 3 (

partition p2019120500 VALUES LESS THAN (1575475200),

partition p2019120501 VALUES LESS THAN (1575478800),

partition p2019120502 VALUES LESS THAN (1575482400),

partition p2019120503 VALUES LESS THAN (1575486000),

partition p2019120504 VALUES LESS THAN (1575489600),

partition p2019120505 VALUES LESS THAN (1575493200),

partition p2019120506 VALUES LESS THAN (1575496800),

partition p2019120507 VALUES LESS THAN (1575500400),

partition p2019120508 VALUES LESS THAN (1575504000),

partition p2019120509 VALUES LESS THAN (1575507600),

partition p2019120510 VALUES LESS THAN (1575511200),

partition p2019120511 VALUES LESS THAN (1575514800),

partition p2019120512 VALUES LESS THAN (1575518400),

partition p2019120513 VALUES LESS THAN (1575522000),

partition p2019120514 VALUES LESS THAN (1575525600),

partition p2019120515 VALUES LESS THAN (1575529200),

partition p2019120516 VALUES LESS THAN (1575532800),

partition p2019120517 VALUES LESS THAN (1575536400),

partition p2019120518 VALUES LESS THAN (1575540000),

partition p2019120519 VALUES LESS THAN (1575543600),

partition p2019120520 VALUES LESS THAN (1575547200),

partiti