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)
转载