CREATE TABLE - Partition definitions

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
    PARTITION partition
        [VALUES {LESS THAN (expression) | MAXVALUE | IN (value_list)}]
        [[STORAGE] ENGINE [=] engine]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] '/path']
        [INDEX DIRECTORY [=] '/path']
        [MAX_ROWS [=] number]
        [MIN_ROWS [=] number]
        [TABLESPACE [=] (tablespace)]
        [NODEGROUP [=] number]
        [(subpartition_definition [, subpartition_definition] ...)]

Explanation

The subclauses described on this documentation page define general parameters of partitions, such as their sizes and locations in the filesystems.

COMMENT

Use this subclause if you want to add a comment to a partition. The text must be contained within single quotes. Comments can be viewed only with the SHOW CREATE TABLE statement.

CREATE TABLE sales_figures
(region_id INT, sales_date DATE, amount INT)
PARTITION BY LIST (region_id) (
   PARTITION US_DATA VALUES IN(100,200,300)
   COMMENT = 'U.S. Data',
   PARTITION EU_DATA VALUES IN(400,500)
   COMMENT = 'Europe Data');

DATA DIRECTORY, INDEX DIRECTORY

With these subclauses you can specify file pathnames in order to fix the locations of partitions. The directories given must exist and you must have access privileges to the given directories.

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
PARTITION BY RANGE (client_id) (
   PARTITION p0 VALUES LESS THAN (500)
   DATA DIRECTORY = '/data/mysql/old_clients/data'
   INDEX DIRECTORY = '/data/mysql/old_clients/index',
   PARTITION p1 VALUES LESS THAN MAXVALUE
   DATA DIRECTORY = '/data/mysql/new_clients/data'
   INDEX DIRECTORY = '/data/mysql/new_clients/index');

ENGINE

This subclause specifies an alternative storage engine to use for the partition. However, at this time all partitions must use the same storage engine.

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
PARTITION BY RANGE (client_id) (
   PARTITION p0 VALUES LESS THAN (500)
   ENGINE = InnoDB,
   PARTITION p1 VALUES LESS THAN MAXVALUE
   ENGINE = InnoDB);

MAX_ROWS, MIN_ROWS

These subclauses suggest the maximum and minimum number of rows in a table partition, respectively. MySQL may deviate from these limits, though.

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
PARTITION BY RANGE (client_id) (
   PARTITION p0 VALUES LESS THAN (500)
   MIN_ROWS = 10 MAX_ROWS = 1000,
   PARTITION p3 VALUES LESS THAN MAXVALUE
   MIN_ROWS = 10 MAX_ROWS = 500);

NODEGROUP

This subclause can be used only with MySQL Cluster, and places a partition in the given node group. (MySQL clusters are divided into different node groups in order to let certain nodes manage the data nodes.)

TABLESPACE

This subclause can be used only with MySQL Cluster, and specifies the tablespace to use with the partition.

VALUES

This subclause specifies a range of values or a list of specific values for indexing and determining the disbursal of data among partions. These are described in the CREATE TABLE - Partitioning page.