Rechercher dans le manuel MySQL
23.2.2 LIST Partitioning
List partitioning in MySQL is similar to range partitioning in
many ways. As in partitioning by RANGE
, 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 using PARTITION BY
LIST(
where
expr
)expr
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 a
VALUES IN
(
, where
value_list
)value_list
is a comma-separated list
of integers.
In MySQL 8.0, it is possible to match against
only a list of integers (and possibly
NULL
—see
Section 23.2.7, “How MySQL Partitioning Handles NULL”) when
partitioning by LIST
.
However, other column types may be used in value lists when
employing LIST COLUMN
partitioning, which
is described later in this section.
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, see Section 13.1.20, “CREATE TABLE Syntax”.
For the examples that follow, we assume that the basic
definition of the table to be partitioned is provided by the
CREATE TABLE
statement shown
here:
(This is the same table used as a basis for the examples in
Section 23.2.1, “RANGE Partitioning”. As with the other
partitioning examples, we assume that the
default_storage_engine
is
InnoDB
.)
Suppose that there are 20 video stores distributed among 4 franchises as shown in the following table.
Region | Store ID Numbers |
---|---|
North | 3, 5, 6, 9, 17 |
East | 1, 2, 10, 11, 19, 20 |
West | 4, 12, 13, 14, 18 |
Central | 7, 8, 15, 16 |
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 the CREATE TABLE
statement shown here:
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 8.0, all rows relating to employees
working at stores in that region can be deleted with the query
ALTER TABLE employees TRUNCATE PARTITION
pWest
, which can be executed much more efficiently
than the equivalent DELETE
statement DELETE FROM employees WHERE store_id IN
(4,12,13,14,18);
. (Using ALTER TABLE
employees DROP PARTITION pWest
would also delete all
of these rows, but would also remove the partition
pWest
from the definition of the table; you
would need to use an ALTER TABLE ... ADD
PARTITION
statement to restore the table's
original partitioning scheme.)
As with RANGE
partitioning, it is possible to
combine LIST
partitioning with partitioning
by hash or key to produce a composite partitioning
(subpartitioning). See
Section 23.2.6, “Subpartitioning”.
Unlike the case with RANGE
partitioning,
there is no “catch-all” such as
MAXVALUE
; all expected values for the
partitioning expression should be covered in PARTITION
... VALUES IN (...)
clauses. An
INSERT
statement containing an
unmatched partitioning column value fails with an error, as
shown in this example:
- -> c2 INT
- -> )
- -> );
- Query OK, 0 rows affected (0.11 sec)
When inserting multiple rows using a single
INSERT
statement into a single
InnoDB
table,
InnoDB
considers the statement a single
transaction, so that the presence of any unmatched values causes
the statement to fail completely, and so no rows are inserted.
You can cause this type of error to be ignored by using the
IGNORE
keyword. If you do so, rows containing
unmatched partitioning column values are not inserted, but any
rows with matching values are inserted, and
no errors are reported:
- Query OK, 1 row affected (0.00 sec)
- Query OK, 3 rows affected (0.00 sec)
- +------+------+
- | c1 | c2 |
- +------+------+
- | 7 | 5 |
- | 1 | 9 |
- | 2 | 5 |
- +------+------+
MySQL 8.0 also provides support for LIST
COLUMNS
partitioning, a variant of
LIST
partitioning that enables you to use
columns of types other than integer types for partitioning
columns, and to use multiple columns as partitioning keys. For
more information, see
Section 23.2.3.2, “LIST COLUMNS partitioning”.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-partitioning-list.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.