ERROR 1517 (HY000): Duplicate partition name s1
7，Columns分区
Columns分区是在MySQL 5.5引入的分区类型，引入Columns分区解决了MySQL 5.5版本之前RANGE分区和LIST分区只支持整数分区，从而导致需要额外的函数计算得到整数或者通过额外的转换表来转换为整数再分区的问题。 Columns分区可以细分为RANGE Columns分区和LIST Columns分区，RANGE Columns分区和LIST Columns分区都支持整数、日期时间、字符串三大数据类型。
应用场景：
商品销售的日报表，年报表等
每天分一张表,表名用年月日每张表分24个分区，每个小时的数据分1个区.
| CREATE TABLE `year_log` ( `id` int(11) DEFAULT NULL, `money` int(11) unsigned NOT NULL, `date` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE (year(date)) ( PARTITION p2007 VALUES LESS THAN (2008), PARTITION p2008 VALUES LESS THAN (2009), PARTITION p2009 VALUES LESS THAN MAXVALUE ); CREATE TABLE `daily_log` ( `id` int(11) NOT NULL, `sid` char(36) NOT NULL, `sname` char(20) DEFAULT NULL, `date` datetime NOT NULL, PRIMARY KEY (`id`,`date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE COLUMNS(`date`) (PARTITION p1 VALUES LESS THAN ('2000-01-02'), PARTITION p2 VALUES LESS THAN ('2000-01-03'), PARTITION p3 VALUES LESS THAN ('2000-01-04'), PARTITION p4 VALUES LESS THAN ('2000-01-05'), PARTITION p5 VALUES LESS THAN ('2000-01-06'), PARTITION p6 VALUES LESS THAN ('2000-01-07'), PARTITION p7 VALUES LESS THAN ('2000-01-08'), PARTITION p367 VALUES LESS THAN (MAXVALUE)); |
三，分区管理
1，删除分区
| mysql> alter table user drop partition p4; |
2，新增分区
| range添加新分区 mysql> alter table user add partition(partition p4 values less than MAXVALUE); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 |
list添加新分区
mysql> alter table list_part add partition(partition p4 values in (25,26,28));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
hash重新分区
| mysql> alter table hash_part add partition partitions 4; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 |
key重新分区
| mysql> alter table key_part add partition partitions 4; Query OK, 1 row affected (0.06 sec) //有数据也会被重新分配 Records: 1 Duplicates: 0 Warnings: 0 |
子分区添加新分区，虽然我没有指定子分区，但是系统会给子分区命名的
| mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 |
