Mysql 5.5.36 表分区方法步骤SQL实例 range、 list、 hash、 key 分区实践 常见错误解决

# 查看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;