Beginning with MySQL 5.1.12, the following constructs are not permitted in partitioning expressions:

Beginning with MySQL 5.1.12, the folowing MySQL functions are specifically not allowed in partitioning expressions:

Use of the arithmetic operators +, –, ×, and / is permitted in partitioning expressions. However, the result must be an integer value or NULL (except in the case of [LINEAR] KEY partitioning, as discussed elswhere in this chapter — see Section 16.2, “Partition Types”, for more information).

Beginning with MySQL 5.1.12, the bit operators |, &, ^, <<, >>, and ~ are not permitted in partitioning expressions.

Beginning with MySQL 5.1.12, only the following MySQL functions are specfically supported in partitioning expressions:

Important: You should keep in mind that the results of many MySQL functions and operators may change according to the server SQL mode. For this reason, it is not advisable to change this mode after creating partitioned tables. See Section 5.2.6, “SQL Modes”.

Using a function like ASCII() or ORD() to convert a string value (such as that of a CHAR or VARCHAR column) to an integer works only when the string uses an 8-bit character set. The collation used for the string can be any collation for the related character set. However, the collations latin1_german2_ci, latin2_czech_cs, and cp1250_czech_cs cannot be used, due to the fact that these collations require one-to-many character conversions.

The maximum number of partitions possible for a given table is 1024. This includes subpartitions.

If, when creating tables with a very large number of partitions (but which is less than the maxmimum stated previously), you encounter an error message such as Got error 24 from storage engine, this means that you may need to increase the value of the open_files_limit system variable. See Section B.1.2.17, “'File' Not Found and Similar Errors”.

Partitioned tables do not support foreign keys. This includes partitioned tables employing the InnoDB storage engine.

Partitioned tables do not support FULLTEXT indexes. This includes partitioned tables employing the MyISAM storage engine.

Partitioned tables do not support GEOMETRY columns.

As of MySQL 5.1.8, temporary tables cannot be partitioned. (Bug#17497)

Tables using the MERGE storage engine cannot be partitioned.

Partitioning of FEDERATED tables is not supported. Beginning with MySQL 5.1.15, it is not possible to create partitioned FEDERATED tables at all. We are working to remove this limitation in a future MySQL release.

Partitioned tables using the CSV storage engine are not supported. Starting with MySQL 5.1.12, it is not possible to create partitioned CSV tables at all.

Prior to MySQL 5.1.6, tables using the BLACKHOLE storage engine also could not be partitioned.

Partitioning by KEY (or LINEAR KEY) is the only type of partitioning supported for the NDB storage engine. Beginning with MySQL 5.1.12, it is not possible to create a Cluster table using any partitioning type other than [LINEAR] KEY, and attempting to do so gives rise to an error.

When performing an upgrade, tables which are partitioned by KEY and that use any storage engine other than NDBCLUSTER and must be dumped and reloaded.

All of a table's partitions and subpartitions (if there are any of the latter) must use the same storage engine. We are working to remove this limitation in a future MySQL release.

A partitioning key must be either an integer column or an expression that resolves to an integer. The column or expression value may also be NULL. (See Section 16.2.6, “How MySQL Partitioning Handles NULL Values”.)

The lone exception to this restriction occurs when partitioning by [LINEAR] KEY — where it is possible to use columns of other types types as partitioning keys — because MySQL's internal key-hashing functions produce the correct data type from these types. For example, the following CREATE TABLE statement is valid:

CREATE TABLE tkc (c1 CHAR)
PARTITION BY KEY(c1)
PARTITIONS 4;

This exception does not apply to BLOB or TEXT column types.

A partitioning key may not be a subquery, even if that subquery resolves to an integer value or NULL.

All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have. In other words, every unique key on the table must use every column in the tables partitioning expression. For example, each of the following table creation statements is invalid:

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1),
    UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

CREATE TABLE t3 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2),
    UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

In each case, the proposed table would have at least one unique key that does not include all columns used in the partitioning expression.

Each of the following statements is valid, and represents one way in which the corresponding invalid table creation statement could be made to work:

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

CREATE TABLE t3 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3),
    UNIQUE KEY (col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

Since every primary key is by definition a unique key, this restriction also includes the table's primary key, if it has one. For example, the next two statements are invalid:

CREATE TABLE t4 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t5 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col3),
    UNIQUE KEY(col2)
)
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;

In both cases, the primary key does not include all columns referenced in the partitioning expression. However, both of the next two statements are valid:

CREATE TABLE t6 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;

CREATE TABLE t7 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2, col4),
    UNIQUE KEY(col2, col1)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;

If a table has no unique keys — this includes having no primary key — then this restriction does not apply, and you may use any column or columns in the partitioning expression as long as the column type is compatible with the partitioning type.

For the same reason, you cannot later add a unique key to a partitioned table unless the key includes all columns used by the table's partitioning expression. Consider given the partitioned table defined as shown here:

CREATE TABLE t_no_pk (c1 INT, c2 INT) 
    PARTITION BY RANGE(c1) (
        PARTITION p0 VALUES LESS THAN (10), 
        PARTITION p1 VALUES LESS THAN (20), 
        PARTITION p2 VALUES LESS THAN (30), 
        PARTITION p3 VALUES LESS THAN (40)
    );

It is possible to add a primary key to t_no_pk using either of these ALTER TABLE statements:

#  possible PK
ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);

#  also a possible PK
ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);

However, the next statement fails, because c1 is part of the partitioning key, but is not part of the proposed primary key:

#  fails with ERROR 1482
ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);

Since t_no_pk has only c1 in its partitioning expression, attempting to adding a unique key on c2 alone fails. However, you can add a unique key that uses both c1 and c2.

These rules also apply to existing non-partitioned tables that you wish to partition using ALTER TABLE ... PARTITION BY. Consider a table np_pk defined as shown here:

CREATE TABLE np_pk (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    added DATE,
    PRIMARY KEY (id)
);

The following ALTER TABLE statements fails with an error, because the added column is not part of any unique key in the table:

ALTER TABLE np_pk
    PARTITION BY HASH( TO_DAYS(added) )
    PARTITIONS 4;

This statement, however, would be valid:

ALTER TABLE np_pk
    PARTITION BY HASH(id)
    PARTITIONS 4;

In the case of np_pk, the only column that may be used as part of a partitioning expression is id; if you wish to partition this table using any other column or columns in the partitioning expression, you must first modify the table, either by adding the desired column or columns to the primary key, or by dropping the primary key altogether.

We are working to remove this limitation in a future MySQL release series.

Subpartitions are limited to HASH or KEY partitioning. HASH and KEY partitions cannot be subpartitioned.