MySQL - 分区所需的主键

Question

我需要每天根据 UTC 时间戳对我的表进行分区，我得到的错误是 Fatal error: A PRIMARY KEY must include all columns in the table's partitioning function

我是这样做的，columnid is INT PRIMARY KEY column date is INT storeing UTC timestamp

我的表结构DESCRIBE stats;

Field   Type    Null    Key     Default     Extra   
id      int(11) NO      PRI     NULL    auto_increment
date    int(11) YES             NULL    
.
.
.
.

创建表结构SHOW CREATE TABLE stats

CREATE TABLE `stats` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `date` int(11) DEFAULT NULL,
 .
 .
 .
 .
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=371 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

我通过 php 执行脚本

$today = strtotime("today midnight");
$partition = date('d_m_y',$today); 

ALTER TABLE `stats`
PARTITION BY RANGE (date) (
PARTITION p_$partition VALUES LESS THAN ($today)
)

我试过了但是语法错误

ALTER TABLE `stats`
UNIQUE KEY (`id`)
PARTITION BY RANGE (date) (
PARTITION p_$partition VALUES LESS THAN ($today)
)

ALTER TABLE `stats`
PRIMARY KEY (`id`)
PARTITION BY RANGE (date) (
PARTITION p_$partition VALUES LESS THAN ($today)
)

ALTER TABLE `stats`
PRIMARY KEY (`id`,`date`)
PARTITION BY RANGE (date) (
PARTITION p_$partition VALUES LESS THAN ($today)
)

也尝试过 HASH 但同样的错误 A PRIMARY KEY must include all columns in the table's partitioning function in

ALTER TABLE stats
PARTITION BY HASH(date)
PARTITIONS 1

也尝试过这种方式，但出现错误Fatal error: Partition management on a not partitioned table is not possible

ALTER TABLE `stats` 
ADD PARTITION (
PARTITION p_$partition VALUES LESS THAN ($today)
)

请查看并提出任何可能的方法。

谢谢

Answer 1

我通过将 date 添加到主键 id 来解决它。

ALTER TABLE `stats` DROP PRIMARY KEY, ADD PRIMARY KEY(`id`,`date`);

现在表分区成功。

希望对其他人也有帮助。

谢谢