mysql - 为什么我无法插入范围列分区

Question

我有一个包含大量数据的表，并且这些数据已经分割成分区。现在我想为新类型的数据集添加新分区。

当前表格如下所示:

create table audit_log
(
    id bigint unsigned auto_increment,
    inserted_at datetime default '0000-00-00 00:00:00' not null,
    eventType varchar(50) not null,
    eventTableName varchar(255) default '' not null,
    entityId int unsigned null,
    matchId int unsigned null,
    userId int unsigned null,
    route varchar(255) null,
    changes longtext null,
    primary key (id, eventTableName, inserted_at)
)
collate=utf8_unicode_ci;

有多个基于 eventTableName 和 inserted_at 列的分区:

ALTER TABLE audit_log PARTITION BY RANGE COLUMNS (eventTableName, inserted_at)  (
    PARITION day_referee_statistics_20190902 VALUES LESS THAN ('referee_statistics','2019-09-03'),
    PARITION day_referee_statistics_20190903 VALUES LESS THAN ('referee_statistics','2019-09-04'),
    PARITION referee_future VALUES LESS THAN ('referee_statistics',MAXVALUE),
    PARITION day_team_statistics_20190902 VALUES LESS THAN ('team_statistics','2019-09-03'),
    PARITION day_team_statistics_20190903 VALUES LESS THAN ('team_statistics','2019-09-04'),
    PARITION team_statistics_future VALUES LESS THAN ('team_statistics',MAXVALUE),
    PARITION future VALUES LESS THAN (MAXVALUE, MAXVALUE);

还有很多其他分区...

现在我想为要审核的新表添加一个新分区:results_future 分区。首先，我尝试替换 future 的分区，但失败了，因为之间还有其他分区具有更高的值。然后我对它们进行了排序，但仍然得到相同的错误:

ALTER TABLE audit_log REORGANIZE PARTITION future_team_statistics INTO (
    PARTITION future_results VALUES LESS THAN ('results',MAXVALUE),
    PARTITION future_team_statistics VALUES LESS THAN ('team_statistics',MAXVALUE)
)

失败:

General error: 1493 VALUES LESS THAN value must be strictly increasing for each partition

Answer 1

我的新分区和 MAXVALUE 分区之间的最小分区不是 team_statistics_future - 在本例中是 day_team_statistics_20190902 分区。所以查询将是:

ALTER TABLE audit_log REORGANIZE PARTITION day_team_statistics_20190902 INTO (
    PARTITION future_results VALUES LESS THAN ('results',MAXVALUE),
    PARTITION day_team_statistics_20190902 VALUES LESS THAN ('team_statistics','2019-09-03')
);