mysql 增加分区_mysql在原有表的基础上添加分区

我们现在有一张表，tb_member_login_logs 用户登录日志表，但数据量太大，需要分区，建议新增一个days字段，int类型，值为 Ymd。

ALTER TABLE `tb_member_login_logs` ADD COLUMN `days` INT (11) UNSIGNED NOT NULL DEFAULT 19700101 COMMENT '日期(格式:Ymd)' AFTER `ID`;

mysql创建分区，分区的字段必须在主键内，不然后报如下错误：

1503 - A PRIMARY KEY must include all columns in the table's partitioning function

所以我们需要创建一个联合主键，首先把主键 ID 自增删除，

ALTER TABLE `tb_member_login_logs` MODIFY COLUMN `id` INT (11) UNSIGNED NOT NULL COMMENT 'ID' FIRST;

然后再删除主键 ID

ALTER TABLE `tb_member_login_logs` DROP PRIMARY KEY;

添加 ID 与 days 的联合主键

ALTER TABLE `tb_member_login_logs` ADD PRIMARY KEY (`id`, `days`);

然后恢复 ID 的自增

ALTER TABLE `tb_member_login_logs` MODIFY COLUMN `id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID' FIRST;

然后根据需要创建分区

ALTER TABLE `tb_member_login_logs` PARTITION BY RANGE(days) (

PARTITION `p197001` VALUES LESS THAN (19700101),

PARTITION `p202003` VALUES LESS THAN (20200301),

PARTITION `p202004` VALUES LESS THAN (20200401),

PARTITION `p202005` VALUES LESS THAN (20200501),

PARTITION `p202006` VALUES LESS THAN (20200601),

PARTITION `p202007` VALUES LESS THAN (20200701),

PARTITION `p202008` VALUES LESS THAN (20200801),

PARTITION `p202009` VALUES LESS THAN (20200901),

PARTITION `p202010` VALUES LESS THAN (20201001),

PARTITION `p202011` VALUES LESS THAN (20201101),

PARTITION `p202012` VALUES LESS THAN (20201201),

PARTITION `p202101` VALUES LESS THAN (20210101),

PARTITION `p202102` VALUES LESS THAN (20210201),

PARTITION `p202103` VALUES LESS THAN (20210301)

);

当然这种做法，运行时间会比较长。

可以创建一个结构与老表一样并分好区的新表，把老表的数据导入新表中。