mysql分区表---list partition

               

author：skate
time：2012/11/28

mysql分区---list partition

 

mysql的list分区和range的分区十分类似，只是list分区的范围是一列表，用PARTITION BY LIST(expr)类似定义，expr是基于一列或某列返回interger的表达式

 

list分区定义
mysql> show create table list_t;
 CREATE TABLE `list_t` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (c1)
(PARTITION p0 VALUES IN (1,4,7) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (2,5,8) ENGINE = InnoDB) */

mysql> insert into list_t values(1,3),(3,4),(4,5);
ERROR 1526 (HY000): Table has no partition for value 3

mysql> insert into list_t values(1,3),(5,4),(4,5);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

清空表
mysql> truncate table list_t;
Query OK, 0 rows affected (0.12 sec)

忽略不匹配的行
mysql> insert ignore into list_t values(1,3),(3,4),(4,5);
Query OK, 2 rows affected (0.04 sec)
Records: 3  Duplicates: 1  Warnings: 0

mysql> select * from list_t;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    3 |
|    4 |    5 |
+------+------+
2 rows in set (0.01 sec)

mysql>

添加分区
mysql> alter table list_t add partition (partition p2 values in (3,6,9));
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table list_t;
 CREATE TABLE `list_t` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (c1)
(PARTITION p0 VALUES IN (1,4,7) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (2,5,8) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (3,6,9) ENGINE = InnoDB) */

mysql>

mysql> insert  into list_t values(3,3),(6,4),(9,5);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from list_t;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    3 |
|    4 |    5 |
|    3 |    3 |
|    6 |    4 |
|    9 |    5 |
+------+------+
5 rows in set (0.00 sec)

 

rebuild分区
mysql> alter table list_t rebuild partition p1;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

检查分区表
mysql> alter table list_t check partition p1;
+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| skate.list_t | check | status   | OK       |
+--------------+-------+----------+----------+
1 row in set (0.02 sec)

收集统计信息
mysql> alter table list_t analyze partition p1;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| skate.list_t | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.01 sec)

mysql>

清空表
mysql> alter table list_t truncate partition p1;
Query OK, 0 rows affected (0.04 sec)

删除分区表
mysql> alter table list_t drop partition p1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

重命名分区
-------------------------------------------------
mysql> show create table list_t;
 CREATE TABLE `list_t` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (c1)
(PARTITION p0 VALUES IN (1,4,7) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (3,6,9) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (2,5,8) ENGINE = InnoDB) */

mysql> alter table list_t  reorganize partition p0,p2 into ( partition p0 values in (1,4), partition p2 values in(7,3,6,9));
Query OK, 5 rows affected (0.26 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> show create table list_t;
CREATE TABLE `list_t` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (c1)
(PARTITION p0 VALUES IN (1,4) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (7,3,6,9) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (2,5,8) ENGINE = InnoDB) */

---------------------------------------------------

 

 

-----end----