How to delete partition in mysql

In mysql, you can use the alter statement with "DROP PARTITION" to delete a partition. The function of "DROP PARTITION" is to delete the specified partition. The syntax is "ALTER TABLE 'table name' DROP PARTITION 'partition" name' ".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to delete a partition in mysql

The syntax for deleting a partition is: ALTER TABLE 'table name' DROP PARTITION 'partition name'

Clear the partition data to be empty, and keep the partition without deleting it. Just clean the data, the command is as follows

alter table bm_scenes_data_reminder truncate partition p20210104;

Delete partition

alter table bm_scenes_data_reminder drop partition p20210104;

After deletion, execute the view table creation statement, you can see that the p20210104 partition is gone

Add partition

##What if you want to add back the newly deleted p20210104 partition? what to do. First try to directly execute the add partition command

ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB);

The results are as follows, indicating that it is not feasible.

mysql> ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB);
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
mysql>

1 is not feasible in step 1. The prompt must be after the last partition before it can be added like this.

Therefore, if you must add back the p20210104 partition (that is, you need to add a partition in the middle), you can only delete all the partitions after p20210104 first, then add the p20210104 partition, and then add back the partitions after p20210104 . The operation is as follows:

##Delete all partitions after the p20210104 partition first

ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210105;
ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210106;
ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210107;
ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210108;
ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210109;
ALTER TABLE bm_scenes_data_reminder drop PARTITION p20210110;
ALTER TABLE bm_scenes_data_reminder drop PARTITION future;

##Add the p20210104 partition

ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210104 VALUES LESS THAN (738159) ENGINE = InnoDB);

##Add back all partitions after the p20210104 partition

ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210105 VALUES LESS THAN (738160) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210106 VALUES LESS THAN (738161) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210107 VALUES LESS THAN (738162) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210108 VALUES LESS THAN (738163) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210109 VALUES LESS THAN (738164) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION p20210110 VALUES LESS THAN (738165) ENGINE = InnoDB);
ALTER TABLE bm_scenes_data_reminder ADD PARTITION (PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

Finally, check the ddl and find that the partition has been added back, but this operation method will delete all partition data after the p20210104 partition. Please be careful in the official online environment

Case

The system has an operating system table sys_log, which can be used to delete the partition 90 days ago and create a partition 4 days later every day (that is, create a table for the partition 4 days later every day). The steps are as follows:

# #Create a new ordinary table and execute it only once

CREATE TABLE `sys_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `begin_time` datetime DEFAULT NULL COMMENT '开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '结束时间',
  `spend_mills` int(11) DEFAULT NULL COMMENT '运行时长,单位ms',
  `username` varchar(100) DEFAULT NULL COMMENT '用户id',
  `log_status` int(11) NOT NULL DEFAULT '0' COMMENT '运行状态,[0]成功[1]失败',
  `code` int(11) NOT NULL DEFAULT '0' COMMENT '错误码',
  `remote_addr` varchar(50) DEFAULT '' COMMENT '远程地址',
  `request_uri` varchar(255) DEFAULT NULL COMMENT '请求路径',
  `user_agent` text COMMENT '用户代理',
  `req_data` text NOT NULL COMMENT '请求参数',
  `resp_data` longtext NOT NULL COMMENT '返回结果',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_log_begintime` (`begin_time`) USING BTREE COMMENT '系统日志的beginTime字段索引'
) ENGINE=Innodb DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

##Modify it into a partitioned table and execute it only once, leaving a partition for the current time and a future partition in the future

ALTER TABLE `sys_log` DROP PRIMARY KEY, ADD PRIMARY KEY(`id`,`begin_time`);
alter table sys_log partition by RANGE (to_days(begin_time)) (
    PARTITION p20210816 VALUES LESS THAN (738383),
    PARTITION future VALUES LESS THAN MAXVALUE
);

##Execute the following every day Partition operation, add a day's partition, such as

ALTER TABLE sys_log drop PARTITION future;
ALTER TABLE sys_log ADD PARTITION (PARTITION p20210817 VALUES LESS THAN (738384) ENGINE = InnoDB);
ALTER TABLE sys_log ADD PARTITION (PARTITION p20210818 VALUES LESS THAN (738385) ENGINE = InnoDB);
ALTER TABLE sys_log ADD PARTITION (PARTITION p20210819 VALUES LESS THAN (738386) ENGINE = InnoDB);
ALTER TABLE sys_log ADD PARTITION (PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

## Scheduled task configuration, perform partition cleaning and creation once a day

30 4 * * * /bin/python /home/testuser/SyslogPartitionClear.py >/dev/null 2>&1

Recommended learning: mysql video tutorial

###

The above is the detailed content of How to delete partition in mysql. For more information, please follow other related articles on the PHP Chinese website!