MySQL 8.4 參考手冊  /  ...  /  RANGE 和 LIST 分割區的管理

26.3.1 RANGE 和 LIST 分割區的管理

新增和刪除範圍和列表分割區的方式類似，因此我們在本節中討論這兩種分割區的管理。如需瞭解如何使用依雜湊或鍵分割的表格，請參閱第 26.3.2 節「HASH 和 KEY 分割區的管理」。

從依 RANGE 或 LIST 分割的表格中刪除分割區，可以使用帶有 DROP PARTITION 選項的 ALTER TABLE 陳述式來完成。假設您已建立一個依範圍分割的表格，然後使用下列 CREATE TABLE 和 INSERT 陳述式填入 10 筆記錄

mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
    ->     PARTITION BY RANGE( YEAR(purchased) ) (
    ->         PARTITION p0 VALUES LESS THAN (1990),
    ->         PARTITION p1 VALUES LESS THAN (1995),
    ->         PARTITION p2 VALUES LESS THAN (2000),
    ->         PARTITION p3 VALUES LESS THAN (2005),
    ->         PARTITION p4 VALUES LESS THAN (2010),
    ->         PARTITION p5 VALUES LESS THAN (2015)
    ->     );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO tr VALUES
    ->     (1, 'desk organiser', '2003-10-15'),
    ->     (2, 'alarm clock', '1997-11-05'),
    ->     (3, 'chair', '2009-03-10'),
    ->     (4, 'bookcase', '1989-01-10'),
    ->     (5, 'exercise bike', '2014-05-09'),
    ->     (6, 'sofa', '1987-06-05'),
    ->     (7, 'espresso maker', '2011-11-22'),
    ->     (8, 'aquarium', '1992-08-04'),
    ->     (9, 'study desk', '2006-09-16'),
    ->     (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.05 sec)
Records: 10  Duplicates: 0  Warnings: 0

您可以看到哪些項目應該已插入分割區 p2，如下所示

mysql> SELECT * FROM tr
    ->     WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-------------+------------+
| id   | name        | purchased  |
+------+-------------+------------+
|    2 | alarm clock | 1997-11-05 |
|   10 | lava lamp   | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)

您也可以使用分割區選擇來取得此資訊，如下所示

mysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id   | name        | purchased  |
+------+-------------+------------+
|    2 | alarm clock | 1997-11-05 |
|   10 | lava lamp   | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)

如需更多資訊，請參閱第 26.5 節「分割區選擇」。

若要刪除名為 p2 的分割區，請執行下列命令

mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)
注意

NDBCLUSTER 儲存引擎不支援 ALTER TABLE ... DROP PARTITION。但是，它支援本章中描述的 ALTER TABLE 的其他分割區相關擴充功能。

務必記住，當您刪除分割區時，也會刪除儲存在該分割區中的所有資料。您可以重新執行先前的 SELECT 查詢來確認這一點

mysql> SELECT * FROM tr WHERE purchased
    -> BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)
注意

原生分割區就地 API 支援 DROP PARTITION，並且可以與 ALGORITHM={COPY|INPLACE} 一起使用。DROP PARTITION 與 ALGORITHM=INPLACE 會刪除儲存在分割區中的資料並刪除分割區。但是，DROP PARTITION 與 ALGORITHM=COPY 或 old_alter_table=ON 會重建分割表格，並嘗試將已刪除分割區中的資料移至另一個具有相容 PARTITION ... VALUES 定義的分割區。無法移至另一個分割區的資料將會被刪除。

因此，您必須擁有表格的 DROP 權限，才能對該表格執行 ALTER TABLE ... DROP PARTITION。

如果您希望刪除所有分割區中的所有資料，同時保留表格定義及其分割區配置，請使用 TRUNCATE TABLE 陳述式。(請參閱第 15.1.37 節「TRUNCATE TABLE 陳述式」。)

如果您打算變更表格的分割區，而不遺失資料，請改用 ALTER TABLE ... REORGANIZE PARTITION。如需關於 REORGANIZE PARTITION 的資訊，請參閱下方或第 15.1.9 節「ALTER TABLE 陳述式」。

如果您現在執行 SHOW CREATE TABLE 陳述式，您可以看到表格的分割區組成已變更

mysql> SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
       Table: tr
Create Table: CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */
1 row in set (0.00 sec)

當您將新的列插入已變更的表格中，且 purchased 欄的值介於 '1995-01-01' 和 '2004-12-31' (含) 之間時，這些列會儲存在分割區 p3 中。您可以透過以下方式驗證這一點

mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tr WHERE purchased
    -> BETWEEN '1995-01-01' AND '2004-12-31';
+------+----------------+------------+
| id   | name           | purchased  |
+------+----------------+------------+
|    1 | desk organiser | 2003-10-15 |
|   11 | pencil holder  | 1995-07-12 |
+------+----------------+------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE tr DROP PARTITION p3;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM tr WHERE purchased
    -> BETWEEN '1995-01-01' AND '2004-12-31';
Empty set (0.00 sec)

伺服器不會像同等的 DELETE 查詢一樣，報告因 ALTER TABLE ... DROP PARTITION 而從表格中刪除的列數。

刪除 LIST 分割區時，會使用與刪除 RANGE 分割區完全相同的 ALTER TABLE ... DROP PARTITION 語法。但是，對您之後使用表格的方式產生的一個重要差異：您再也不能將任何包含在定義已刪除分割區的值清單中的值插入表格中。(如需範例，請參閱第 26.2.2 節「LIST 分割區」。)

若要將新的範圍或列表分割區新增至先前已分割的表格，請使用 ALTER TABLE ... ADD PARTITION 陳述式。對於依 RANGE 分割的表格，這可以用來將新的範圍新增至現有分割區清單的末尾。假設您有一個分割表格，其中包含您的組織的成員資料，其定義如下

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000)
);

進一步假設成員的最低年齡為 16 歲。隨著 2015 年底的臨近，您意識到您必須很快準備好接納出生於 2000 年 (及之後) 的成員。您可以修改 members 表格以容納出生於 2000 年至 2010 年的新成員，如下所示

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

對於依範圍分割的表格，您只能使用 ADD PARTITION 將新的分割區新增至分割區清單的高端。嘗試以此方式在現有分割區之間或之前新增新的分割區會導致錯誤，如下所示

mysql> ALTER TABLE members
     >     ADD PARTITION (
     >     PARTITION n VALUES LESS THAN (1970));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
   increasing for each partition

您可以透過將第一個分割區重新組織為兩個新的分割區來解決此問題，這兩個分割區會分割它們之間的範圍，如下所示

ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1970),
        PARTITION n1 VALUES LESS THAN (1980)
);

使用 SHOW CREATE TABLE，您可以看到 ALTER TABLE 陳述式已達到預期的效果

mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
       Table: members
Create Table: CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)

另請參閱第 15.1.9.1 節「ALTER TABLE 分割區操作」。

您也可以使用 ALTER TABLE ... ADD PARTITION 將新的分割區新增至依 LIST 分割的表格。假設使用下列 CREATE TABLE 陳述式來定義表格 tt

CREATE TABLE tt (
    id INT,
    data INT
)
PARTITION BY LIST(data) (
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
);

您可以新增新的分割區，以儲存具有 data 欄值 7、14 和 21 的列，如下所示

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

請記住，您不能新增涵蓋現有分割區值清單中已包含的任何值的新的 LIST 分割區。如果您嘗試這樣做，會導致錯誤

mysql> ALTER TABLE tt ADD PARTITION 
     >     (PARTITION np VALUES IN (4, 8, 12));
ERROR 1465 (HY000): Multiple definition of same constant »
                    in list partitioning

因為任何具有 data 欄值 12 的列都已指派給分割區 p1，所以您無法在表格 tt 上建立新的分割區，其中在其值清單中包含 12。若要完成此操作，您可以刪除 p1，並新增 np，然後新增具有修改定義的新 p1。但是，如先前所述，這會導致遺失儲存在 p1 中的所有資料—而且通常這不是您真正想要做的事情。另一個解決方案似乎是建立一個具有新分割區的表格副本，並使用 CREATE TABLE ... SELECT ... 將資料複製到其中，然後刪除舊表格並重新命名新表格，但當處理大量資料時，這可能非常耗時。在需要高可用性的情況下，這也可能不可行。

您可以在單一 ALTER TABLE ... ADD PARTITION 陳述式中新增多個分割區，如下所示

CREATE TABLE employees (
  id INT NOT NULL,
  fname VARCHAR(50) NOT NULL,
  lname VARCHAR(50) NOT NULL,
  hired DATE NOT NULL
)
PARTITION BY RANGE( YEAR(hired) ) (
  PARTITION p1 VALUES LESS THAN (1991),
  PARTITION p2 VALUES LESS THAN (1996),
  PARTITION p3 VALUES LESS THAN (2001),
  PARTITION p4 VALUES LESS THAN (2005)
);

ALTER TABLE employees ADD PARTITION (
    PARTITION p5 VALUES LESS THAN (2010),
    PARTITION p6 VALUES LESS THAN MAXVALUE
);

幸運的是，MySQL 的分割區實作提供了在不遺失資料的情況下重新定義分割區的方法。讓我們首先看看幾個涉及 RANGE 分割區的簡單範例。回想一下 members 表格，現在其定義如下所示

mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
       Table: members
Create Table: CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)

假設您想要將所有出生於 1960 年之前的會員資料列移動到一個獨立的分割區。如同我們已經看到的，這無法使用 ALTER TABLE ... ADD PARTITION 來完成。不過，您可以使用另一個與分割區相關的 ALTER TABLE 延伸功能來達成這個目的。

ALTER TABLE members REORGANIZE PARTITION n0 INTO (
    PARTITION s0 VALUES LESS THAN (1960),
    PARTITION s1 VALUES LESS THAN (1970)
);

實際上，這個指令會將分割區 p0 分割成兩個新的分割區 s0 和 s1。它也會根據兩個 PARTITION ... VALUES ... 子句中所包含的規則，將儲存在 p0 中的資料移動到新的分割區中，因此 s0 只會包含 YEAR(dob) 小於 1960 的記錄，而 s1 則會包含 YEAR(dob) 大於或等於 1960 但小於 1970 的資料列。

REORGANIZE PARTITION 子句也可以用於合併相鄰的分割區。您可以依照這裡所示，還原先前對 members 表格所做的變更效果。

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (1970)
);

使用 REORGANIZE PARTITION 分割或合併分割區時，不會遺失任何資料。在執行上述語句時，MySQL 會將儲存在分割區 s0 和 s1 中的所有記錄移動到分割區 p0。

REORGANIZE PARTITION 的一般語法如下所示：

ALTER TABLE tbl_name
    REORGANIZE PARTITION partition_list
    INTO (partition_definitions);

在這裡，tbl_name 是已分割表格的名稱，而 partition_list 是以逗號分隔的一或多個要變更之現有分割區名稱的清單。partition_definitions 是以逗號分隔的新分割區定義清單，其遵循與 CREATE TABLE 中使用的 partition_definitions 清單相同的規則。當使用 REORGANIZE PARTITION 時，您不限於將多個分割區合併為一個，或將一個分割區分割成多個。例如，您可以將 members 表格的所有四個分割區重組為兩個，如下所示：

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
    PARTITION m0 VALUES LESS THAN (1980),
    PARTITION m1 VALUES LESS THAN (2000)
);

您也可以將 REORGANIZE PARTITION 與使用 LIST 分割的表格一起使用。讓我們回到在清單分割的 tt 表格中新增新的分割區，但因為新的分割區具有已存在於其中一個現有分割區值清單中的值而失敗的問題。我們可以透過新增一個僅包含不衝突值的分割區來處理這個問題，然後重新組織新的分割區和現有的分割區，以便將儲存在現有分割區中的值移動到新的分割區。

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);

以下是在使用 ALTER TABLE ... REORGANIZE PARTITION 重新分割使用 RANGE 或 LIST 分割的表格時，需要牢記的一些重點：

  • 用於判斷新分割配置的 PARTITION 選項，與 CREATE TABLE 陳述式中使用的選項遵循相同的規則。

    新的 RANGE 分割配置不能有任何重疊的範圍；新的 LIST 分割配置不能有任何重疊的值集合。

  • partition_definitions 清單中的分割區組合，在整體上應考量與 partition_list 中指定的組合分割區相同的範圍或值集合。

    例如，分割區 p1 和 p2 在此章節中所用的範例 members 表格中，共同涵蓋 1980 年到 1999 年。對這兩個分割區的任何重組都應整體涵蓋相同的年份範圍。

  • 對於以 RANGE 分割的表格，您只能重組相鄰的分割區；您無法跳過範圍分割區。

    例如，您無法使用以 ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ... 開頭的語句來重組範例 members 表格，因為 p0 涵蓋 1970 年之前的年份，而 p2 則涵蓋 1990 年至 1999 年（含）的年份，因此這些不是相鄰的分割區。（在這種情況下，您不能跳過分割區 p1。）

  • 您無法使用 REORGANIZE PARTITION 來變更表格所使用的分割類型（例如，您無法將 RANGE 分割區變更為 HASH 分割區，反之亦然）。您也不能使用此語句來變更分割運算式或欄。若要在不刪除並重新建立表格的情況下完成任何一項工作，您可以使用 ALTER TABLE ... PARTITION BY ...，如下所示：

    ALTER TABLE members
    PARTITION BY HASH( YEAR(dob) )
    PARTITIONS 8;

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