MySQL :: MySQL 9.0 參考手冊 :: 26.3.1 RANGE 和 LIST 分割區的管理

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

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

26.3.1 RANGE 和 LIST 分割區的管理

新增和刪除 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)

注意

DROP PARTITION 受原生分割區就地 API 支援，且可以與 ALGORITHM={COPY|INPLACE} 一起使用。使用 ALGORITHM=INPLACE 的 DROP PARTITION 會刪除分割區中儲存的資料並刪除分割區。但是，使用 ALGORITHM=COPY 或 old_alter_table=ON 的 DROP PARTITION 會重建分割區的資料表，並嘗試將刪除分割區中的資料移至具有相容 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)

伺服器不會報告因 ALTER TABLE ... DROP PARTITION 而從資料表刪除的資料列數目，如同等效的 DELETE 查詢一樣。

刪除 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;
```

上一步首頁上一層下一步