MySQL分区表：表中如果存在主键、唯一键等唯一性约束，而分区键又是一个普通列，该如何调整？...

当 MySQL 表中存在主键或唯一键等唯一性约束，而您希望对表进行分区，并且分区键是一个普通的列时，您需要确保分区键（或分区键的一部分）被包含在主键或唯一键中。这是因为 MySQL 要求分区键或分区键的一部分必须出现在主键或唯一键中，以确保数据的一致性和完整性。

解决方案

根据您的具体需求，您可以采取以下几种方法之一来调整表结构：

1. 将分区键包含在主键中：

• 如果分区键是唯一的，您可以考虑将分区键包含在主键中。
• 这样做可能会导致主键变得非常宽泛，从而影响性能。

2. 更改分区键：

• 如果主键列是唯一的，并且您希望保持主键不变，您可以考虑将分区键更改为与主键列相同或包含主键列。
• 这种方法可能更适合大多数场景，因为它不会导致主键变得过于宽泛。

3. 创建复合主键：

• 如果您希望保留主键，并且还想使用某个列作为分区键，您可以创建一个复合主键，将主键列和分区键都包含进来。
• 这种方法可以满足分区的要求，但同样可能导致主键变得宽泛。

示例：更改分区键

假设您希望保留 id 作为主键，并希望使用 created_at 作为分区键，您可以考虑更改分区键为 id，因为 id 已经是唯一的。

创建表

登录后复制

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product VARCHAR(50) DEFAULT 'testingpartitions',
    amount INT DEFAULT 1,
    created_at DATE NOT NULL
)
PARTITION BY RANGE(id)
(
    PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    PARTITION p2 VALUES LESS THAN (3000000),
    PARTITION p3 VALUES LESS THAN (4000000),
    PARTITION p4 VALUES LESS THAN (5000000),
    PARTITION p5 VALUES LESS THAN (6000000),
    PARTITION p6 VALUES LESS THAN (7000000),
    PARTITION p7 VALUES LESS THAN (8000000),
    PARTITION p8 VALUES LESS THAN (9000000),
    PARTITION p9 VALUES LESS THAN (10000000),
    PARTITION p10 VALUES LESS THAN (11000000),
    PARTITION p11 VALUES LESS THAN MAXVALUE
);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.

在这个示例中，我们将分区键更改为 id，并且定义了多个分区。每个分区包含一定范围的 id 值。

示例：创建复合主键

如果您希望保留 id 作为主键的一部分，并且还想使用 created_at 作为分区键，您可以创建一个复合主键，如下所示：

创建表

登录后复制

CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    product VARCHAR(50) DEFAULT 'testingpartitions',
    amount INT DEFAULT 1,
    created_at DATE NOT NULL,
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE(YEAR(created_at))
(
    PARTITION p0 VALUES LESS THAN (1995),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (1997),
    PARTITION p3 VALUES LESS THAN (1998),
    PARTITION p4 VALUES LESS THAN (1999),
    PARTITION p5 VALUES LESS THAN (2000),
    PARTITION p6 VALUES LESS THAN (2001),
    PARTITION p7 VALUES LESS THAN (2002),
    PARTITION p8 VALUES LESS THAN (2003),
    PARTITION p9 VALUES LESS THAN (2004),
    PARTITION p10 VALUES LESS THAN (2010),
    PARTITION p11 VALUES LESS THAN MAXVALUE
);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.

在这个示例中，我们将主键定义为 id 和 created_at 的组合，以满足分区键必须包含在主键中的要求。

示例：调整现有表

如果您希望保留现有的表结构，并且表已经存在，那么您可以考虑以下步骤来调整表：

1. 备份表：在进行任何更改之前，务必备份您的表。
2. 删除当前主键：删除当前的主键约束。
3. 创建复合主键：创建一个新的复合主键。

示例：调整现有表

假设您的 sales 表已经存在，并且 id 已经是主键，您想要将 id 和 created_at 设置为复合主键。

登录后复制

-- 备份表
CREATE TABLE sales_backup LIKE sales;
INSERT INTO sales_backup SELECT * FROM sales;

-- 删除当前的主键
ALTER TABLE sales DROP PRIMARY KEY;

-- 创建新的复合主键
ALTER TABLE sales ADD PRIMARY KEY (id, created_at);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

总结

• 如果您希望保留 id 作为主键，并且希望使用 created_at 作为分区键，您可以选择更改分区键为 id。
• 如果您希望保留 created_at 作为分区键，并且希望保留 id 作为主键的一部分，您可以创建一个复合主键。

原创作者: zhangxueliang 转载于: https://blog.51cto.com/zhangxueliang/11709303