MySQL 5.5 range Partition Add Delete Processing Method Example

Introduction

RANGE partitions are based on a given range of contiguous intervals, whereas earlier versions of RANGE were mainly integer-based partitions. RANGE partitions can also be used for DATE, DATETIME columns in version 5.7, while non-shaping based RANGE COLUMN partitions are available in versions 5.5 and above. RANGE partitions must be continuous and cannot overlap. Use

"VALUES LESS THAN ()" to define the partition interval, the unshaped range values need to use single quotation marks, and MAXVALUE can be used as the highest value of the partition.

This article will introduce the related contents of MySQL 5.5 range partition addition and deletion processing, and share them for your reference and study. Let's take a look at the detailed introduction below:

1. Delete partitions

## View the amount of data for the partition to be processed and export it as a backup 

mysql> select count(*) from baby_account_change_log where updated_time >'2016-12-01 00:00:00' and updated_time <'2017-01-01 00:00:00';
+----------+
| count(*) |
+----------+
| 66252 | 
+----------+
1 row in set (0.23 sec)

## Export Backup 

mysql> select * into outfile '/tmp/baby_account_change_log_p1.sql' from baby_account_change_log where updated_time >'2016-12-01 00:00:00' and updated_time <'2017-01-01 00:00:00' limit 100000000000;
Query OK, 66252 rows affected (2.71 sec)


## Confirm that you want to process the partition 

mysql> explain partitions select count(*) from baby_account_change_log where updated_time >'2016-12-01 00:00:00' and updated_time <'2017-01-01 00:00:00';

+----+-------------+-------------------------------+------------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref | rows | Extra     |
+----+-------------+-------------------------------+------------+-------+---------------+---------+---------+------+-------+--------------------------+
| 1 | SIMPLE  | baby_account_change_log | p1   | index | NULL   | PRIMARY | 8  | NULL | 66252 | Using where; Using index | 
+----+-------------+-------------------------------+------------+-------+---------------+---------+---------+------+-------+--------------------------+

## Delete Partition 

mysql> alter table baby_account_change_log drop partition p0;
Query OK, 0 rows affected (0.01 sec)

2. Add partitions

# Error prompt to delete the partition that stores the maximum value 
mysql> alter table baby_account_change_log add partition(PARTITION p13 VALUES LESS THAN (unix_timestamp('2017-12-31 23:59:59')));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

# Delete Maximum Storage Partition 
mysql> alter table baby_account_change_log drop partition p12;

## Add a new partition 

mysql> alter table baby_account_change_log add partition(PARTITION p12 VALUES LESS THAN (unix_timestamp('2017-12-31 23:59:59')));

Summarize