Example of adding and deleting range partitions in MySQL 5.5

introduce

RANGE partitioning is based on a given continuous interval range. Early versions of RANGE were mainly based on integer partitioning. In version 5.7, DATE and DATETIME columns can also use RANGE partitioning. At the same time, versions above 5.5 provide non-integer-based RANGE COLUMN partitioning. RANGE partitions must be contiguous and cannot overlap. use

"VALUES LESS THAN ()" is used to define the partition range. Non-integer range values need to use single quotes, and MAXVALUE can be used as the highest value of the partition.

This article will introduce the relevant content of adding and deleting range partitions in MySQL 5.5, and share it with you for your reference and learning. Let's take a look at the detailed introduction:

1. Delete partition

##Check the amount of data in the partition to be processed and export it as a backupmysql> 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 backupmysql> 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 partitionmysql> 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 partitionmysql> alter table baby_account_change_log drop partition p0;
Query OK, 0 rows affected (0.01 sec)

2. Add partition

#Error message: delete the partition storing 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 the partition storing the maximum valuemysql> alter table baby_account_change_log drop partition p12;

##Add a new partitionmysql> alter table baby_account_change_log add partition(PARTITION p12 VALUES LESS THAN (unix_timestamp('2017-12-31 23:59:59')));

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in: