MySQL 表分区（八）

• 对列表表分区来说，只要新增加的分区对应的值在之前的表分区中没有出现过，就可以通过 alter table… add partition 来增加

mysql> CREATE TABLE tt ( id INT, data INT ) PARTITION BY LIST(data) 
    -> ( PARTITION p0 VALUES IN (5, 10, 15), 
    -> PARTITION p1 VALUES IN (6, 12, 18) ); 
Query OK, 0 rows affected (0.27 sec)

mysql> ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tt;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt    | CREATE TABLE `tt` (
  `id` int(11) DEFAULT NULL,
  `data` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (`data`)
(PARTITION p0 VALUES IN (5,10,15) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (6,12,18) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (7,14,21) ENGINE = InnoDB) */ |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE members REORGANIZE PARTITION n0,n1 INTO ( PARTITION p0 VALUES LESS  THAN (1980) );
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table members;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| members | CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`dob`))
(PARTITION p0 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */ |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO ( PARTITION m0 VALUES LESS 
    -> THAN (1980), PARTITION m1 VALUES LESS THAN (2010)); 
Query OK, 0 rows affected (0.72 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table members;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                          |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| members | CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`dob`))
(PARTITION m0 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION m1 VALUES LESS THAN (2010) ENGINE = InnoDB) */ |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from members;
+------+-------+-------+------------+
| id   | fname | lname | dob        |
+------+-------+-------+------------+
|    1 | a     | a     | 1967-01-01 |
|    1 | a     | a     | 1977-01-01 |
+------+-------+-------+------------+
2 rows in set (0.01 sec)

mysql> show create table tt;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt    | CREATE TABLE `tt` (
  `id` int(11) DEFAULT NULL,
  `data` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (`data`)
(PARTITION p0 VALUES IN (5,10,15) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (6,12,18) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (7,14,21) ENGINE = InnoDB) */ |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tt;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt    | CREATE TABLE `tt` (
  `id` int(11) DEFAULT NULL,
  `data` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (`data`)
(PARTITION p0 VALUES IN (5,10,15) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (6,12,18) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (7,14,21) ENGINE = InnoDB,
 PARTITION np VALUES IN (4,8) ENGINE = InnoDB) */ |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE tt REORGANIZE PARTITION p1,np INTO 
    -> ( PARTITION p1 VALUES IN (6, 18), PARTITION np VALUES in (4, 8, 12) ); ); 
ERROR 1519 (HY000): When reorganizing a set of partitions they must be in consecutive order
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
mysql> insert into tt values(1,10),(2,5);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tt;
+------+------+
| id   | data |
+------+------+
|    1 |   10 |
|    2 |    5 |
+------+------+
2 rows in set (0.00 sec)

mysql> show create table tt;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt    | CREATE TABLE `tt` (
  `id` int(11) DEFAULT NULL,
  `data` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (`data`)
(PARTITION p0 VALUES IN (5,10,15) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (6,12,18) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (7,14,21) ENGINE = InnoDB,
 PARTITION np VALUES IN (4,8) ENGINE = InnoDB) */ |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER  TABLE tt REORGANIZE PARTITION p0,p1 INTO ( PARTITION p0 VALUES IN (6, 18), PARTITION p1 VALUES in (5,15));
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0