如何將分區添加到 mariadb / mysql 中的現有表？

我有下表。

我也想添加分區。

CREATE TABLE `app_log_Test` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `dateCreated` datetime NOT NULL,
 `host` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `label` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `event` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `level` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `message` text COLLATE utf8mb4_unicode_ci,
 `version` bigint(20) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `app_log_dateCreated` (`dateCreated`),
 KEY `app_log_label` (`label`),
 KEY `app_log_event` (`event`),
 KEY `app_log_level` (`level`)
) ENGINE=TokuDB `COMPRESSION`=tokudb_quicklz

我正在使用 MariaDB 10。

MariaDB [test2]> alter table app_log_Test partition by RANGE(TO_DAYS(dateCreated))(
-> PARTITION p_201809 VALUES LESS THAN (TO_DAYS('2018-09-01 00:00:00')) ENGINE = TokuDB,
-> PARTITION p_201810 VALUES LESS THAN (TO_DAYS('2018-10-01 00:00:00')) ENGINE = TokuDB);

我收到以下錯誤

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

首先我需要處理錯誤

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

這是有道理的，分區意味著根據某些條件將數據放入不同的文件中。如果分區條件不是表主鍵索引的一部分，這將不知道如何將數據放在哪裡。

alter table app_log_Test drop PRIMARY KEY, add primary key (`id`, `dateCreated`);

接下來，我可以重新執行我的 alter table 以添加我關心的分區。

ALTER TABLE app_log_Test
PARTITION BY RANGE (TO_DAYS(dateCreated))
   (PARTITION p_invalid_date VALUES LESS THAN (0) ENGINE = TokuDB,
   PARTITION p_201809 VALUES LESS THAN (TO_DAYS('2018-09-01 00:00:00')),
   PARTITION p_201810 VALUES LESS THAN (TO_DAYS('2018-10-01 00:00:00')),
   PARTITION p_max_future_dates VALUES LESS THAN MAXVALUE);

如果在那之後我需要添加更多分區。我不必再次指定分區方案，我只需添加分區及其約束即可。

ALTER TABLE app_log_Test
   REORGANIZE PARTITION p_max_future_dates INTO (
       PARTITION p_201811 VALUES LESS THAN (TO_DAYS('2018-11-01 00:00:00')),
       PARTITION p_201812 VALUES LESS THAN (TO_DAYS('2018-12-01 00:00:00')),
       PARTITION p_max_future_dates  VALUES LESS THAN MAXVALUE);  

我的桌子現在看起來像這樣。

show create table app_log_Test;
Create Table: CREATE TABLE `app_log_Test` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `dateCreated` datetime NOT NULL,
 `host` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `label` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `event` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `level` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `message` text COLLATE utf8mb4_unicode_ci,
 `version` bigint(20) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`,`dateCreated`),
 KEY `app_log_dateCreated` (`dateCreated`),
 KEY `app_log_label` (`label`),
 KEY `app_log_event` (`event`),
 KEY `app_log_level` (`level`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci `COMPRESSION`=tokudb_zlib

/*!50100 PARTITION BY RANGE (TO_DAYS(dateCreated))
(PARTITION p_invalid_date VALUES LESS THAN (0) ENGINE = TokuDB,
PARTITION p_201901 VALUES LESS THAN (737425) ENGINE = TokuDB,
PARTITION p_201810 VALUES LESS THAN (737333) ENGINE = TokuDB,
PARTITION p_201811 VALUES LESS THAN (737364) ENGINE = TokuDB,
PARTITION p_201812 VALUES LESS THAN (737394) ENGINE = TokuDB,
PARTITION p_max_future_dates VALUES LESS THAN MAXVALUE ENGINE = TokuDB) */

引用自：https://dba.stackexchange.com/questions/219748