# 查看mysql版本
select version();
# 1.range分区
CREATE TABLE IF NOT EXISTS user_range3 (
id int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
name varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
sex int(1) NOT NULL DEFAULT 0 COMMENT '性别',
PRIMARY KEY (id, sex)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN(3),
PARTITION p1 VALUES LESS THAN(6),
PARTITION p2 VALUES LESS THAN(9),
PARTITION p3 VALUES LESS THAN(12),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
# 插入一些数据
INSERT INTO `test`.`user_range` (`name` ,`sex`)VALUES ('tank', '0')
,('zhang',1),('ying',1),('张',1),('映',0),('test1',1),('tank2',1)
,('tank1',1),('test2',1),('test3',1),('test4',1),('test5',1),('tank3',1)
,('tank4',1),('tank5',1),('tank6',1),('tank7',1),('tank8',1),('tank9',1)
,('tank10',1),('tank11',1),('tank12',1),('tank13',1),('tank21',1),('tank42',1);
#验证数据存放的分区
EXPLAIN PARTITIONS select * from user_range where id <= 8 LIMIT 0,5;
# 2.list分区
create table if not exists `list_part3` (
`id` int(11) not null auto_increment comment '用户ID',
`province_id` int(2) not null default 0 comment '省',
`name` varchar(50) not null default '' comment '名称',
`sex` int(1) not null default 0 comment '性别',
PRIMARY KEY (id, province_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY LIST(province_id) (
PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10) ENGINE = InnoDB,
PARTITION p1 VALUES IN (11,12,13,14,15,16,17,18,19,20) ENGINE = InnoDB,
PARTITION p2 VALUES IN (21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
PARTITION p3 VALUES IN (31,32,33,34,35,36,37,38,39,40) ENGINE = InnoDB,
PARTITION p4 VALUES IN (41,42,43,44,45,46,47,48,49,50) ENGINE = InnoDB
);
# 注意:
# 主键要包含PARTITION BY LIST(col)中的col,即col要是表其中的一个主键,否则
# ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
# mysql5.5的各个分区的ENGINE要相同,否则:
# [Err] 1497 - The mix of handlers in the partitions is not allowed in this version of MySQL
# 3.hash分区
CREATE TABLE IF NOT EXISTS `hash_part2` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID',
`comment` varchar(1000) NOT NULL DEFAULT '' COMMENT '评论',
`ip` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',
PRIMARY KEY (`id`, ip)
) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY HASH(id)
PARTITIONS 4;
CREATE TABLE IF NOT EXISTS `hash_part2` (
`news_id` int(11) NOT NULL COMMENT '新闻ID',
`content` varchar(1000) NOT NULL COMMENT '新闻内容',
`u_id` varchar(25) NOT NULL COMMENT '来源IP',
`create_time` date not null default '0000-00-00 00:00:00' COMMENT '创建时间'#,
#PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY HASH(YEAR(create_time))
PARTITIONS 4;
# 4.LINEAR HASH分区
CREATE TABLE IF NOT EXISTS `linear_hash_part` (
`news_id` int(11) NOT NULL COMMENT '新闻ID',
`content` varchar(1000) NOT NULL COMMENT '新闻内容',
`u_id` varchar(25) NOT NULL COMMENT '来源IP',
`create_time` date not null default '0000-00-00 00:00:00' COMMENT '创建时间'#,
#PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY LINEAR HASH(YEAR(create_time))
PARTITIONS 4;
# 5.key分区
CREATE TABLE IF NOT EXISTS `key_part` (
`news_id` int(11) NOT NULL COMMENT '新闻ID',
`content` varchar(1000) NOT NULL COMMENT '新闻内容',
`u_id` varchar(25) NOT NULL COMMENT '来源IP',
`create_time` date not null default '0000-00-00 00:00:00' COMMENT '创建时间'#,
#PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY KEY(create_time)
PARTITIONS 4;
# 6.LINEAR KEY分区
CREATE TABLE IF NOT EXISTS `linear_key_part0` (
`news_id` int(11) NOT NULL COMMENT '新闻ID',
`content` varchar(1000) NOT NULL COMMENT '新闻内容',
`u_id` varchar(25) NOT NULL COMMENT '来源IP',
`create_time` date not null default '0000-00-00 00:00:00' COMMENT '创建时间'#,
#PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY LINEAR KEY(create_time)
PARTITIONS 4;
# 查看创建表
SHOW CREATE TABLE t_list;
# 删除表分区
ALTER TABLE t_list DROP PARTITION p0;
Mysql 5.5.36 表分区方法步骤SQL实例 range、 list、 hash、 key 分区实践 常见错误解决
最新推荐文章于 2021-02-02 02:07:08 发布