MySQL 5.7 分区键、主键和唯一键的关系

Posted on by YongJing(敬勇)

分区键、主键和唯一键
这里将讨论分区键与主键和唯一键的关系。控制这种关系的规则可以表达如下：分区表的分区表达式中使用的所有列必须是表可能具有的每个唯一 键的一部分。

换句话说，表上的每个唯一键都必须使用表的分区表达式中的每一列。(这也包括表的主键，因为根据定义，它是唯一的键。本节稍后将讨论这 种特殊情况。)例如，下列创建表的语句都是无效的：

mysql> 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;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> 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;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

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

下列语句都是有效的，表示了相应的无效表创建语句的一种工作方式：

mysql> 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;
Query OK, 0 rows affected (0.02 sec)

mysql> 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;
Query OK, 0 rows affected (0.01 sec)

下面的例子显示了在这种情况下产生的错误：

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 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

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

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.03 sec)

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

下面的表根本不能分区，因为没有办法在分区键中包含属于两个唯一键的任何列：

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

由于每个主键根据定义都是唯一键，因此此限制还包括表的主键（如果有的话）。例如，下面两个语句是无效的：

mysql> 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;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> 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;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

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

mysql> 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;
Query OK, 0 rows affected (0.02 sec)

mysql> 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;
Query OK, 0 rows affected (0.02 sec)

如果一张表没有唯一键（包括没有主键），那么这个限制就不适用了，只要列类型与分区类型兼容，就可以在分区表达式中使用任意一列或多列 。
出于同样的原因，用户不能在分区表中添加唯一键，除非这个键包含了表的分区表达式所使用的所有列。考虑如下所示创建的分区表：

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.02 sec)

可以使用ALTER TABLE语句为t_no_pk添加一个主键：

mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

然而，下一条语句失败了，因为c1是分区键的一部分，但不是建议的主键的一部分：

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，如下所示：

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.01 sec)

下面的ALTER 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列作为分区列的语句是有效的，如下所示：

mysql> ALTER TABLE np_pk PARTITION BY HASH(id) PARTITIONS 4;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

发表评论

电子邮件地址不会被公开。

Proudly powered by WordPress | Indrajeet by Sus Hill.