22.6.1 分区键,主键和唯一键
本节讨论分区键与主键和唯一键的关系。控制这种关系的规则可以 table 示为:分区 table 的分区 table 达式中使用的所有列都必须是 table 可能具有的每个唯一键的一部分。
换句话说,table 上的每个唯一键都必须使用 table 的分区 table 达式中的每一列。 (这也包括 table 的主键,因为根据定义它是唯一键.本节稍后将讨论这种特殊情况.)例如,以下每个 table 创建语句均无效:
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;
在每种情况下,提议的 table 都将具有至少一个唯一键,该唯一键不包括分区 table 达式中使用的所有列。
以下每个语句均有效,并且 table 示一种使相应的无效 table 创建语句起作用的方式:
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;
此示例显示在这种情况下产生的错误:
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
,但是这些列都不是 table 上唯一键的一部分。这显示了无效 table 定义的一种可能的修复方法:
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
是两个唯一键的一部分,并且 table 创建语句成功。
下 table 根本无法分区,因为无法在分区键中包含属于两个唯一键的任何列:
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)
);
由于根据定义,每个主键都是唯一键,因此该限制还包括 table 的主键(如果有的话)。例如,以下两个语句无效:
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;
在这两种情况下,主键都不包括分区 table 达式中引用的所有列。但是,接下来的两个语句均有效:
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;
如果 table 没有唯一键(包括没有主键),则此限制不适用,并且您可以在分区 table 达式中使用任何列,只要列类型与分区类型兼容即可。
出于相同的原因,您以后不能再将唯一键添加到分区 table 中,除非该键包括该 table 的分区 table 达式使用的所有列。考虑如下所示创建的分区 table:
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
添加主键:
# 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
是分区键的一部分,但不是建议的主键的一部分:
# 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
的分区 table 达式中只有c1
,因此仅尝试在c2
上添加唯一键失败。但是,您可以添加同时使用c1
和c2
的唯一键。
这些规则也适用于您希望使用ALTER TABLE ...分区依据进行分区的现有未分区 table。考虑如下所示创建的 tablenp_pk
:
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
列不是 table 中任何唯一键的一部分:
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
列作为 partitioning 列是有效的,如下所示:
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
的情况下,唯一可用作分区 table 达式一部分的列是id
;如果您希望使用分区 table 达式中的任何其他列对该 table 进行分区,则必须首先修改该 table,方法是将所需的一个或多个列添加到主键中,或者完全删除主键。