For the examples in this section, we will use the article table partitioned in this way:

CREATE TABLE article (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  date DATE NOT NULL,
  author VARCHAR(100),
  language TINYINT UNSIGNED,
  text TEXT,
  PRIMARY KEY (id, date)
)
  ENGINE = InnoDB
PARTITION BY RANGE (YEAR(date)) (
  PARTITION p0 VALUES LESS THAN (1990),
  PARTITION p1 VALUES LESS THAN (2000),
  PARTITION p3 VALUES LESS THAN (2010),
  PARTITION p4 VALUES LESS THAN (2020)
);

With RANGE and LIST partitioning types, four operations are supported to modify the partitions:

Dropping an existing RANGE or LIST partition deletes all the records contained in that partition. For example, if we want to drop the partition p0, all articles published before 1990 will be lost. The following is the syntax to do this:

ALTER TABLE article DROP PARTITION p0;

New partitions can be added with a simple statement. However, there is a limitation in this operation: the new partition can only contain values that are not stored in any other partition. With the RANGE type, this means that the new partition can only be added to the end. Of course, this can only be done if the last partition does not catch all VALUES LESS THAN MAXVALUE to avoid ranges intersection. Here is an example:

ALTER TABLE article ADD PARTITION (
  PARTITION p5 VALUES LESS THAN (2030)
);

The DROP PARTITION and ADD PARTITION clauses support the IF EXISTS and IF NOT EXISTS options. With this option, a note (instead of an error) will be produced if you try to drop a partition that does not exist, or add an existing partition. These options exist for many MariaDB statements and make installation or update scripts more robust and easy to write. The following example shows what happens when trying to drop a nonexisting partition:

MariaDB [test]> ALTER TABLE article DROP PARTITION p0;
ERROR 1507 (HY000): Error in list of partitions to DROP
MariaDB [test]> ALTER TABLE article DROP PARTITION IF EXISTS p0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 1
Note (Code 1507): Error in list of partitions to DROP

The REORGANIZE command allows us to split partitions or rename them. It replaces one or more partition with one or more new partitions defined in the command. Its stronger limitation is that it cannot modify the partitions' ranges, except for the last one, whose range can be extended. This makes complex merge and split operations impossible, such as splitting two partitions into three or merging two partitions into one.

In the following example, p1 is split again into the two original partitions:

ALTER TABLE article REORGANIZE PARTITION p1 INTO (
  PARTITION p0 VALUES LESS THAN (1990),
  PARTITION p1 VALUES LESS THAN (2000)
);

If we want to merge them again, we can use REORGANIZE to avoid data loss:

ALTER TABLE article REORGANIZE PARTITION p0, p1 INTO (
  PARTITION p1 VALUES LESS THAN (2000)
);

Let's rename p4 into the current one:

ALTER TABLE article REORGANIZE PARTITION p4 INTO (
  PARTITION current VALUES LESS THAN (2020)
);

To remove a table partitioning without losing its data, use the following:

ALTER TABLE article REMOVE PARTITIONING;

Normally, it is not desirable to remove a table partitioning. However, this can be done to bring the table to an intermediate state which allows us to change its partitioning type or its partitioning expression, or to perform a complex reorganization that cannot be accomplished using REORGANIZE PARTITION.

In this section, we will use the article table partitioned by HASH:

CREATE TABLE article (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  date DATE NOT NULL,
  author VARCHAR(100),
  language TINYINT UNSIGNED,
  text TEXT,
  PRIMARY KEY (id, date)
)
  ENGINE = InnoDB
PARTITION BY HASH(YEAR(date))
PARTITIONS 8;

The following operations are allowed for tables partitioned by HASH or KEY:

In the Modifying RANGE and LIST partitions section, we already discussed how and why we can remove partitioning and this topic will not be repeated.

Adding a partition is simple. The syntax is the same that we used for a partitioned table of the RANGE type. Given the nature of HASH and KEY partitioning types, they do not impose any restrictions to the creation of new partitions. In the following example, we will add two partitions:

ALTER TABLE article ADD PARTITION PARTITIONS 2;

The extended syntax is also allowed:

ALTER TABLE article ADD PARTITION (
  PARTITION p8,
  PARTITION p9
);

This syntax can be used to specify nondefault names or paths for the new partitions. See the Partitions' physical files section for details on how to distribute partitions over multiple disks.

The HASH and KEY types do not support the DROP operation: because of their nature, it is never desirable to destroy a partition and all its data. However, it is possible to use COALESCE to merge one or more partitions to others. The data will not be lost; instead, they will be copied to other partitions. This operation is relatively fast. The following example shows how to eliminate two partitions from the article table:

ALTER TABLE article COALESCE PARTITION 2;
Note that the value after COALESCE PARTITION is the number of the partition that will disappear, not the number of partitions that we want to remain after the statement execution.

For these partitioning types, the REORGANIZE command can only be used to rename a partition or change its options. The following example shows how to rename a partition:

ALTER TABLE article REORGANIZE PARTITION p0 INTO (
  PARTITION p000
);

The techniques described here work with all partitioning types though they are hardly useful with partitioned tables of the HASH and KEY types. The reason is that, when using these types, logical sets of rows are distributed over all the partitions.

However, the RANGE and LIST types allow us to divide data into groups that are assigned to a particular partition based on one or more values. Usually, the partitioning expression is simple, also for performance reasons. This allows a human being to easily find out which partition, or group of partitions, stores a set of rows.

MariaDB 10.0 supports an ALTER TABLE clause that allows us to exchange the data stored in a partition with the data stored in a nonpartitioned table.

Now, suppose we have the article table defined with the following RANGE partitions:

CREATE TABLE article (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  date DATE NOT NULL,
  author VARCHAR(100),
  language TINYINT UNSIGNED,
  text TEXT,
  PRIMARY KEY (id, date)
)
  ENGINE = InnoDB
PARTITION BY RANGE (YEAR(date)) (
  PARTITION p0 VALUES LESS THAN (1990),
  PARTITION p1 VALUES LESS THAN (2000),
  PARTITION p3 VALUES LESS THAN (2010),
  PARTITION current VALUES LESS THAN (2020)
);

In the following example, we want to move the rows from the current partition to a new table called recent_article. To do this, we need to create the new table whose definition is identical to the definition of this article, except that recent_article must not be partitioned. The easiest way to do this is to copy the table structure and then remove the partitioning from the new table. Then, we can exchange the data, which is shown as follows:

MariaDB [test]> CREATE TABLE recent_article LIKE article;
Query OK, 0 rows affected (1.79 sec)
MariaDB [test]> ALTER TABLE recent_article REMOVE PARTITIONING;
Query OK, 0 rows affected (1.51 sec)               
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [test]> ALTER TABLE article
    -> EXCHANGE PARTITION current
    -> WITH TABLE recent_article;
Query OK, 0 rows affected (0.44 sec)

If we don't want to empty the original row's container, or the version of our MariaDB server is older than 10.0, we can use the well-known SELECT … INSERT or CREATE TABLE … SELECT statements. If we want to use it to exchange data on an old MariaDB version, we will need to create a temporary table.

When copying data from a partition on MariaDB 10.0, we can use a particular SELECT extension that only returns data from the specified partitions. This extension will be explained later in this chapter, in the Query optimizations section. However, it is intuitive enough to show its use in data copying in this section. The following example shows how to copy the content of the current partition into the recent_article table, without deleting the original rows:

MariaDB [test]> CREATE OR REPLACE TABLE recent_article
    -> SELECT * FROM article PARTITION (current);
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0