MySQL Partition Table-list Partition

Author: skate
Time: 2012/11/28

MySQL partition --- list Partition

 

The list partitions of MySQL are very similar to the range partitions, except that the range of list partitions is a list, which is defined similarly by partition by list (expr, expr is an expression that returns interger based on one or more columns.

 

List partition Definition
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

Clear table
Mysql> truncate table list_t;
Query OK, 0 rows affected (0.12 Sec)

Ignore unmatched rows
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>

Add Partition
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 Partition
Mysql> alter table list_t rebuild partition P1;
Query OK, 0 rows affected (0.12 Sec)
Records: 0 duplicates: 0 Warnings: 0

Check partition tables
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)

Collect statistics
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>

Clear table
Mysql> alter table list_t truncate partition P1;
Query OK, 0 rows affected (0.04 Sec)

Delete a partition table
Mysql> alter table list_t drop partition P1;
Query OK, 0 rows affected (0.03 Sec)
Records: 0 duplicates: 0 Warnings: 0

Mysql>

Rename a partition
-------------------------------------------------
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 (), partition P2 values in ));
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 ----