MySQL分区表例子——List分区

列表分区(List分区)

这里假设表中有一个sale_item_type 字段，数据类型为INT 型

当sale_item_type 为1，3，5的时候，作为一个分区

当sale_item_type 为2，4，6的时候，作为另一个分区

当sale_item_type 为7，8的时候，作为最后一个分区

初期分区定义

mysql> CREATE TABLE sale_data2 (

->   sale_date  DATETIME NOT NULL,

->   sale_item_type  INT NOT NULL ,

->   sale_money DECIMAL(10,2) NOT NULL

-> )

-> PARTITION BY LIST (sale_item_type) (

->   PARTITION pFoods VALUES IN (1,3,5),

->   PARTITION pSmoke VALUES IN (2,4,6),

->   PARTITION pDrink VALUES IN (7,8)

-> );

Query OK, 0 rows affected (0.11 sec)

核对结果

mysql> SHOW CREATE TABLE sale_data2\G

*************************** 1. row ********************

Table: sale_data2

Create Table: CREATE TABLE `sale_data2` (

`sale_date` datetime NOT NULL,

`sale_item_type` int(11) NOT NULL,

`sale_money` decimal(10,2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY LIST (sale_item_type)

(PARTITION pFoods VALUES IN (1,3,5) ENGINE = InnoDB,

PARTITION pSmoke VALUES IN (2,4,6) ENGINE = InnoDB,

PARTITION pDrink VALUES IN (7,8) ENGINE = InnoDB) */

1 row in set (0.00 sec)

新增分区

mysql> ALTER TABLE sale_data2

->   ADD PARTITION (PARTITION pOther VALUES IN (9, 10));

Query OK, 0 rows affected (0.23 sec)

Records: 0  Duplicates: 0  Warnings: 0

核对结果

mysql> SHOW CREATE TABLE sale_data2\G

*************************** 1. row ***************************

Table: sale_data2

Create Table: CREATE TABLE `sale_data2` (

`sale_date` datetime NOT NULL,

`sale_item_type` int(11) NOT NULL,

`sale_money` decimal(10,2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY LIST (sale_item_type)

(PARTITION pFoods VALUES IN (1,3,5) ENGINE = InnoDB,

PARTITION pSmoke VALUES IN (2,4,6) ENGINE = InnoDB,

PARTITION pDrink VALUES IN (7,8) ENGINE = InnoDB,

PARTITION pOther VALUES IN (9,10) ENGINE = InnoDB) */

1 row in set (0.00 sec)

删除分区

mysql> ALTER TABLE sale_data2

->   DROP PARTITION pOther;

Query OK, 0 rows affected (0.34 sec)

Records: 0  Duplicates: 0  Warnings: 0

核对结果

mysql> SHOW CREATE TABLE sale_data2\G

*************************** 1. row ***************************

Table: sale_data2

Create Table: CREATE TABLE `sale_data2` (

`sale_date` datetime NOT NULL,

`sale_item_type` int(11) NOT NULL,

`sale_money` decimal(10,2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY LIST (sale_item_type)

(PARTITION pFoods VALUES IN (1,3,5) ENGINE = InnoDB,

PARTITION pSmoke VALUES IN (2,4,6) ENGINE = InnoDB,

PARTITION pDrink VALUES IN (7,8) ENGINE = InnoDB) */

1 row in set (0.00 sec)

分区的拆分

下面的SQL将pDrink 拆分为pBeer 与pCola

mysql> ALTER TABLE sale_data2 REORGANIZE PARTITION pDrink INTO (

->     PARTITION pBeer VALUES IN (7),

->     PARTITION pCola VALUES IN (8)

-> );

Query OK, 0 rows affected (0.39 sec)

Records: 0  Duplicates: 0  Warnings: 0

核对结果

mysql> SHOW CREATE TABLE sale_data2\G

*************************** 1. row ***************************

Table: sale_data2

Create Table: CREATE TABLE `sale_data2` (

`sale_date` datetime NOT NULL,

`sale_item_type` int(11) NOT NULL,

`sale_money` decimal(10,2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY LIST (sale_item_type)

(PARTITION pFoods VALUES IN (1,3,5) ENGINE = InnoDB,

PARTITION pSmoke VALUES IN (2,4,6) ENGINE = InnoDB,

PARTITION pBeer VALUES IN (7) ENGINE = InnoDB,

PARTITION pCola VALUES IN (8) ENGINE = InnoDB) */

1 row in set (0.00 sec)

分区的合并

下面的SQL，将pBeer,pCola合并到pDrink

mysql> ALTER TABLE sale_data2

->   REORGANIZE PARTITION pBeer,pCola INTO (

->     PARTITION pDrink VALUES IN (7,8)

-> );

Query OK, 0 rows affected (0.36 sec)

Records: 0  Duplicates: 0  Warnings: 0

核对结果

mysql> SHOW CREATE TABLE sale_data2\G

*************************** 1. row ***********************

Table: sale_data2

Create Table: CREATE TABLE `sale_data2` (

`sale_date` datetime NOT NULL,

`sale_item_type` int(11) NOT NULL,

`sale_money` decimal(10,2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY LIST (sale_item_type)

(PARTITION pFoods VALUES IN (1,3,5) ENGINE = InnoDB,

PARTITION pSmoke VALUES IN (2,4,6) ENGINE = InnoDB,

PARTITION pDrink VALUES IN (7,8) ENGINE = InnoDB) */

1 row in set (0.00 sec)