Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of mysql how to delete the partition, the content is detailed and easy to understand, the operation is simple and fast, and has a certain reference value, I believe you will gain something after reading this mysql article on how to delete the partition, let's take a look at it.

In mysql, you can use the alter statement with "DROP PARTITION" to delete the 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.

Mysql delete partition

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

Clean up the partition data is empty, keep the partition does not delete, just clean up 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

Execute the view table creation statement after deletion, and you can see that the p20210104 partition is gone.

Increase zoning

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

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

The results are as follows, which shows 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 definitionmysql >

# # 1 step is not feasible. The hint must be after the last partition before it can be added in this way.

Therefore, if you have to add back the partition p20210104 (that is, you need to add more partitions in the middle), you can only delete all the partitions after p20210104, then add p20210104, and then add back the partitions after p20210104. Do the following:

# # delete all partitions following 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

# # adding p20210104 partition

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

# # add back all the partitions behind 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 will delete all the partition data behind the p20210104 partition. Please be careful in the official online environment.

Case

The system has an operating system table sys_log, which can delete the partition before 90 days every day and create a partition after 4 days at the same time (that is, create the partition after 4 days every day). The steps are as follows:

# # create a new regular table and execute it only once

CREATE TABLE `sys_ log` (`id`time (20) NOT NULL AUTO_INCREMENT, `begin_ time`datetime DEFAULT NULL COMMENT 'start time', `end_ time`datetime DEFAULT NULL COMMENT 'end time', `spend_ mills`int (11) DEFAULT NULL COMMENT 'run time, unit ms', `username` varchar (100) DEFAULT NULL COMMENT' user id', `log_ status` int (11) NOT NULL DEFAULT'0' COMMENT 'running status, [0] succeeded [1] failed' `code` int (11) NOT NULL DEFAULT'0' COMMENT 'error code', 'remote_ addr` varchar' COMMENT 'remote address', 'request_ uri` varchar' DEFAULT NULL COMMENT 'request path', `user_ agent` text COMMENT 'user agent', `req_ data`text NOT NULL COMMENT 'request parameter', `resp_ data`longtext NOT NULL COMMENT 'return result', PRIMARY KEY (`id`) USING BTREE KEY `idx_log_ begintime` (`begin_ time`) USING BTREE COMMENT 'beginTime field index of Syslog') ENGINE=Innodb DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

# # modify the partition table to be executed only once, leaving a partition of the current time and a future future partition

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)

# # perform the following partition operations every day to add one day of partitions, 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 to perform partition cleaning and creation once a day

304 * / bin/python / home/testuser/SyslogPartitionClear.py > / dev/null 2 > & 1 this is the end of the article on "how to delete partitions in mysql". Thank you for reading! I believe you all have a certain understanding of the knowledge of "how to delete mysql partition". If you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.