17.3.3. Обмен Разделами и Подразделами с Таблицами

В MySQL 5.7 возможно обмениваться табличным разделом или подразделом с таблицей, используя ALTER TABLE ... EXCHANGE PARTITION оператор — то есть, чтобы переместить любые существующие строки в раздел или подраздел к неразделенной таблице, и любые существующие строки в неразделенной таблице к табличному разделу или подразделу.

Такие операции подвергаются следующим условиям:

Таблица, которой обменяются, не должна быть разделена, но должна иначе иметь ту же самую структуру таблицы как разделенная таблица.
Таблица, которой обменяются, не должна быть временной таблицей.
Любые строки, существующие в неразделенной таблице до обмена, должны лечь в пределах диапазона, определенного для раздела или подраздела.
У таблицы, которой обменяются, возможно, нет никаких внешних ключей, и при этом у любых других таблиц не может быть внешних ключей, которые ссылаются на эту таблицу.
В дополнение к ALTER, INSERT, и CREATE полномочия, обычно требуемые для ALTER TABLE операторы, Вы должны иметь DROP полномочие выполнить ALTER TABLE ... EXCHANGE PARTITION.

Кроме того, следует также знать о следующих эффектах ALTER TABLE ... EXCHANGE PARTITION:

Выполнение этого оператора не вызывает, любой включает или разделенную таблицу или обмененную таблицу.
Любой AUTO_INCREMENT столбцы в обмененной таблице сбрасываются.

Полный синтаксис ALTER TABLE ... EXCHANGE PARTITION оператор показывают здесь, где pt разделенная таблица, p раздел или подраздел, которым обменяются, и t неразделенная таблица, которой обменяются с p:

ALTER TABLE pt     EXCHANGE PARTITION p     WITH TABLE t;

Одним и только одним разделом или подразделом можно обменяться с одной и только одной неразделенной таблицей в сингле ALTER TABLE EXCHANGE PARTITION оператор. Чтобы обмениваться многократными разделами или подразделами, используйте многократный ALTER TABLE EXCHANGE PARTITION операторы. EXCHANGE PARTITION возможно, не объединяется с другим ALTER TABLE опции. Разделение и (если применимый) подразделение используемого разделенной таблицей может иметь какой-либо тип или типы, поддерживаемые в MySQL 5.7.

Предположите что разделенная таблица e был создан и заполнил использование следующих SQL-операторов:

CREATE TABLE e (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30))    PARTITION BY RANGE (id) (        PARTITION p0 VALUES LESS THAN (50),        PARTITION p1 VALUES LESS THAN (100),        PARTITION p2 VALUES LESS THAN (150),        PARTITION p3 VALUES LESS THAN (MAXVALUE));INSERT INTO e VALUES     (1669, "Jim", "Smith"),    (337, "Mary", "Jones"),    (16, "Frank", "White"),    (2005, "Linda", "Black");

Теперь мы создаем неразделенную копию e именованный e2. Это может быть сделано, используя mysql клиент как показано здесь:

mysql> CREATE TABLE e2 LIKE e;Query OK, 0 rows affected (1.34 sec)mysql> ALTER TABLE e2 REMOVE PARTITIONING;Query OK, 0 rows affected (0.90 sec)Records: 0  Duplicates: 0  Warnings: 0

Можно видеть который разделы в таблице e содержите строки, запрашивая INFORMATION_SCHEMA.PARTITIONS таблица, как это:

mysql> SELECT PARTITION_NAME,
        TABLE_ROWS    ->     FROM
        INFORMATION_SCHEMA.PARTITIONS    ->     WHERE TABLE_NAME =
        'e';+----------------+------------+| PARTITION_NAME | TABLE_ROWS |+----------------+------------+| p0             |          1 || p1             |          0 || p2             |          0 || p3             |          3 |+----------------+------------+4 rows in set (0.00 sec)

Обмениваться разделом p0 в таблице e с таблицей e2, можно использовать ALTER TABLE оператор, показанный здесь:

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE
        e2;Query OK, 0 rows affected (0.28 sec)

Более точно, оператор только выпущенные причины любые строки, найденные в разделе, который будет подкачан с найденными в таблице. Можно наблюдать, как это произошло, запрашивая INFORMATION_SCHEMA.PARTITIONS таблица, как прежде. Строка таблицы, которая была ранее найдена в разделе p0 больше не присутствует:

mysql> SELECT PARTITION_NAME,
        TABLE_ROWS    ->     FROM
        INFORMATION_SCHEMA.PARTITIONS    ->     WHERE TABLE_NAME =
        'e';+----------------+------------+| PARTITION_NAME | TABLE_ROWS |+----------------+------------+| p0             |          0 || p1             |          0 || p2             |          0 || p3             |          3 |+----------------+------------+4 rows in set (0.00 sec)

Если Вы запрашиваете таблицу e2, можно видеть, что "недостающая" строка может теперь быть найдена там:

mysql> SELECT * FROM e2;+----+-------+-------+| id | fname | lname |+----+-------+-------+| 16 | Frank | White |+----+-------+-------+1 row in set (0.00 sec)

Таблица, которой обменяются с разделом, должна не обязательно быть пустой. Чтобы демонстрировать это, мы сначала вставляем новую строку в таблицу e, удостоверяясь, что эта строка сохранена в разделе p0 выбирая id значение столбца, которое является меньше чем 50, и проверяющий это впоследствии, запрашивая PARTITIONS таблица:

                                                            mysql> INSERT INTO e VALUES (41, "Michael", "Green");              Query OK, 1 row affected (0.05 sec)                                mysql> SELECT PARTITION_NAME, TABLE_ROWS     ->     FROM INFORMATION_SCHEMA.PARTITIONS     ->     WHERE TABLE_NAME = 'e';              +----------------+------------+               | PARTITION_NAME | TABLE_ROWS |               +----------------+------------+               | p0             |          1 |               | p1             |          0 |               | p2             |          0 |               | p3             |          3 |               +----------------+------------+               4 rows in set (0.00 sec)

Теперь мы еще раз обмениваемся разделом p0 с таблицей e2 использование того же самого ALTER TABLE оператор как ранее:

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE
        e2;Query OK, 0 rows affected (0.28 sec)

Вывод следующих запросов показывает, что строка таблицы, которая была сохранена в разделе p0 и строка таблицы, которая была сохранена в таблице e2, до издания ALTER TABLE оператор, теперь переключили места:

mysql> SELECT * FROM e;+------+-------+-------+| id   | fname | lname |+------+-------+-------+|   16 | Frank | White || 1669 | Jim   | Smith ||  337 | Mary  | Jones || 2005 | Linda | Black |+------+-------+-------+4 rows in set (0.00 sec)mysql> SELECT PARTITION_NAME, TABLE_ROWS    ->     FROM INFORMATION_SCHEMA.PARTITIONS    ->     WHERE TABLE_NAME = 'e';+----------------+------------+| PARTITION_NAME | TABLE_ROWS |+----------------+------------+| p0             |          1 || p1             |          0 || p2             |          0 || p3             |          3 |+----------------+------------+4 rows in set (0.00 sec)mysql> SELECT * FROM e2;+----+---------+-------+| id | fname   | lname |+----+---------+-------+| 41 | Michael | Green |+----+---------+-------+1 row in set (0.00 sec)

Следует иметь в виду что любые строки, найденные в неразделенной таблице до издания ALTER TABLE ... EXCHANGE PARTITION оператор должен удовлетворить любым условиям, требуемым для них быть сохраненными в целевом разделе; иначе, сбои оператора. Чтобы видеть, как это происходит, сначала вставьте строку в e2 это не может быть сохранено в разделе p0 из таблицы e потому что id значение столбца является слишком большим; тогда, попытайтесь обмениваться таблицей с разделом снова:

mysql> INSERT INTO e2 VALUES (51, "Ellen",
        "McDonald");Query OK, 1 row affected (0.08 sec)mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;ERROR 1707 (HY000): Found row that does not match the partition

IGNORE ключевое слово принимается, но не имеет никакого эффекта когда использующийся с EXCHANGE PARTITION, как показано здесь:

mysql> ALTER IGNORE TABLE e EXCHANGE PARTITION p0
        WITH TABLE e2;ERROR 1707 (HY000): Found row that does not match the partition

Можно также обмениваться подразделом подразделенной таблицы (см. Раздел 17.2.6, "Подделя") с неразделенной таблицей, используя ALTER TABLE ... EXCHANGE PARTITION оператор. В следующем примере мы сначала составляем таблицу es это делится RANGE и подразделенный KEY, заполните эту таблицу, поскольку мы действительно представляли в виде таблицы e, и затем создайте пустую, неразделенную копию es2 из таблицы, как показано здесь:

mysql> CREATE TABLE es (    ->     id INT NOT NULL,    ->     fname
        VARCHAR(30),    ->     lname VARCHAR(30)    -> )    ->     PARTITION BY RANGE
        (id)    ->     SUBPARTITION BY KEY (lname)    ->     SUBPARTITIONS 2 (    ->         PARTITION
        p0 VALUES LESS THAN (50),    ->         PARTITION p1 VALUES
        LESS THAN (100),    ->         PARTITION p2 VALUES LESS THAN
        (150),    ->         PARTITION p3 VALUES LESS THAN
        (MAXVALUE)    ->     );Query OK, 0 rows affected (2.76 sec)mysql> INSERT INTO es VALUES    ->     (1669,
        "Jim", "Smith"),    ->     (337, "Mary",
        "Jones"),    ->     (16, "Frank", "White"),    ->     (2005, "Linda", "Black");Query OK, 4 rows affected (0.04 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> CREATE TABLE es2 LIKE es;Query OK, 0 rows affected (1.27 sec)mysql> ALTER TABLE es2 REMOVE PARTITIONING;Query OK, 0 rows affected (0.70 sec)Records: 0  Duplicates: 0  Warnings: 0

Хотя мы явно не называли ни одного из подразделов, составляя таблицу es, мы можем получить сгенерированные имена для них включением SUBPARTITION_NAME из PARTITIONS таблица от INFORMATION_SCHEMA выбирая из той таблицы, как показано здесь:

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME,
        TABLE_ROWS    ->     FROM
        INFORMATION_SCHEMA.PARTITIONS    ->     WHERE TABLE_NAME =
        'es';+----------------+-------------------+------------+| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |+----------------+-------------------+------------+| p0             | p0sp0             |          1 || p0             | p0sp1             |          0 || p1             | p1sp0             |          0 || p1             | p1sp1             |          0 || p2             | p2sp0             |          0 || p2             | p2sp1             |          0 || p3             | p3sp0             |          3 || p3             | p3sp1             |          0 |+----------------+-------------------+------------+8 rows in set (0.00 sec)

Следующий ALTER TABLE оператор обменивается подразделом p3sp0 таблица es с неразделенной таблицей es2:

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH
        TABLE es2;Query OK, 0 rows affected (0.29 sec)

Можно проверить, что строками обменялись, выпуская следующие запросы:

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME,
        TABLE_ROWS    ->     FROM
        INFORMATION_SCHEMA.PARTITIONS    ->     WHERE TABLE_NAME =
        'es';+----------------+-------------------+------------+| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |+----------------+-------------------+------------+| p0             | p0sp0             |          1 || p0             | p0sp1             |          0 || p1             | p1sp0             |          0 || p1             | p1sp1             |          0 || p2             | p2sp0             |          0 || p2             | p2sp1             |          0 || p3             | p3sp0             |          0 || p3             | p3sp1             |          0 |+----------------+-------------------+------------+8 rows in set (0.00 sec)mysql> SELECT * FROM es2;+------+-------+-------+| id   | fname | lname |+------+-------+-------+| 1669 | Jim   | Smith ||  337 | Mary  | Jones || 2005 | Linda | Black |+------+-------+-------+3 rows in set (0.00 sec)

Если таблица подделится, можно обмениваться только подразделом таблицы — не всего раздела — с неразделенной таблицей, как показано здесь:

mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH
        TABLE es2;ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition

Сравнение структур таблиц, используемых MySQL, очень строго. Число, порядок, имена, и типы столбцов и индексируют разделенной таблицы, и неразделенная таблица должна соответствовать точно. Кроме того, обе таблицы должны использовать тот же самый механизм хранения:

mysql> CREATE TABLE es3 LIKE e;Query OK, 0 rows affected (1.31 sec)mysql> ALTER TABLE es3 REMOVE PARTITIONING;Query OK, 0 rows affected (0.53 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW CREATE TABLE es3\G*************************** 1. row ***************************       Table: es3Create Table: CREATE TABLE `es3` (  `id` int(11) NOT NULL,  `fname` varchar(30) DEFAULT NULL,  `lname` varchar(30) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql> ALTER TABLE es3 ENGINE = MyISAM;Query OK, 0 rows affected (0.15 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL