[+/-]
This section discusses the types of partitioning which are available in MySQL 5.5. These include:
RANGE
partitioning.
This type of partitioning assigns rows to partitions based
on column values falling within a given range. MySQL 5.5
adds an extension, RANGE COLUMNS
, to this
type. See Section 17.2.3.1, “Range columns 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. MySQL 5.5 adds an extension,
LIST COLUMNS
, to this type. See
Section 17.2.3.2, “List columns partitioning”.
HASH
partitioning.
With this type of 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 nonnegative integer value. An
extension to this type, LINEAR HASH
, is
also available. See Section 17.2.4, “HASH
Partitioning”.
KEY
partitioning.
This type of partitioning is 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. These columns can contain
other than integer values, since the hashing function
supplied by MySQL guarantees an integer result regardless of
the column data type. An extension to this type,
LINEAR KEY
, is also available. See
Section 17.2.5, “KEY
Partitioning”.
A very common use of database partitioning is to segregate data by
date. It is not difficult in MySQL to create partitioning schemes
based on DATE
,
TIME
, or
DATETIME
columns, or based on
expressions making use of such columns.
When partitioning by KEY
or LINEAR
KEY
, you can use a DATE
,
TIME
, or
DATETIME
column as the partitioning
column without performing any modification of the column value.
For example, this table creation statement is perfectly valid in
MySQL:
CREATE TABLE members ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, username VARCHAR(16) NOT NULL, email VARCHAR(35), joined DATE NOT NULL ) PARTITION BY KEY(joined) PARTITIONS 6;
Beginning with MySQL 5.5.0, it is also possible to use a
DATE
or
DATETIME
column as the partitioning
column using RANGE COLUMNS
and LIST
COLUMNS
partitioning.
MySQL's other partitioning types, however, require a partitioning
expression that yields an integer value or
NULL
.
Additional examples of partitioning using dates may be found here:
For more complex examples of date-based partitioning, see:
MySQL partitioning is optimized for use with the
TO_DAYS()
,
YEAR()
, and (in MySQL 5.5.0 and
later) TO_SECONDS()
functions.
However, you can use other date and time functions that return an
integer or NULL
, such as
WEEKDAY()
,
DAYOFYEAR()
, or
MONTH()
. See
Section 11.6, “Date and Time Functions”, for more information
about such functions.
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): Duplicate partition name mypart
Failure occurs because MySQL sees no difference between the
partition names mypart
and
MyPart
.
When you specify the number of partitions for the table, this must
be expressed as a positive, nonzero integer literal with no
leading zeroes, and may not be an expression such as
0.8E+01
or 6-2
, even if it
evaluates to an integer value. Decimal fractions are not allowed.
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 12.1.14, “CREATE TABLE
Syntax”.
User Comments
Add your own comment.