Movatterモバイル変換

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, seeSection 22.3.2, “Management of HASH and KEY Partitions”.

Dropping a partition from a table that is partitioned by eitherRANGE or byLIST can be accomplished using theALTER TABLE statement with theDROP PARTITION option. Suppose that you have created a table that is partitioned by range and then populated with 10 records using the followingCREATE TABLE andINSERT 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 partitionp2 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)

SeeSection 22.5, “Partition Selection”, for more information.

To drop the partition namedp2, execute the following command:

mysql> ALTER TABLE tr DROP PARTITION p2;Query OK, 0 rows affected (0.03 sec)

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 previousSELECT query:

mysql> SELECT * FROM tr WHERE purchased    -> BETWEEN '1995-01-01' AND '1999-12-31';Empty set (0.00 sec)

Because of this, you must have theDROP privilege for a table before you can executeALTER 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 theTRUNCATE TABLE statement. (SeeSection 13.1.34, “TRUNCATE TABLE Statement”.)

If you intend to change the partitioning of a tablewithout losing data, useALTER TABLE ... REORGANIZE PARTITION instead. See below or inSection 13.1.8, “ALTER TABLE Statement”, for information aboutREORGANIZE PARTITION.

If you now execute aSHOW 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: trCreate Table: CREATE TABLE `tr` (  `id` int(11) DEFAULT NULL,  `name` varchar(50) DEFAULT NULL,  `purchased` date DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1/*!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 withpurchased column values between'1995-01-01' and'2004-12-31' inclusive, those rows are stored in partitionp3. 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 ofALTER TABLE ... DROP PARTITION is not reported by the server as it would be by the equivalentDELETE query.

DroppingLIST partitions uses exactly the sameALTER TABLE ... DROP PARTITION syntax as used for droppingRANGE 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. (SeeSection 22.2.2, “LIST Partitioning”, for an example.)

To add a new range or list partition to a previously partitioned table, use theALTER TABLE ... ADD PARTITION statement. For tables which are partitioned byRANGE, 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 are soon going to be admitting members who were born in 2000 (and later). You can modify themembers 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 useADD 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));

UsingSHOW CREATE TABLE you can see that theALTER TABLE statement has had the desired effect:

mysql> SHOW CREATE TABLE members\G*************************** 1. row ***************************       Table: membersCreate 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=latin1/*!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 alsoSection 13.1.8.1, “ALTER TABLE Partition Operations”.

You can also useALTER TABLE ... ADD PARTITION to add new partitions to a table that is partitioned byLIST. Suppose a tablett is defined using the followingCREATE 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 thedata column values7,14, and21 as shown:

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

Keep in mind that youcannot add a newLIST 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 thedata column value12 have already been assigned to partitionp1, you cannot create a new partition on tablett that includes12 in its value list. To accomplish this, you could dropp1, and addnp and then a newp1 with a modified definition. However, as discussed earlier, this would result in the loss of all data stored inp1—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 usingCREATE 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 singleALTER 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 involvingRANGE partitioning. Recall themembers table which is now defined as shown here:

mysql> SHOW CREATE TABLE members\G*************************** 1. row ***************************       Table: membersCreate 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=latin1/*!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 usingALTER TABLE ... ADD PARTITION. However, you can use another partition-related extension toALTER 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 partitionn0 into two new partitionss0 ands1. It also moves the data that was stored inn0 into the new partitions according to the rules embodied in the twoPARTITION ... VALUES ... clauses, so thats0 contains only those records for whichYEAR(dob) is less than 1960 ands1 contains those rows in whichYEAR(dob) is greater than or equal to 1960 but less than 1970.

AREORGANIZE PARTITION clause may also be used for merging adjacent partitions. You can reverse the effect of the previous statement on themembers 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 usingREORGANIZE PARTITION. In executing the above statement, MySQL moves all of the records that were stored in partitionss0 ands1 into partitionp0.

The general syntax forREORGANIZE PARTITION is shown here:

ALTER TABLEtbl_name    REORGANIZE PARTITIONpartition_list    INTO (partition_definitions);

Here,tbl_name is the name of the partitioned table, andpartition_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 thepartition_definitions list used inCREATE TABLE. You are not limited to merging several partitions into one, or to splitting one partition into many, when usingREORGANIZE PARTITION. For example, you can reorganize all four partitions of themembers 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 useREORGANIZE PARTITION with tables that are partitioned byLIST. Let us return to the problem of adding a new partition to the list-partitionedtt 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 usingALTER TABLE ... REORGANIZE PARTITION to repartition tables that are partitioned byRANGE orLIST: