MySQL 8.4 參考手冊  /  ...  /  RANGE COLUMNS 分區

26.2.3.1 RANGE COLUMNS 分區

範圍欄 (RANGE COLUMNS) 分區與範圍 (range) 分區類似，但它允許您使用基於多個欄值的範圍來定義分區。此外，您可以使用非整數類型的欄來定義範圍。

RANGE COLUMNS 分區與 RANGE 分區在以下方面顯著不同

  • RANGE COLUMNS 不接受表達式，只接受欄的名稱。

  • RANGE COLUMNS 接受一個或多個欄的清單。

    RANGE COLUMNS 分區基於 元組（欄值清單）之間的比較，而不是標量值之間的比較。將列放入 RANGE COLUMNS 分區也是基於元組之間的比較；這將在本節稍後進行進一步討論。

  • RANGE COLUMNS 分區欄不限於整數欄；字串、DATE 和 DATETIME 欄也可以用作分區欄。（有關詳細資訊，請參閱第 26.2.3 節「COLUMNS 分區」。）

以下顯示了建立使用 RANGE COLUMNS 分區的表格的基本語法

CREATE TABLE table_name
PARTITION BY RANGE COLUMNS(column_list) (
    PARTITION partition_name VALUES LESS THAN (value_list)[,
    PARTITION partition_name VALUES LESS THAN (value_list)][,
    ...]
)

column_list:
    column_name[, column_name][, ...]

value_list:
    value[, value][, ...]
注意

並非所有在建立分區表格時可以使用的 CREATE TABLE 選項都在此處顯示。有關完整資訊，請參閱 第 15.1.20 節「CREATE TABLE 陳述式」。

在剛才顯示的語法中，column_list 是一個或多個欄的清單（有時稱為分區欄清單），而 value_list 是一個值的清單（也就是說，它是分區定義值清單）。必須為每個分區定義提供 value_list，並且每個 value_list 必須具有與 column_list 的欄數相同的數值。一般來說，如果您在 COLUMNS 子句中使用 N 個欄，則每個 VALUES LESS THAN 子句也必須提供 N 個值的清單。

分區欄清單和定義每個分區的值清單中的元素必須以相同的順序出現。此外，值清單中的每個元素必須與欄清單中對應的元素具有相同的資料類型。然而，分區欄清單和值清單中欄名稱的順序不必與 CREATE TABLE 陳述式主要部分的表格欄定義的順序相同。與使用 RANGE 分區的表格一樣，您可以使用 MAXVALUE 來表示一個值，使得插入給定欄中的任何合法值始終小於該值。以下是一個 CREATE TABLE 陳述式的範例，可以幫助說明所有這些重點

mysql> CREATE TABLE rcx (
    ->     a INT,
    ->     b INT,
    ->     c CHAR(3),
    ->     d INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,d,c) (
    ->     PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
    ->     PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
    ->     PARTITION p2 VALUES LESS THAN (15,30,'sss'),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    -> );
Query OK, 0 rows affected (0.15 sec)

表格 rcx 包含欄 a、b、c、d。提供給 COLUMNS 子句的分區欄清單使用這些欄中的 3 個，順序為 a、d、c。用於定義分區的每個值清單都包含 3 個相同順序的值；也就是說，每個值清單元組都具有 (INT、INT、CHAR(3)) 的形式，這對應於欄 a、d 和 c（按此順序）使用的資料類型。

將列放入分區的方式，是將要插入的列中與 COLUMNS 子句中的欄清單匹配的元組，與 VALUES LESS THAN 子句中用於定義表格分區的元組進行比較來確定。因為我們比較的是元組（也就是說，值的清單或集合）而不是標量值，所以與簡單的 RANGE 分區的情況相比，與 RANGE COLUMNS 分區一起使用的 VALUES LESS THAN 的語意有所不同。在 RANGE 分區中，產生表達式值等於 VALUES LESS THAN 中限制值的列永遠不會放入對應的分區；然而，當使用 RANGE COLUMNS 分區時，有時可能會有一個列，其分區欄清單的第一個元素的值等於 VALUES LESS THAN 值清單中的第一個元素的值，但仍會被放入對應的分區中。

考慮由此陳述式建立的 RANGE 分區表格

CREATE TABLE r1 (
    a INT,
    b INT
)
PARTITION BY RANGE (a)  (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

如果我們向此表格插入 3 列，使得每列的 a 欄值為 5，則所有 3 列都將儲存在分區 p1 中，因為在每種情況下 a 欄值都不小於 5，我們可以透過對資訊綱要 PARTITIONS 表格執行正確的查詢來看到這一點

mysql> INSERT INTO r1 VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

現在考慮一個類似的表格 rc1，它使用 RANGE COLUMNS 分區，並且在 COLUMNS 子句中引用了欄 a 和 b，如下所示建立

CREATE TABLE rc1 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a, b) (
    PARTITION p0 VALUES LESS THAN (5, 12),
    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

如果我們將與我們剛才插入到 r1 中的列完全相同的列插入到 rc1 中，則列的分佈會非常不同

mysql> INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

這是因為我們比較的是列而不是標量值。我們可以將插入的列值與用於定義表格 rc1 中分區 p0 的 VALUES THAN LESS THAN 子句中的限制列值進行比較，如下所示

mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);
+-----------------+-----------------+-----------------+
| (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |
+-----------------+-----------------+-----------------+
|               1 |               1 |               0 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

元組 (5,10) 和 (5,11) 的計算結果小於 (5,12)，因此它們儲存在分區 p0 中。由於 5 不小於 5，且 12 不小於 12，因此 (5,12) 被認為不小於 (5,12)，並儲存在分區 p1 中。

可以使用明確的列建構子來編寫前面範例中的 SELECT 陳述式，如下所示

SELECT ROW(5,10) < ROW(5,12), ROW(5,11) < ROW(5,12), ROW(5,12) < ROW(5,12);

有關在 MySQL 中使用列建構子的詳細資訊，請參閱第 15.2.15.5 節「列子查詢」。

對於僅使用單個分區欄的 RANGE COLUMNS 分區表格，列在分區中的儲存方式與使用 RANGE 分區的等效表格相同。以下 CREATE TABLE 陳述式建立一個使用 1 個分區欄以 RANGE COLUMNS 分區的表格

CREATE TABLE rx (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS (a)  (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

如果我們將列 (5,10)、(5,11) 和 (5,12) 插入到此表格中，我們可以發現它們的位置與我們先前建立並填入的表格 r 的位置相同

mysql> INSERT INTO rx VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

還可以建立使用 RANGE COLUMNS 分區的表格，其中一個或多個欄的限制值在連續的分區定義中重複。只要用於定義分區的欄值元組嚴格遞增，您就可以這樣做。例如，以下每個 CREATE TABLE 陳述式都有效

CREATE TABLE rc2 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,20),
    PARTITION p2 VALUES LESS THAN (10,30),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
 );

CREATE TABLE rc3 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,20),
    PARTITION p2 VALUES LESS THAN (10,30),
    PARTITION p3 VALUES LESS THAN (10,35),
    PARTITION p4 VALUES LESS THAN (20,40),
    PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
 );

以下陳述式也成功，即使乍看之下它似乎不會成功，因為欄 b 的限制值對於分區 p0 為 25，對於分區 p1 為 20，而欄 c 的限制值對於分區 p1 為 100，對於分區 p2 為 50

CREATE TABLE rc4 (
    a INT,
    b INT,
    c INT
)
PARTITION BY RANGE COLUMNS(a,b,c) (
    PARTITION p0 VALUES LESS THAN (0,25,50),
    PARTITION p1 VALUES LESS THAN (10,20,100),
    PARTITION p2 VALUES LESS THAN (10,30,50),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
 );

當設計使用 RANGE COLUMNS 分割的表格時，您始終可以使用 mysql 客戶端比較所需的元組，來測試連續的分區定義，如下所示

mysql> SELECT (0,25,50) < (10,20,100), (10,20,100) < (10,30,50);
+-------------------------+--------------------------+
| (0,25,50) < (10,20,100) | (10,20,100) < (10,30,50) |
+-------------------------+--------------------------+
|                       1 |                        1 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)

如果 CREATE TABLE 陳述式包含的分區定義不是嚴格遞增順序，則會失敗並出現錯誤，如本例所示

mysql> CREATE TABLE rcf (
    ->     a INT,
    ->     b INT,
    ->     c INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,b,c) (
    ->     PARTITION p0 VALUES LESS THAN (0,25,50),
    ->     PARTITION p1 VALUES LESS THAN (20,20,100),
    ->     PARTITION p2 VALUES LESS THAN (10,30,50),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    ->  );
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

當您遇到此類錯誤時，可以通過在它們的欄位列表之間進行「小於」比較來推斷哪些分區定義無效。在此情況下，問題出在分區 p2 的定義，因為用於定義它的元組不大於用於定義分區 p3 的元組，如下所示

mysql> SELECT (0,25,50) < (20,20,100), (20,20,100) < (10,30,50);
+-------------------------+--------------------------+
| (0,25,50) < (20,20,100) | (20,20,100) < (10,30,50) |
+-------------------------+--------------------------+
|                       1 |                        0 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)

在使用 RANGE COLUMNS 時，也可能在多個 VALUES LESS THAN 子句中出現相同欄位的 MAXVALUE。但是，連續分區定義中單個欄位的限制值應為遞增的，不應定義超過一個分區使用 MAXVALUE 作為所有欄位值的上限，並且此分區定義應出現在 PARTITION ... VALUES LESS THAN 子句列表的最後。此外，您不能在多個分區定義中使用 MAXVALUE 作為第一個欄位的限制值。

如前所述，使用 RANGE COLUMNS 分區時，也可以使用非整數欄位作為分割欄位。（有關完整列表，請參閱 第 26.2.3 節，“COLUMNS 分區”。）考慮一個名為 employees 的表格（未分區），使用以下陳述式建立

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
);

使用 RANGE COLUMNS 分區，您可以建立此表格的版本，該版本會根據員工的姓氏將每列資料儲存在四個分區之一中，如下所示

CREATE TABLE employees_by_lname (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN ('g'),
    PARTITION p1 VALUES LESS THAN ('m'),
    PARTITION p2 VALUES LESS THAN ('t'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

或者，您可以透過執行以下 ALTER TABLE 陳述式，使先前建立的 employees 表格使用此方案進行分割

ALTER TABLE employees PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN ('g'),
    PARTITION p1 VALUES LESS THAN ('m'),
    PARTITION p2 VALUES LESS THAN ('t'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
注意

由於不同的字元集和校對規則具有不同的排序順序，因此使用字串欄位作為分割欄位時，正在使用的字元集和校對規則可能會影響使用 RANGE COLUMNS 分割的表格中特定列資料的儲存位置。此外，在建立此類表格後，變更給定資料庫、表格或欄位的字元集或校對規則可能會導致列資料的分佈方式發生變化。例如，當使用區分大小寫的校對規則時，'and' 會排序在 'Andersen' 之前，但當使用不區分大小寫的校對規則時，則相反。

有關 MySQL 如何處理字元集和校對規則的資訊，請參閱 第 12 章，字元集、校對規則、Unicode。

同樣，您可以透過使用此處所示的 ALTER TABLE 陳述式，使 employees 表格以這樣的方式分割，即每列資料根據對應員工的雇用十年期儲存在數個分區之一中

ALTER TABLE employees PARTITION BY RANGE COLUMNS (hired)  (
    PARTITION p0 VALUES LESS THAN ('1970-01-01'),
    PARTITION p1 VALUES LESS THAN ('1980-01-01'),
    PARTITION p2 VALUES LESS THAN ('1990-01-01'),
    PARTITION p3 VALUES LESS THAN ('2000-01-01'),
    PARTITION p4 VALUES LESS THAN ('2010-01-01'),
    PARTITION p5 VALUES LESS THAN (MAXVALUE)
);

有關 PARTITION BY RANGE COLUMNS 語法的其他資訊，請參閱 第 15.1.20 節，“CREATE TABLE 陳述式”。


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