MySQL 9.0 參考手冊  /  ...  /  與表格交換分割區和子分割區

26.3.3 與表格交換分割區和子分割區

在 MySQL 9.0 中，可以使用 ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt 來將表格分割區或子分割區與表格交換，其中 pt 是已分割的表格，而 p 是 pt 的分割區或子分割區，要與未分割的表格 nt 交換，前提是以下陳述為真

  1. 表格 nt 本身未分割。

  2. 表格 nt 不是暫存表格。

  3. 表格 pt 和 nt 的結構在其他方面相同。

  4. 表格 nt 不包含任何外鍵參考，而且沒有其他表格具有任何參考 nt 的外鍵。

  5. 在 nt 中，沒有任何列超出 p 的分割區定義範圍。如果使用了 WITHOUT VALIDATION，則此條件不適用。

  6. 兩個表格都必須使用相同的字元集和校對。

  7. 對於 InnoDB 表格，兩個表格都必須使用相同的列格式。若要判斷 InnoDB 表格的列格式，請查詢 INFORMATION_SCHEMA.INNODB_TABLES。

  8. p 的任何分割區層級 MAX_ROWS 設定都必須與為 nt 設定的表格層級 MAX_ROWS 值相同。p 的任何分割區層級 MIN_ROWS 設定也必須與為 nt 設定的任何表格層級 MIN_ROWS 值相同。

    無論 pt 是否有生效的明確表格層級 MAX_ROWS 或 MIN_ROWS 選項，情況都是如此。

  9. 兩個表格 pt 和 nt 之間的 AVG_ROW_LENGTH 不能不同。

  10. INDEX DIRECTORY 在表格和要與其交換的分割區之間不能不同。

  11. 在任一表格中都不能使用任何表格或分割區 TABLESPACE 選項。

除了執行 ALTER TABLE 陳述式通常所需的 ALTER、INSERT 和 CREATE 權限外，您還必須具有 DROP 權限才能執行 ALTER TABLE ... EXCHANGE PARTITION。

您還應該知道 ALTER TABLE ... EXCHANGE PARTITION 的下列影響

  • 執行 ALTER TABLE ... EXCHANGE PARTITION 不會在已分割的表格或要交換的表格上叫用任何觸發器。

  • 交換表格中的任何 AUTO_INCREMENT 列都會重設。

  • IGNORE 關鍵字與 ALTER TABLE ... EXCHANGE PARTITION 一起使用時沒有作用。

此處顯示 ALTER TABLE ... EXCHANGE PARTITION 的語法，其中 pt 是已分割的表格，p 是要交換的分割區（或子分割區），而 nt 是要與 p 交換的未分割表格

ALTER TABLE pt
    EXCHANGE PARTITION p
    WITH TABLE nt;

您可以選擇性地附加 WITH VALIDATION 或 WITHOUT VALIDATION。指定 WITHOUT VALIDATION 時，ALTER TABLE ... EXCHANGE PARTITION 作業在交換分割區和未分割表格時不會執行任何逐列驗證，允許資料庫管理員負責確保列在分割區定義的界限內。WITH VALIDATION 是預設值。

在單一 ALTER TABLE EXCHANGE PARTITION 陳述式中，只能將一個分割區或子分割區與一個未分割表格交換。若要交換多個分割區或子分割區，請使用多個 ALTER TABLE EXCHANGE PARTITION 陳述式。EXCHANGE PARTITION 可能無法與其他 ALTER TABLE 選項組合。已分割表格使用的分割區和（如果適用）子分割區可以是 MySQL 9.0 中支援的任何類型。

與未分割表格交換分割區

假設已使用下列 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 (0.04 sec)

mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

您可以查詢 Information Schema PARTITIONS 表格，來查看表格 e 中哪些分割區包含列，如下所示

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 |
+----------------+------------+
2 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.04 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 陳述式之前，在未分割表格中找到的任何列，都必須符合儲存在目標分割區中所需的條件；否則，陳述式會失敗。為了了解這種情況如何發生，請先在 e2 中插入一個超出表格 e 的分割區 p0 分割區定義範圍的列。例如，插入一個 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

只有 WITHOUT VALIDATION 選項才能允許此操作成功。

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

當分割區與包含與分割區定義不符的列的表格交換時，資料庫管理員有責任修復不匹配的列，這可以使用 REPAIR TABLE 或 ALTER TABLE ... REPAIR PARTITION 執行。

交換分割區，不進行逐列驗證

為了避免在將分割區與具有許多列的表格交換時進行耗時的驗證，可以透過將 WITHOUT VALIDATION 附加到 ALTER TABLE ... EXCHANGE PARTITION 陳述式，來跳過逐列驗證的步驟。

以下範例比較了在有驗證和沒有驗證的情況下，將分割區與未分割表格交換時的執行時間差異。分割表格（表格 e）包含兩個各有 100 萬列的分割區。表格 e 的 p0 中的列會被移除，而 p0 會與一個包含 100 萬列的未分割表格交換。WITH VALIDATION 操作需要 0.74 秒。相較之下，WITHOUT VALIDATION 操作需要 0.01 秒。

# Create a partitioned table with 1 million rows in each partition

CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (1000001),
        PARTITION p1 VALUES LESS THAN (2000001),
);

mysql> SELECT COUNT(*) FROM e;
| COUNT(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.27 sec)

# View the rows in each partition

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+-------------+
| PARTITION_NAME | TABLE_ROWS  |
+----------------+-------------+
| p0             |     1000000 |
| p1             |     1000000 |
+----------------+-------------+
2 rows in set (0.00 sec)

# Create a nonpartitioned table of the same structure and populate it with 1 million rows

CREATE TABLE e2 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
);

mysql> SELECT COUNT(*) FROM e2;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.24 sec)

# Create another nonpartitioned table of the same structure and populate it with 1 million rows

CREATE TABLE e3 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
);

mysql> SELECT COUNT(*) FROM e3;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.25 sec)

# Drop the rows from p0 of table e

mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)

# Confirm that there are no rows in partition p0

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

# Exchange partition p0 of table e with the table e2 'WITH VALIDATION'

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

# Confirm that the partition was exchanged with table e2

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

# Once again, drop the rows from p0 of table e

mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)

# Confirm that there are no rows in partition p0

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

# Exchange partition p0 of table e with the table e3 'WITHOUT VALIDATION'

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e3 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.01 sec)

# Confirm that the partition was exchanged with table e3

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

如果分割區與包含與分割區定義不符的列的表格交換，資料庫管理員有責任修復不匹配的列，這可以使用 REPAIR TABLE 或 ALTER TABLE ... REPAIR PARTITION 執行。

將子分割區與未分割表格交換

您也可以使用 ALTER TABLE ... EXCHANGE PARTITION 陳述式，將子分割表格的子分割區（請參閱第 26.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 時，我們沒有明確命名任何子分割區，但我們可以透過從 INFORMATION_SCHEMA 的 PARTITIONS 表格中選取時，包含該表格的 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> 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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

本範例中的 ALTER TABLE ... ENGINE ... 陳述式之所以能運作，是因為先前的 ALTER TABLE 從表格 es3 中移除了分割區。


上一頁   首頁   向上   下一頁
相關文件
MySQL 9.0 發行說明
下載本手冊
PDF (US Ltr) - 40.0Mb
PDF (A4) - 40.1Mb
Man Pages (TGZ) - 258.2Kb
Man Pages (Zip) - 365.3Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb