Movatterモバイル変換

List partitioning in MySQL is similar to range partitioning in many ways. As in partitioning byRANGE, each partition must be explicitly defined. The chief difference between the two types of partitioning is that, in list partitioning, each partition is defined and selected based on the membership of a column value in one of a set of value lists, rather than in one of a set of contiguous ranges of values. This is done by usingPARTITION BY LIST(expr) whereexpr is a column value or an expression based on a column value and returning an integer value, and then defining each partition by means of aVALUES IN (value_list), wherevalue_list is a comma-separated list of integers.

Unlike the case with partitions defined by range, list partitions do not need to be declared in any particular order. For more detailed syntactical information, seeSection 13.1.18, “CREATE TABLE Statement”.

For the examples that follow, we assume that the basic definition of the table to be partitioned is provided by theCREATE TABLE statement shown here:

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT '1970-01-01',    separated DATE NOT NULL DEFAULT '9999-12-31',    job_code INT,    store_id INT);

(This is the same table used as a basis for the examples inSection 22.2.1, “RANGE Partitioning”.)

Suppose that there are 20 video stores distributed among 4 franchises as shown in the following table.

To partition this table in such a way that rows for stores belonging to the same region are stored in the same partition, you could use theCREATE TABLE statement shown here:

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT '1970-01-01',    separated DATE NOT NULL DEFAULT '9999-12-31',    job_code INT,    store_id INT)PARTITION BY LIST(store_id) (    PARTITION pNorth VALUES IN (3,5,6,9,17),    PARTITION pEast VALUES IN (1,2,10,11,19,20),    PARTITION pWest VALUES IN (4,12,13,14,18),    PARTITION pCentral VALUES IN (7,8,15,16));

This makes it easy to add or drop employee records relating to specific regions to or from the table. For instance, suppose that all stores in the West region are sold to another company. In MySQL 5.7, all rows relating to employees working at stores in that region can be deleted with the queryALTER TABLE employees TRUNCATE PARTITION pWest, which can be executed much more efficiently than the equivalentDELETE statementDELETE FROM employees WHERE store_id IN (4,12,13,14,18);. (UsingALTER TABLE employees DROP PARTITION pWest would also delete all of these rows, but would also remove the partitionpWest from the definition of the table; you would need to use anALTER TABLE ... ADD PARTITION statement to restore the table's original partitioning scheme.)

As withRANGE partitioning, it is possible to combineLIST partitioning with partitioning by hash or key to produce a composite partitioning (subpartitioning). SeeSection 22.2.6, “Subpartitioning”.

Unlike the case withRANGE partitioning, there is no“catch-all” such asMAXVALUE; all expected values for the partitioning expression should be covered inPARTITION ... VALUES IN (...) clauses. AnINSERT statement containing an unmatched partitioning column value fails with an error, as shown in this example:

mysql> CREATE TABLE h2 (    ->   c1 INT,    ->   c2 INT    -> )    -> PARTITION BY LIST(c1) (    ->   PARTITION p0 VALUES IN (1, 4, 7),    ->   PARTITION p1 VALUES IN (2, 5, 8)    -> );Query OK, 0 rows affected (0.11 sec)mysql> INSERT INTO h2 VALUES (3, 5);ERROR 1525 (HY000): Table has no partition for value 3

When inserting multiple rows using a singleINSERT statement the behavior depends on whether the table uses a transactional storage engine. For anInnoDB table, the statement is considered a single transaction, so the presence of any unmatched values causes the statement to fail completely, and no rows are inserted. For a table using a nontransactional storage engine such asMyISAM, any rows coming before the row containing the unmatched value are inserted, but any coming after it are not.

You can cause this type of error to be ignored by using theIGNORE keyword, although a warning is issued for each row containing unmatched partitioning column values, as shown here.

mysql> TRUNCATE h2;Query OK, 1 row affected (0.00 sec)mysql> TABLE h2;Empty set (0.00 sec)mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);Query OK, 3 rows affected, 2 warnings (0.01 sec)Records: 5  Duplicates: 2  Warnings: 2mysql> SHOW WARNINGS;+---------+------+------------------------------------+| Level   | Code | Message                            |+---------+------+------------------------------------+| Warning | 1526 | Table has no partition for value 6 || Warning | 1526 | Table has no partition for value 3 |+---------+------+------------------------------------+2 rows in set (0.00 sec)

You can see in the output of the followingTABLE statement that rows containing unmatched partitioning column values were silently rejected, while rows containing no unmatched values were inserted into the table:

mysql> TABLE h2;+------+------+| c1   | c2   |+------+------+|    7 |    5 ||    1 |    9 ||    2 |    5 |+------+------+3 rows in set (0.00 sec)

MySQL also provides support forLIST COLUMNS partitioning, a variant ofLIST partitioning that enables you to use columns of types other than integer for partitioning columns, and to use multiple columns as partitioning keys. For more information, seeSection 22.2.3.2, “LIST COLUMNS partitioning”.