Adding and dropping of range and list partitions are handled in a similar fashion, so we discuss the management of both sorts of partitioning in this section. For information about working with tables that are partitioned by hash or key, see Section 26.3.2, “Management of HASH and KEY Partitions”.
Dropping a partition from a table that is partitioned by either
RANGE
or by LIST
can be
accomplished using the
ALTER
TABLE
statement with the DROP
PARTITION
option. Suppose that you have created a
table that is partitioned by range and then populated with 10
records using the following CREATE
TABLE
and INSERT
statements:
mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990),
-> PARTITION p1 VALUES LESS THAN (1995),
-> PARTITION p2 VALUES LESS THAN (2000),
-> PARTITION p3 VALUES LESS THAN (2005),
-> PARTITION p4 VALUES LESS THAN (2010),
-> PARTITION p5 VALUES LESS THAN (2015)
-> );
Query OK, 0 rows affected (0.28 sec)
mysql> INSERT INTO tr VALUES
-> (1, 'desk organiser', '2003-10-15'),
-> (2, 'alarm clock', '1997-11-05'),
-> (3, 'chair', '2009-03-10'),
-> (4, 'bookcase', '1989-01-10'),
-> (5, 'exercise bike', '2014-05-09'),
-> (6, 'sofa', '1987-06-05'),
-> (7, 'espresso maker', '2011-11-22'),
-> (8, 'aquarium', '1992-08-04'),
-> (9, 'study desk', '2006-09-16'),
-> (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.05 sec)
Records: 10 Duplicates: 0 Warnings: 0
You can see which items should have been inserted into partition
p2
as shown here:
mysql> SELECT * FROM tr
-> WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-------------+------------+
| id | name | purchased |
+------+-------------+------------+
| 2 | alarm clock | 1997-11-05 |
| 10 | lava lamp | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)
You can also get this information using partition selection, as shown here:
mysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id | name | purchased |
+------+-------------+------------+
| 2 | alarm clock | 1997-11-05 |
| 10 | lava lamp | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)
See Section 26.5, “Partition Selection”, for more information.
To drop the partition named p2
, execute the
following command:
mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)
The NDBCLUSTER
storage engine
does not support ALTER TABLE ... DROP
PARTITION
. It does, however, support the other
partitioning-related extensions to
ALTER
TABLE
that are described in this chapter.
It is very important to remember that, when you drop a
partition, you also delete all the data that was stored in that
partition. You can see that this is the case by
re-running the previous SELECT
query:
mysql> SELECT * FROM tr WHERE purchased
-> BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)
DROP PARTITION
is supported by native
partitioning in-place APIs and may be used with
ALGORITHM={COPY|INPLACE}
. DROP
PARTITION
with ALGORITHM=INPLACE
deletes data stored in the partition and drops the partition.
However, DROP PARTITION
with
ALGORITHM=COPY
or
old_alter_table=ON
rebuilds
the partitioned table and attempts to move data from the
dropped partition to another partition with a compatible
PARTITION ... VALUES
definition. Data that
cannot be moved to another partition is deleted.
Because of this, you must have the
DROP
privilege for a table before
you can execute ALTER TABLE ... DROP
PARTITION
on that table.
If you wish to drop all data from all partitions while
preserving the table definition and its partitioning scheme, use
the TRUNCATE TABLE
statement.
(See Section 15.1.37, “TRUNCATE TABLE Statement”.)
If you intend to change the partitioning of a table
without losing data, use ALTER
TABLE ... REORGANIZE PARTITION
instead. See below or
in Section 15.1.9, “ALTER TABLE Statement”, for information about
REORGANIZE PARTITION
.
If you now execute a SHOW CREATE
TABLE
statement, you can see how the partitioning
makeup of the table has been changed:
mysql> SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
Table: tr
Create Table: CREATE TABLE `tr` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */
1 row in set (0.00 sec)
When you insert new rows into the changed table with
purchased
column values between
'1995-01-01'
and
'2004-12-31'
inclusive, those rows are stored
in partition p3
. You can verify this as
follows:
mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tr WHERE purchased
-> BETWEEN '1995-01-01' AND '2004-12-31';
+------+----------------+------------+
| id | name | purchased |
+------+----------------+------------+
| 1 | desk organiser | 2003-10-15 |
| 11 | pencil holder | 1995-07-12 |
+------+----------------+------------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE tr DROP PARTITION p3;
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT * FROM tr WHERE purchased
-> BETWEEN '1995-01-01' AND '2004-12-31';
Empty set (0.00 sec)
The number of rows dropped from the table as a result of
ALTER TABLE ... DROP PARTITION
is not
reported by the server as it would be by the equivalent
DELETE
query.
Dropping LIST
partitions uses exactly the
same ALTER TABLE ... DROP PARTITION
syntax as
used for dropping RANGE
partitions. However,
there is one important difference in the effect this has on your
use of the table afterward: You can no longer insert into the
table any rows having any of the values that were included in
the value list defining the deleted partition. (See
Section 26.2.2, “LIST Partitioning”, for an example.)
To add a new range or list partition to a previously partitioned
table, use the ALTER TABLE ... ADD PARTITION
statement. For tables which are partitioned by
RANGE
, this can be used to add a new range to
the end of the list of existing partitions. Suppose that you
have a partitioned table containing membership data for your
organization, which is defined as follows:
CREATE TABLE members (
id INT,
fname VARCHAR(25),
lname VARCHAR(25),
dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
PARTITION p0 VALUES LESS THAN (1980),
PARTITION p1 VALUES LESS THAN (1990),
PARTITION p2 VALUES LESS THAN (2000)
);
Suppose further that the minimum age for members is 16. As the
calendar approaches the end of 2015, you realize that you must
soon be prepared to admit members who were born in 2000 (and
later). You can modify the members
table to
accommodate new members born in the years 2000 to 2010 as shown
here:
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
With tables that are partitioned by range, you can use
ADD PARTITION
to add new partitions to the
high end of the partitions list only. Trying to add a new
partition in this manner between or before existing partitions
results in an error as shown here:
mysql> ALTER TABLE members
> ADD PARTITION (
> PARTITION n VALUES LESS THAN (1970));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
increasing for each partition
You can work around this problem by reorganizing the first partition into two new ones that split the range between them, like this:
ALTER TABLE members
REORGANIZE PARTITION p0 INTO (
PARTITION n0 VALUES LESS THAN (1970),
PARTITION n1 VALUES LESS THAN (1980)
);
Using SHOW CREATE TABLE
you can
see that the ALTER TABLE
statement has had
the desired effect:
mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
Table: members
Create Table: 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 n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
PARTITION n1 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)
See also Section 15.1.9.1, “ALTER TABLE Partition Operations”.
You can also use ALTER TABLE ... ADD
PARTITION
to add new partitions to a table that is
partitioned by LIST
. Suppose a table
tt
is defined using the following
CREATE TABLE
statement:
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)
);
You can add a new partition in which to store rows having the
data
column values 7
,
14
, and 21
as shown:
ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));
Keep in mind that you cannot add a new
LIST
partition encompassing any values that
are already included in the value list of an existing partition.
If you attempt to do so, an error results:
mysql> ALTER TABLE tt ADD PARTITION
> (PARTITION np VALUES IN (4, 8, 12));
ERROR 1465 (HY000): Multiple definition of same constant »
in list partitioning
Because any rows with the data
column value
12
have already been assigned to partition
p1
, you cannot create a new partition on
table tt
that includes 12
in its value list. To accomplish this, you could drop
p1
, and add np
and then a
new p1
with a modified definition. However,
as discussed earlier, this would result in the loss of all data
stored in p1
—and it is often the case
that this is not what you really want to do. Another solution
might appear to be to make a copy of the table with the new
partitioning and to copy the data into it using
CREATE TABLE ...
SELECT ...
, then drop the old table and rename the new
one, but this could be very time-consuming when dealing with a
large amounts of data. This also might not be feasible in
situations where high availability is a requirement.
You can add multiple partitions in a single ALTER TABLE
... ADD PARTITION
statement as shown here:
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
hired DATE NOT NULL
)
PARTITION BY RANGE( YEAR(hired) ) (
PARTITION p1 VALUES LESS THAN (1991),
PARTITION p2 VALUES LESS THAN (1996),
PARTITION p3 VALUES LESS THAN (2001),
PARTITION p4 VALUES LESS THAN (2005)
);
ALTER TABLE employees ADD PARTITION (
PARTITION p5 VALUES LESS THAN (2010),
PARTITION p6 VALUES LESS THAN MAXVALUE
);
Fortunately, MySQL's partitioning implementation provides ways
to redefine partitions without losing data. Let us look first at
a couple of simple examples involving RANGE
partitioning. Recall the members
table which
is now defined as shown here:
mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
Table: members
Create Table: 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 n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
PARTITION n1 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)
Suppose that you would like to move all rows representing
members born before 1960 into a separate partition. As we have
already seen, this cannot be done using
ALTER
TABLE ... ADD PARTITION
. However, you can use another
partition-related extension to
ALTER
TABLE
to accomplish this:
ALTER TABLE members REORGANIZE PARTITION n0 INTO (
PARTITION s0 VALUES LESS THAN (1960),
PARTITION s1 VALUES LESS THAN (1970)
);
In effect, this command splits partition p0
into two new partitions s0
and
s1
. It also moves the data that was stored in
p0
into the new partitions according to the
rules embodied in the two PARTITION ... VALUES
...
clauses, so that s0
contains
only those records for which
YEAR(dob)
is less than 1960 and
s1
contains those rows in which
YEAR(dob)
is greater than or
equal to 1960 but less than 1970.
A REORGANIZE PARTITION
clause may also be
used for merging adjacent partitions. You can reverse the effect
of the previous statement on the members
table as shown here:
ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
PARTITION p0 VALUES LESS THAN (1970)
);
No data is lost in splitting or merging partitions using
REORGANIZE PARTITION
. In executing the above
statement, MySQL moves all of the records that were stored in
partitions s0
and s1
into
partition p0
.
The general syntax for REORGANIZE PARTITION
is shown here:
ALTER TABLE tbl_name
REORGANIZE PARTITION partition_list
INTO (partition_definitions);
Here, tbl_name
is the name of the
partitioned table, and partition_list
is a comma-separated list of names of one or more existing
partitions to be changed.
partition_definitions
is a
comma-separated list of new partition definitions, which follow
the same rules as for the
partition_definitions
list used in
CREATE TABLE
. You are not limited
to merging several partitions into one, or to splitting one
partition into many, when using REORGANIZE
PARTITION
. For example, you can reorganize all four
partitions of the members
table into two,
like this:
ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
PARTITION m0 VALUES LESS THAN (1980),
PARTITION m1 VALUES LESS THAN (2000)
);
You can also use REORGANIZE PARTITION
with
tables that are partitioned by LIST
. Let us
return to the problem of adding a new partition to the
list-partitioned tt
table and failing because
the new partition had a value that was already present in the
value-list of one of the existing partitions. We can handle this
by adding a partition that contains only nonconflicting values,
and then reorganizing the new partition and the existing one so
that the value which was stored in the existing one is now moved
to the new one:
ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
PARTITION p1 VALUES IN (6, 18),
PARTITION np VALUES in (4, 8, 12)
);
Here are some key points to keep in mind when using
ALTER TABLE ... REORGANIZE PARTITION
to
repartition tables that are partitioned by
RANGE
or LIST
:
The
PARTITION
options used to determine the new partitioning scheme are subject to the same rules as those used with aCREATE TABLE
statement.A new
RANGE
partitioning scheme cannot have any overlapping ranges; a newLIST
partitioning scheme cannot have any overlapping sets of values.The combination of partitions in the
partition_definitions
list should account for the same range or set of values overall as the combined partitions named in thepartition_list
.For example, partitions
p1
andp2
together cover the years 1980 through 1999 in themembers
table used as an example in this section. Any reorganization of these two partitions should cover the same range of years overall.For tables partitioned by
RANGE
, you can reorganize only adjacent partitions; you cannot skip range partitions.For instance, you could not reorganize the example
members
table using a statement beginning withALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ...
becausep0
covers the years prior to 1970 andp2
the years from 1990 through 1999 inclusive, so these are not adjacent partitions. (You cannot skip partitionp1
in this case.)You cannot use
REORGANIZE PARTITION
to change the type of partitioning used by the table (for example, you cannot changeRANGE
partitions toHASH
partitions or the reverse). You also cannot use this statement to change the partitioning expression or column. To accomplish either of these tasks without dropping and re-creating the table, you can useALTER TABLE ... PARTITION BY ...
, as shown here:ALTER TABLE members PARTITION BY HASH( YEAR(dob) ) PARTITIONS 8;