MySQL Version:5.1.49-communtity
1. 检查MySQL服务器是否支持表分区
mysql>show variables like ‘%partition%’;
2. 创建表
CREATE TABLE USER ( id INT AUTO_INCREMENT, username VARCHAR(20), birthday DATETIME, PRIMARY KEY (id,username) )ENGINE=INNODB PARTITION BY KEY(id,username) PARTITIONS 7;
3. 查看各个分区的情况(information_schema数据库的partitions表)
mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_schema='partitiontest' and table_name like 'user';
4. 指定数据文件和索引文件的位置(在Windows平台被忽略)
CREATE TABLE USER ( id INT AUTO_INCREMENT, username VARCHAR(20), birthday DATETIME, PRIMARY KEY (id,username) )ENGINE=INNODB PARTITION BY KEY(id,username) PARTITIONS 2( PARTITION p0 DATA DIRECTORY 'D:/' INDEX DIRECTORY 'D:/', PARTITION p1 DATA DIRECTORY 'E:/' INDEX DIRECTORY 'E:/' );
5. 修改分区类型(如果原表有数据,此语句不会损坏数据)
由Key类型改为hash
类型: ALTER TABLE `user` PARTITION BY HASH(id) PARTITIONS 3;
6. 删除分区(只能用于RANGE和LIST):
ALTER TABLE `user` DROP PARTITION p0;
7. 减少分区(只能用户KEY和HASH)
ALTER TABLE `user` COALESCE PARTITION 2;
//
减少两个分区,减少个数必须小于原有分区数
8. 添加分区
ALTER TABLE `user` ADD PARTITION PARTITIONS 2;
添加
2 个分区
ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));
9. 重新组织分区(RANGE和LIST)
ALTER TABLE members REORGANIZE PARTITION p0 INTO ( PARTITION s0 VALUES LESS THAN (1960), PARTITION s1 VALUES LESS THAN (1970) ); ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO ( PARTITION p0 VALUES LESS THAN (1970) );