Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 26.9Mb
PDF (A4) - 26.9Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


MySQL 5.6 リファレンスマニュアル  /  ...  /  パーティションとサブパーティションをテーブルと交換する

19.3.3 パーティションとサブパーティションをテーブルと交換する

MySQL 5.6 では、ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt を使用して、テーブルパーティションまたはサブパーティションをテーブルと交換できます。ここで、pt はパーティション化されたテーブル、p はパーティション化されていないテーブル nt と交換する pt のパーティションまたはサブパーティションです (次の記述が true である場合)。

  1. テーブル nt 自体はパーティション化されていない。

  2. テーブル nt は一時テーブルではない。

  3. テーブル pt および nt の構造はそれ以外の点で同じである。

  4. テーブル nt は外部キー参照を含まず、ほかのどのテーブルも nt を参照する外部キーを持たない。

  5. nt 内に p のパーティション定義の境界の外に存在する行がない。

ALTER TABLE ステートメントに通常必要な ALTERINSERT、および CREATE 権限に加えて、ALTER TABLE ... EXCHANGE PARTITION を実行するための DROP 権限が必要です。

ALTER TABLE ... EXCHANGE PARTITION の次の影響も考慮してください。

  • ALTER TABLE ... EXCHANGE PARTITION を実行しても、パーティション化されたテーブルまたは交換されるテーブルに対するトリガーは呼び出されません。

  • 交換されるテーブル内の AUTO_INCREMENT カラムがリセットされます。

  • IGNORE キーワードは、ALTER TABLE ... EXCHANGE PARTITION と一緒に使用された場合、効果を持つません。

ALTER TABLE ... EXCHANGE PARTITION ステートメントの完全な構文を次に示します。ここで、pt はパーティション化されたテーブル、p は交換されるパーティションまたはサブパーティション、ntp と交換されるパーティション化されていないテーブルです。

ALTER TABLE pt 
    EXCHANGE PARTITION p 
    WITH TABLE nt;

単一 ALTER TABLE EXCHANGE PARTITION ステートメントでは、1 つのパーティションまたはサブパーティションのみを 1 つのパーティション化されていないテーブルのみと交換できます。複数のパーティションまたはサブパーティションを交換するには、複数の ALTER TABLE EXCHANGE PARTITION ステートメントを使用してください。EXCHANGE PARTITION は、ほかの ALTER TABLE オプションと組み合わせることはできません。パーティション化されたテーブルによって使用されるパーティショニングおよび (該当する場合) サブパーティショニングには、MySQL 5.6 でサポートされる任意のタイプを選択できます。

パーティションをパーティション化されていないテーブルと交換する

次の SQL ステートメントを使用して、パーティション化されたテーブル e が作成および移入されているとします。

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");

ここで、e2 という名前の、e のパーティション化されていないコピーを作成します。これは、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)
注記

パーティション化された InnoDB テーブルの場合、INFORMATION_SCHEMA.PARTITIONS テーブルの TABLE_ROWS カラムに示される行数は、SQL 最適化で使用される見積もり値であり、常に正確とはかぎりません。

テーブル e 内のパーティション p0 をテーブル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 に格納されていることを確認します (50 より小さい id カラム値を選択し、これをあとで 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)

ここで、前と同じ ALTER TABLE ステートメントを使用して、ふたたびパーティション p0 をテーブル e2 と交換します。

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

次のクエリーの出力は、ALTER TABLE ステートメントを発行する前に、パーティション p0 に格納されていたテーブル行およびテーブル e2 に格納されていたテーブル行の配置が切り替わったことを示しています。

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 ステートメントを発行する前にパーティション化されていないテーブルで見つかる行は、それらがターゲットパーティションに格納されるために必要な条件を満たしている必要があり、そうでない場合はステートメントが失敗することを覚えておいてください。これがどのように発生するかを確認するために、まずテーブルe のパーティション p0 のパーティション定義の境界外の行を、e2 に挿入します。たとえば、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

サブパーティションをパーティション化されていないテーブルと交換する

ALTER TABLE ... EXCHANGE PARTITION ステートメントを使用して、サブパーティション化されたテーブルのサブパーティション (セクション19.2.6「サブパーティショニング」を参照してください) をパーティション化されていないテーブルと交換することもできます。次の例では、まず RANGE によってパーティション化され、KEY によってサブパーティション化されたテーブル es を作成し、テーブル 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: 0

mysql> 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 を作成するときにサブパーティションの名前を明示的に指定しなかったけれども、PARTITIONS テーブルから選択するときに、次のように INFORMATION_SCHEMA からそのテーブルの SUBPARTITION_NAME を取り込むことで、それらに生成された名前を取得できます。

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 ステートメントは、テーブル es のサブパーティション p3sp0 をパーティション化されていないテーブル 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: 0

mysql> SHOW CREATE TABLE es3\G
*************************** 1. row ***************************
       Table: es3
Create Table: CREATE TABLE `es3` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 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: 0

mysql> 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