This section discusses the types of partitioning which are
available in MySQL 5.1. These include:
RANGE
partitioning: Assigns rows to partitions based on
column values falling within a given range. See
Section 17.2.1, “RANGE Partitioning”.
LIST
partitioning: Similar to partitioning by range,
except that the partition is selected based on columns
matching one of a set of discrete values. See
Section 17.2.2, “LIST Partitioning”.
HASH
partitioning: A partition is selected based on the
value returned by a user-defined expression that operates on
column values in rows to be inserted into the table. The
function may consist of any expression valid in MySQL that
yields a non-negative integer value. See
Section 17.2.3, “HASH Partitioning”.
KEY
partitioning: Similar to partitioning by hash,
except that only one or more columns to be evaluated are
supplied, and the MySQL server provides its own hashing
function. The column or columns must contain only integer
values. See Section 17.2.4, “KEY Partitioning”.
It is important to remember — regardless of the type of
partitioning that you use — that partitions are always
numbered automatically and in sequence when created, starting with
0. When a new row is inserted into a
partitioned table, it is these partition numbers that are used in
identifying the correct partition. For example, if your table uses
4 partitions, these partitions are numbered 0,
1, 2, and
3. For the RANGE and
LIST partitioning types, it is necessary to
ensure that there is a partition defined for each partition
number. For HASH partitioning, the user
function employed must return an integer value greater than
0. For KEY partitioning,
this issue is taken care of automatically by the hashing function
which the MySQL server employs internally.
Names of partitions generally follow the rules governing other
MySQL identifiers, such as those for tables and databases.
However, you should note that partition names are not
case-sensitive. For example, the following CREATE
TABLE statement fails as shown:
mysql> CREATE TABLE t2 (val INT)
-> PARTITION BY LIST(val)(
-> PARTITION mypart VALUES IN (1,3,5),
-> PARTITION MyPart VALUES IN (2,4,6)
-> );
ERROR 1488 (HY000): All partitions must have unique names in the table
Failure occurs because MySQL sees no difference between the
partition names mypart and
MyPart.
In the sections that follow, we do not necessarily provide all
possible forms for the syntax that can be used for creating each
partition type; this information may be found in
Section 13.1.5, “CREATE TABLE Syntax”.
