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.01sec)
mysql> ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
Query OK,0 rows affected (0.15sec)
Records:0 Duplicates: 0 Warnings: 0mysql>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.00sec)
mysql>ALTER TABLE tt REORGANIZE PARTITION p1,np INTO-> ( PARTITION p1 VALUES IN (6, 18), PARTITION np VALUES in (4, 8, 12) ); );
ERROR1519 (HY000): When reorganizing a set of partitions they must be inconsecutive order
ERROR1064 (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 1mysql> insert into tt values(1,10),(2,5);
Query OK,2 rows affected (0.03sec)
Records:2 Duplicates: 0 Warnings: 0mysql> select * fromtt;+------+------+
| id | data |
+------+------+
| 1 | 10 |
| 2 | 5 |
+------+------+
2 rows in set (0.00sec)
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.00sec)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.34sec)
Records:0 Duplicates: 0 Warnings: 0