Detailed explanation of adding deletion processing examples for MySQL 5.5 range partition

This article mainly introduces you to the relevant information about the MySQL 5.5 range partition addition and deletion processing. The article introduces it in detail through the example code, which has certain reference and learning value for everyone. Friends who need it can take a look below.

Introduction

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, and versions above 5.5 provide non-shaping-based RANGE COLUMN partitioning. RANGE partitions must be contiguous and cannot overlap. Use

"VALUES LESS THAN ()" to define the partition interval. 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 to you the relevant content of adding and deleting the MySQL 5.5 range partition, and share it with you for your reference and study. Let’s take a look at the detailed introduction:

1. Delete partitions

##查看要处理的分区的数据量，并导出作为备份

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

##导出备份

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 <&#39;2017-01-01 00:00:00&#39; limit 100000000000;
Query OK, 66252 rows affected (2.71 sec)


##确认要处理分区

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

+----+-------------+-------------------------------+------------+-------+---------------+---------+---------+------+-------+--------------------------+
| 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 | 
+----+-------------+-------------------------------+------------+-------+---------------+---------+---------+------+-------+--------------------------+

##删除分区

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

2. Add partitions

#错误提示删除存储最大值分区
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

#删除存储最大值分区
mysql> alter table baby_account_change_log drop partition p12;

##增加新的分区

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

Related recommendations:

Mysql partitions and range partitions detailed introduction

mysql partitions Management-range partition_MySQL

MySQLrange partition (2)_MySQL

The above is the detailed content of Detailed explanation of adding deletion processing examples for MySQL 5.5 range partition. For more information, please follow other related articles on the PHP Chinese website!