MySQL 9.0 參考手冊  /  ...  /  RANGE COLUMNS 分割

26.2.3.1 RANGE COLUMNS 分割

範圍欄分割類似於範圍分割，但可讓您使用基於多個欄值的範圍來定義分割。此外，您可以使用非整數類型的欄來定義範圍。

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 子句中的元組進行比較。因為我們比較的是元組（也就是值列表或集合），而不是標量值，所以 VALUES LESS THAN 與 RANGE COLUMNS 分割一起使用時的語義，與簡單的 RANGE 分割的情況有些不同。在 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，正如我們可以透過針對 Information Schema 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)
 );

以下陳述式也會成功，即使乍看之下它似乎不會成功，因為分割區 p0 的欄位 b 的限制值為 25，而分割區 p1 的限制值為 20，且分割區 p1 的欄位 c 的限制值為 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 時，MAXVALUE 也可能在多個 VALUES LESS THAN 子句中用於相同的欄位。然而，連續分割區定義中個別欄位的限制值應以遞增方式排列，不應有多於一個分割區定義使用 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)
);

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

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。

同樣地，您可以讓 employees 表格以這種方式進行分割，使每一列都根據對應員工的聘用年代儲存在數個分割區之一中，方法是使用此處所示的 ALTER TABLE 陳述式

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