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

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

?

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

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

?

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

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

?

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

然后再删除主键 ID

?

1	ALTER TABLE `tb_member_login_logs` DROP PRIMARY KEY;

添加 ID 与 days 的联合主键

?

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

然后恢复 ID 的自增

?

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

然后根据需要创建分区

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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) );

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

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