MySQL :: MySQL 9.0 参考手册 :: 26.6.1 分区键、主键和唯一键

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / 分区键、主键和唯一键

26.6.1 分区键、主键和唯一键

本节讨论分区键与主键和唯一键的关系。管理此关系的规则可以表示如下：分区表的分区表达式中使用的所有列必须是该表可能具有的所有唯一键的一部分。

换句话说，表上的每个唯一键都必须使用表分区表达式中的所有列。（这也包括表的主键，因为它定义上是一个唯一键。此特定情况将在本节后面讨论。）例如，以下每个表创建语句都是无效的

Press CTRL+C to copy
 
CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1),
    UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

在每种情况下，提议的表都将至少有一个唯一键不包含分区表达式中使用的所有列。

以下每个语句都是有效的，并且代表使相应的无效表创建语句起作用的一种方式

Press CTRL+C to copy
 
CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

此示例显示了在这种情况下产生的错误

Press CTRL+C to copy
 
mysql> CREATE TABLE t3 (
    ->     col1 INT NOT NULL,
    ->     col2 DATE NOT NULL,
    ->     col3 INT NOT NULL,
    ->     col4 INT NOT NULL,
    ->     UNIQUE KEY (col1, col2),
    ->     UNIQUE KEY (col3)
    -> )
    -> PARTITION BY HASH(col1 + col3)
    -> PARTITIONS 4;
ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

CREATE TABLE 语句失败是因为提议的分区键中都包含了 col1 和 col3，但是这两个列都不是表上两个唯一键的一部分。这展示了对无效表定义的一种可能的修复方法

Press CTRL+C to copy
 
mysql> CREATE TABLE t3 (
    ->     col1 INT NOT NULL,
    ->     col2 DATE NOT NULL,
    ->     col3 INT NOT NULL,
    ->     col4 INT NOT NULL,
    ->     UNIQUE KEY (col1, col2, col3),
    ->     UNIQUE KEY (col3)
    -> )
    -> PARTITION BY HASH(col3)
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.05 sec)

在这种情况下，提议的分区键 col3 是两个唯一键的一部分，并且表创建语句成功。

以下表根本无法分区，因为无法在分区键中包含属于两个唯一键的任何列

Press CTRL+C to copy
 
CREATE TABLE t4 (
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3),
    UNIQUE KEY (col2, col4)
);

由于每个主键都定义上是一个唯一键，因此此限制也包括表的主键（如果有）。例如，接下来的两个语句是无效的

Press CTRL+C to copy
 
CREATE TABLE t5 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t6 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col3),
    UNIQUE KEY(col2)
)
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;

在这两种情况下，主键都不包含分区表达式中引用的所有列。但是，接下来的两个语句都是有效的

Press CTRL+C to copy
 
CREATE TABLE t7 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;

CREATE TABLE t8 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2, col4),
    UNIQUE KEY(col2, col1)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;

如果表没有唯一键（包括没有主键），则此限制不适用，您可以使用分区表达式中的任何列或列，只要列类型与分区类型兼容即可。

出于同样的原因，您不能将唯一键添加到已有的分区表中，除非该键包含表分区表达式使用的所有列。请考虑如下所示创建的分区表

Press CTRL+C to copy
 
mysql> CREATE TABLE t_no_pk (c1 INT, c2 INT)
    ->     PARTITION BY RANGE(c1) (
    ->         PARTITION p0 VALUES LESS THAN (10),
    ->         PARTITION p1 VALUES LESS THAN (20),
    ->         PARTITION p2 VALUES LESS THAN (30),
    ->         PARTITION p3 VALUES LESS THAN (40)
    ->     );
Query OK, 0 rows affected (0.12 sec)

可以使用以下任一ALTER TABLE 语句将主键添加到 t_no_pk 中

Press CTRL+C to copy
 
#  possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

# drop this PK
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

#  use another possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

# drop this PK
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

但是，下一个语句将失败，因为 c1 是分区键的一部分，但不是提议的主键的一部分

Press CTRL+C to copy
 
#  fails with error 1503
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

由于 t_no_pk 的分区表达式中只有 c1，因此尝试仅在 c2 上添加唯一键会失败。但是，您可以添加使用 c1 和 c2 两个的唯一键。

这些规则也适用于希望使用 ALTER TABLE ... PARTITION BY 分区的现有非分区表。请考虑如下所示创建的表 np_pk

Press CTRL+C to copy
 
mysql> CREATE TABLE np_pk (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(50),
    ->     added DATE,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.08 sec)

以下 ALTER TABLE 语句将出现错误而失败，因为 added 列不是表中任何唯一键的一部分

Press CTRL+C to copy
 
mysql> ALTER TABLE np_pk
    ->     PARTITION BY HASH( TO_DAYS(added) )
    ->     PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

但是，使用 id 列作为分区列的此语句是有效的，如下所示

Press CTRL+C to copy
 
mysql> ALTER TABLE np_pk
    ->     PARTITION BY HASH(id)
    ->     PARTITIONS 4;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

在 np_pk 的情况下，唯一可以作为分区表达式一部分使用的列是 id；如果您希望使用分区表达式中的任何其他列或列来对该表进行分区，则必须先修改该表，方法是将所需的列或列添加到主键中，或者完全删除主键。

上一个首页上层下一个