我目前正在使用 MySQL 5.6。

在我的數據庫中，我有一個名為paramlog_ems. 它有 2M 行

我想根據日期對這個日誌表進行分區。

這是表結構：

CREATE TABLE paramlog_ems (
 SiteIndex smallint(5) unsigned NOT NULL,
 RegionIndex smallint(5) unsigned NOT NULL,
 OrganizationIndex smallint(5) unsigned NOT NULL,
 DeviceSlaveId smallint(5) unsigned NOT NULL,
 UserIndex smallint(5) unsigned NOT NULL,
 ParameterID smallint(5) unsigned NOT NULL,
 ParamValue double(20,10) NOT NULL,
 DeviceName varchar(50) NOT NULL,
 LogDate datetime NOT NULL,
 Category tinyint(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

我想使用 MySQL Workbench 來做到這一點。

以下是按月對錶進行分區的步驟

創建一個臨時表，在 LogDate 上分區和索引

CREATE TABLE paramlog_ems_new (
 SiteIndex smallint(5) unsigned NOT NULL,
 RegionIndex smallint(5) unsigned NOT NULL,
 OrganizationIndex smallint(5) unsigned NOT NULL,
 DeviceSlaveId smallint(5) unsigned NOT NULL,
 UserIndex smallint(5) unsigned NOT NULL,
 ParameterID smallint(5) unsigned NOT NULL,
 ParamValue double(20,10) NOT NULL,
 DeviceName varchar(50) NOT NULL,
 LogDate datetime NOT NULL,
 Category tinyint(3) NOT NULL,
 KEY LogDate (LogDate)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(`LogDate`)
(PARTITION p00000000000 VALUES LESS THAN ('2015-01-01') ENGINE = MyISAM,
PARTITION p2015_01_jan VALUES LESS THAN ('2015-02-01') ENGINE = MyISAM,
PARTITION p2015_02_feb VALUES LESS THAN ('2015-03-01') ENGINE = MyISAM,
PARTITION p2015_03_mar VALUES LESS THAN ('2015-04-01') ENGINE = MyISAM,
PARTITION p2015_04_apr VALUES LESS THAN ('2015-05-01') ENGINE = MyISAM,
PARTITION p2015_05_may VALUES LESS THAN ('2015-06-01') ENGINE = MyISAM,
PARTITION p2015_06_jun VALUES LESS THAN ('2015-07-01') ENGINE = MyISAM,
PARTITION p2015_07_jul VALUES LESS THAN ('2015-08-01') ENGINE = MyISAM,
PARTITION p2015_08_aug VALUES LESS THAN ('2015-09-01') ENGINE = MyISAM,
PARTITION p2015_09_sep VALUES LESS THAN ('2015-10-01') ENGINE = MyISAM,
PARTITION p2015_10_oct VALUES LESS THAN ('2015-11-01') ENGINE = MyISAM,
PARTITION p2015_11_nov VALUES LESS THAN ('2015-12-01') ENGINE = MyISAM,
PARTITION p2015_12_dec VALUES LESS THAN ('2016-01-01') ENGINE = MyISAM,
PARTITION p99999999999 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM);

載入溫度表

INSERT INTO paramlog_ems_new SELECT * FROM paramlog_ems;

交換臨時表

ALTER TABLE paramlog_ems RENAME paramlog_ems_old;
ALTER TABLE paramlog_ems_new RENAME paramlog_ems;

試一試 ！！！

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