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)