创建一个测试表test.user,结构如下:

user

1.Range分区
CREATE TABLE `abc` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`account` VARCHAR(255) NOT NULL COMMENT '账号',
`passwd` VARCHAR(255) NOT NULL COMMENT '加密密码',
`salt` VARCHAR(10) NOT NULL COMMENT 'salt',
`state` ENUM('0','1','2') NOT NULL DEFAULT '0' COMMENT '状态,0正常',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=5 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = INNODB,
PARTITION p1 VALUES LESS THAN (10) ENGINE = INNODB,
PARTITION p2 VALUES LESS THAN (15) ENGINE = INNODB,
PARTITION p3 VALUES LESS THAN (20) ENGINE = INNODB)

查看分区情况:

SELECT * FROM information_schema.partitions WHERE TABLE_SCHEMA='test' AND TABLE_NAME='user' ;

如下图:

partition info

2.list分区方法

CREATE TABLE `abc` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`account` VARCHAR(255) NOT NULL COMMENT '账号',
`passwd` VARCHAR(255) NOT NULL COMMENT '加密密码',
`salt` VARCHAR(10) NOT NULL COMMENT 'salt',
`state` ENUM('0','1','2') NOT NULL DEFAULT '0' COMMENT '状态,0正常',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=5 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC 
PARTITION BY LIST(state)
    PARTITION p1 VALUES IN (0,1),
    PARTITION p2 VALUES IN (2),
)

3.hash分区方法

CREATE TABLE `abc` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`account` VARCHAR(255) NOT NULL COMMENT '账号',
`passwd` VARCHAR(255) NOT NULL COMMENT '加密密码',
`salt` VARCHAR(10) NOT NULL COMMENT 'salt',
`state` ENUM('0','1','2') NOT NULL DEFAULT '0' COMMENT '状态,0正常',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=5 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC 
PARTITION BY HASH(id)

4.key分区方法(表达式hash)

CREATE TABLE `abc` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`account` VARCHAR(255) NOT NULL COMMENT '账号',
`passwd` VARCHAR(255) NOT NULL COMMENT '加密密码',
`salt` VARCHAR(10) NOT NULL COMMENT 'salt',
`state` ENUM('0','1','2') NOT NULL DEFAULT '0' COMMENT '状态,0正常',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=5 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC 
PARTITION BY LINEAR HASH(MD5(id))

Post Navigation